Search Results

Search found 8547 results on 342 pages for 'hash join'.

Page 166/342 | < Previous Page | 162 163 164 165 166 167 168 169 170 171 172 173  | Next Page >

• Doctrine YAML not generating correctly? Or is this markup wrong?

  - by ropstah

  I'm trying to get a many-to-many relationship between Users and Settings. The models seem to be generated correctly, however the following query fails: "User_Setting" with an alias of "us" in your query does not reference the parent component it is related to. $q = new Doctrine_RawSql(); $q->select('{s.*}, {us.*}') ->from('User u CROSS JOIN Setting s LEFT JOIN User_Setting us ON us.usr_auto_key = u.usr_auto_key AND us.set_auto_key = s.set_auto_key') ->addComponent('s', 'Setting s INDEXBY s.set_auto_key') ->addComponent('us', 'User_Setting us') ->where(u.usr_auto_key = ?',$this->usr_auto_key); $this->settings = $q->execute(); Does anyone spot a problem? This is my YAML: User: connection: default tableName: User columns: usr_auto_key: type: integer(4) fixed: false unsigned: false primary: true autoincrement: true notnull: true email: type: string(100) fixed: false unsigned: false primary: false default: '' notnull: true autoincrement: false password: type: string(32) fixed: false unsigned: false primary: false default: '' notnull: true autoincrement: false relations: Setting: class: Setting foreignAlias: User refClass: User_Setting local: usr_auto_key foreign: set_auto_key Setting: connection: default tableName: Setting columns: set_auto_key: type: integer(4) fixed: false unsigned: false primary: true autoincrement: true notnull: true name: type: string(50) fixed: false unsigned: false primary: false notnull: true autoincrement: false User_Setting: connection: default tableName: User_Setting columns: usr_auto_key: type: integer(4) fixed: false unsigned: false primary: true autoincrement: false notnull: true set_auto_key: type: integer(4) fixed: false unsigned: false primary: true autoincrement: false notnull: true value: type: string(255) fixed: false unsigned: false primary: false notnull: true autoincrement: false relations: Setting: foreignAlias: User_Setting local: set_auto_key foreign: set_auto_key User: foreignAlias: User_Setting local: usr_auto_key foreign: usr_auto_key

  Read the article

• Worpress WorkFlow Modfications

  - by blgnklc

  Hi All WordPress Lovers, I would like to ask a help about Zensor which is a plugin that you publish a post then a moderator approves the post to be published on the wordpress blog site. When a post is awating for approval, each awaiting post is appearing "waiting moderation". But, I dont want any link appears before moderator approval. Actually I found the joing sentence below; 1- Must be added to the end of JOIN part of any query: LEFT JOIN wp_zensor ON ID = wp_zensor.post_id 2- Must be added to the end of WHERE condition : AND wp_zensor.moderation_status = 'approved' Could you please show me; where should I add these modification on the category link presentation below: <h2>Politics</h2> <?php $recent = new WP_Query("cat=31&showposts=1"); while($recent->have_posts()) : $recent->the_post();?> <b><a href="<?php the_permalink() ?>" rel="bookmark"><?php the_title(); ?></a></b> <?php the_content_limit(140, "devami &raquo;"); ?> <div class="hppostmeta"> <p><?php the_time('j F Y, H:i'); ?> | <?php the_author_posts_link(); ?></p> </div> <?php endwhile; ?> Or any general solutions will be welcomed. Thanks. BK

  Read the article

• Jquery push data to the good row.

  - by marharépa

  Hi! This is my big problem. This script put the information to a table. function handleAccountFeed(result) { var entries = result.feed.getEntries(); var outputTable = ['<table><tr>', '<th>Account Name</th>', '<th>Profile Name</th>', '<th>Profile ID</th>', '<th>Table Id</th></tr>']; for (var i = 0, entry; entry = entries[i]; ++i) { var row = [ entry.getPropertyValue('ga:AccountName'), entry.getTitle().getText(), entry.getPropertyValue('ga:ProfileId'), entry.getTableId().getValue() ].join('</td><td>'); outputTable.push('<tr><td>', row, '</td></tr>'); } outputTable.push('</table>'); document.getElementById('outputDiv').innerHTML = outputTable.join(''); } I've got an own SMARTY template, which makes a table: <table id="stuffs"> <tbody> {section name=i loop=$ownsites} <tr><td>{$ownsites[i].id}</td><td>{$ownsites[i].domain}</td><td>PLACE</td></tr> {/section} <tbody> </table> I'd like to put the TableId (getTableId().getValue()) to that row's PLACE where {$ownsites[i].domain} is equal to the jquery row's Title (getTitle().getText()) How can i do this within this jQuery script?

  Read the article

• Why aren't my threads start at the same time? Java

  - by Ada

  Hi, I have variable number of threads which are used for parallel downloading. I used this, for(int i = 0; i< sth; i++){ thrList.add(new myThread (parameters)); thrList.get(i).start(); thrList.get(i).join(); } I don't know why but they wait for each other to complete. When using threads, I am supposed get mixed print outs, since right then there are several threads running that code. However, when I print them out, they are always in order and one thread waits for the previous one to finish first. I only want them to join the main thread, not wait for each other. I noticed that when I measured time while downloading in parallel. How can I fix this? Why are they doing it in order? In my .java, there is MyThread class with run and there is Downloader class with static methods and variables. Would they be the cause of this? The static methods and variables? How can I fix this problem?

  Read the article

• Distinct() to return List<> returning Duplicates

  - by KDM

  I have a list of Filters that are passed into a webservice and I iterate over the collection and do Linq query and then add to the list of products but when I do a GroupBy and Distinct() it doesn't remove the duplicates. I am using a IEnumerable because when you use Disinct it converts it to IEnumerable. If you know how to construct this better and make my function return a type of List<Product> that would be appreciated thanks. Here is my code in C#: if (Tab == "All-Items") { List<Product> temp = new List<Product>(); List<Product> Products2 = new List<Product>(); foreach (Filter filter in Filters) { List<Product> products = (from p in db.Products where p.Discontinued == false && p.DepartmentId == qDepartment.Id join f in db.Filters on p.Id equals f.ProductId join x in db.ProductImages on p.Id equals x.ProductId where x.Dimension == "180X180" && f.Name == filter.Name /*Filter*/ select new Product { Id = p.Id, Title = p.Title, ShortDescription = p.ShortDescription, Brand = p.Brand, Model = p.Model, Image = x.Path, FriendlyUrl = p.FriendlyUrl, SellPrice = p.SellPrice, DiscountPercentage = p.DiscountPercentage, Votes = p.Votes, TotalRating = p.TotalRating }).ToList<Product>(); foreach (Product p in products) { temp.Add(p); } IEnumerable temp2 = temp.GroupBy(x => x.Id).Distinct(); IEnumerator e = temp.GetEnumerator(); while (e.MoveNext()) { Product c = e.Current as Product; Products2.Add(c); } } pf.Products = Products2;// return type must be List<Product> }

  Read the article

• SQL Server 2005: Internal Query Processor Error:

  - by Geetha

  I am trying to execute this following procedure in SQL Server 2005. I was able to execute this in my development server and when i tried to use this in the Live Server I am getting an Error "Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services". am using the same Database and the same format. when we searched in the web it shows some fixes to be used in sql server 2005 to avoid this error but my DBA has confirmed that all the patches are updated in our server. can anyone give me some clue on this. Query: create Procedure [dbo].[sample_Select] @ID as varchar(40) as Declare @Execstring as varchar(1000) set @Execstring = ' Declare @MID as varchar(40) Set @MID = '''+@ID+''' select * from ( select t1.field1, t1.field2 AS field2 , t1.field3 AS field3 , L.field1 AS field1 , L. field2 AS field2 from table1 AS t1 INNER JOIN MasterTable AS L ON L. field1 = t1. field2 where t1. field2 LIKE @MID ) as DataTable PIVOT ( Count(field2) FOR field3 IN (' Select @Execstring=@Execstring+ L.field2 +',' FROM MasterTable AS L inner join table1 AS t1 ON t1.field1= L.field2 Where t1.field2 LIKE @ID set @Execstring = stuff(@Execstring, len(@Execstring), 1, '') set @Execstring =@Execstring +')) as pivotTable' exec (@Execstring)

  Read the article

• Creating a Variable From Two Others

  - by John

  Hello, In the HTML table below, I would like to add a third column that equals $row["countSubmissions"] times 10 plus $row["countComments"]. How could I do this? Thanks in advance, John $sqlStr = "SELECT l.loginid, l.username, COALESCE(s.total, 0) AS countSubmissions, COALESCE(c.total, 0) AS countComments FROM login l LEFT JOIN ( SELECT loginid, COUNT(1) AS total FROM submission GROUP BY loginid ) s ON l.loginid = s.loginid LEFT JOIN ( SELECT loginid, COUNT(1) AS total FROM comment GROUP BY loginid ) c ON l.loginid = c.loginid GROUP BY l.loginid ORDER BY countSubmissions DESC LIMIT 10"; $result = mysql_query($sqlStr); $arr = array(); echo "<table class=\"samplesrec1edit\">"; while ($row = mysql_fetch_array($result)) { echo '<tr>'; echo '<td class="sitename1edit1"><a href="http://www...com/.../members/index.php?profile='.$row["username"].'">'.stripslashes($row["username"]).'</a></td>'; echo '<td class="sitename1edit2">'.stripslashes($row["countSubmissions"]).'</td>'; echo '<td class="sitename1edit2">'.stripslashes($row["countComments"]).'</td>'; echo '</tr>'; } echo "</table>";

  Read the article

• how to compare two tables fields name with another value in mysql?

  - by I Like PHP

  I have two tables table_school school_open_time|school_close_time|school_day 8:00 AM | 9:00PM | Monday 10:00 AM | 7:00PM | Wednesday table_college college_open_time|college_close_time|college_day 10:00 AM | 8:00PM | Monday 10:00 AM | 9:00PM | Tuesday 10:00 AM | 5:00PM | Wednesday Now I want to select school_open_time school_close time, college_open_time and college_close_time according to today (means college_day=school_day=today), and also if there is no row for a specific day in any of one table then it display blank field ( LEFT JOIN , I think I can use). Please suggest me best and optimized query for this. UPDATE: if there is no open time and close time for school then college_open_time and college_close_time has to be returned( not to be filled in database,just return) as school_open_time and school_close_time. and there always must be college_open_time and college_close_time for a given day MORE UPDATE: i m using below query SELECT college_open_time,college_close_time ,school_open_time, school_close_time FROM tbl_college LEFT JOIN tbl_school ON school_owner_id=college_owner_id WHERE college_owner_id='".$_session['user_id']."' AND college_day='".date('l',time())."'"; it return single row (left hand having some value and right hand having blank value) when there is no row of a given day in table_school, BUT display seven rows with same value on left hand side(college_open_time, college_close_time) and 6 blank row on right hand side (school_open_time and school_close_time) i need only one row when both table have a row of a given day but using above query take only first row of corresponding table_school where school_owner_id is 50(let), it not see the condition that school_day name should be given day

  Read the article

• Is it possible to aggregate over differing where clauses?

  - by BenAlabaster

  Is it possible to calculate multiple aggregates based on differing where clauses? For instance: Let's say I have two tables, one for Invoice and one for InvoiceLineItems. The invoice table has a total field for the invoice total, and each of the invoice line item records in the InvoiceLineItems table contains a field that denotes whether the line item is discountable or not. I want three sum totals, one where Discountable = 0 and one where Discountable = 1 and one where Discountable is irrelevant. Such that my output would be: InvoiceNumber Total DiscountableTotal NonDiscountableTotal ------------- ----- ----------------- -------------------- 1 53.27 27.27 16.00 2 38.94 4.76 34.18 3... The only way I've found so far is by using something like: Select i.InvoiceNumber, i.Total, t0.Total As DiscountableTotal, t1.Total As NonDiscountableTotal From Invoices i Left Join ( Select InvoiceNumber, Sum(Amount), From InvoiceLineItems Where Discountable = 0 Group By InvoiceNumber ) As t0 On i.InvoiceNumber = t0.InvoiceNumber Left Join ( Select InvoiceNumber, Sum(Amount) From InvoiceLineItems Where Discountable = 1 Group By InvoiceNumber ) As t1 On i.InvoiceNumber = t1.InvoiceNumber This seems somewhat cumbersome, it would be nice if I could do something like: Select InvoiceNumber, Sum(Amount) Where Discountable = 1 As Discountable Sum(Amount) Where Discountable = 0 As NonDiscountable Group By InvoiceNumber I realize that SQL is completely invalid, but it logically portrays what I'm trying to do... TIA P.S. I need this to run on a SQL Server 2000 instance, but I am also interested (for future reference) if/how I would achieve this on SQL Server 2005/2008.

  Read the article

• please suggest mysql query for this

  - by I Like PHP

  I HAVE TWO TABLES shown below table_joining id join_id(PK) transfer_id(FK) unit_id transfer_date joining_date 1 j_1 t_1 u_1 2010-06-05 2010-03-05 2 j_2 t_2 u_3 2010-05-10 2010-03-10 3 j_3 t_3 u_6 2010-04-10 2010-01-01 4 j_5 NULL u_3 NULL 2010-06-05 5 j_6 NULL u_4 NULL 2010-05-05 table_transfer id transfer_id(PK) pastUnitId futureUnitId effective_transfer_date 1 t_1 u_3 u_1 2010-06-05 2 t_2 u_6 u_1 2010-05-10 3 t_3 u_5 u_3 2010-04-10 now i want to know total employee detalis( using join_id) which are currently working on unit u_3 . means i want only join_id j_1 (has transfered but effective_transfer_date is future date, right now in u_3) j_2 ( tansfered and right now in `u_3` bcoz effective_transfer_date has been passed) j_6 ( right now in `u_3` and never transfered) what i need to take care of below steps( as far as i know ) <1> first need to check from table_joining whether transfer_id is NULL or not <2> if transfer_id= is NULL then see unit_id=u_3 where joining_date <=CURDATE() ( means that person already joined u_3) <3> if transfer_id is NOT NULL then go to table_transfer using transfer_id (foreign key reference) <4> now see the effective_transfer_date regrading that transfer_id whether effective_transfer_date<=CURDATE() <5> if transfer date has been passed(means transfer has been done) then return futureUnitID otherwise return pastUnitID i used two separate query but don't know how to join those query?? for step <1 ans <2 SELECT unit_id FROM table_joining WHERE joining_date<=CURDATE() AND transfer_id IS NULL AND unit_id='u_3' for step<5 SELECT IF(effective_transfer_date <= CURDATE(),futureUnitId,pastUnitId) AS currentUnitID FROM table_transfer // here how do we select only those rows which have currentUnitID='u_3' ?? please guide me the process?? i m just confused with JOINS. i think using LEFT JOIN can return the data i need, or if we use subquery value to main query? but i m not getting how to implement ...please help me. Thanks for helping me alwayz

  Read the article

• Rails - Create if record doesn't exist or else update.....Whats Best way to do this?

  - by ChrisWesAllen

  Hi, I have a create statement for some models but its creating a record within a join table regardless if the record exist. Here is what my code looks like. @user = User.find(current_user) @event = Event.find(params[:id]) for interest in @event.interests @user.choices.create(:interest => interest, :score => 4) end The problem is it creates records no matter what. I would like it to create a record if it doesnt exist, if a record does exist I would just to it to take the attribute of the found record and add or subtract 1. So, I've been looking around and I see something called find_or_create_by. My question is what happens if it finds? Preferably if it finds,I would like to take the current :score attribute and +1. SO is it possible to find or create by id? I'm not sure what attribute I would find by since the model I'm looking at is a join model which only had id foreign keys and the score attribute. I tried @user.choices.find_or_create_by_user(:user => @user.id, :interest => interest, :score => 4) but got "undefined method `find_by_user'".....ANy ideas or help?

  Read the article

• Rails - HABTM Relationship -- How Can I Find A Record Based On An Attribute Of The Associated Model

  - by ChrisWesAllen

  I have setup this HABTM relationship in the past and its worked before....Now it isnt and I'm at my wits end trying to figure out whats wrong. I've looking through the rails guides all day and cant seem to figure out what I'm doing wrong, so help would really be appreciated. I have 2 models connected through a join model and I'm trying to find records based an attribute of the associated model. Event.rb has_and_belongs_to_many :interests Interest.rb has_and_belongs_to_many :events and a join table migration that was created like create_table 'events_interests', :id => false do |t| t.column :event_id, :integer t.column :interest_id, :integer end I tried @events = Event.all(:include => :interest, :conditions => [" interest.id = ?", 4 ] ) But got the error "Association named 'interest' was not found; perhaps you misspelled it?"... which I didnt of course I tried @events = Event.interests.find(:all, :conditions => [" interest.id = ?", 4 ] ) but got the error "undefined method `interests' for #Class:0x4383348" How can I find the Events that have an interest id of 4....I'm definitely going bald from this lol

  Read the article

• Prototype or jQuery for DOM manipulation (client-side dynamic content)

  - by luiggitama

  I need to know which of these two JavaScript frameworks is better for client-side dynamic content modification for known DOM elements (by id), in terms of performance, memory usage, etc.: Prototype's $('id').update(content) jQuery's jQuery('#id').html(content) BTW, both libraries coexist with no conflict in my app, because I'm using RichFaces for JSF development, that's why I can use "jQuery" instead of "$". I have at least 20 updatable areas in my page, and for each one I prepare content (tables, option lists, etc.), based on some user-defined client-side criteria filtering or some AJAX event, etc., like this: var html = []; int idx = 0; ... html[idx++] = '<tr><td class="cell"><span class="link" title="View" onclick="myFunction('; html[idx++] = param; html[idx++] = ')"></span>'; html[idx++] = someText; html[idx++] = '</td></tr>'; ... So here comes the question, which is better to use: // Prototype's $('myId').update(html.join('')); // or jQuery's jQuery('#myId').html(html.join('')); Other needed functions are hide() and show(), which are present in both frameworks. Which is better? Also I'm needing to enable/disable form controls, and to read/set their values. Note that I know my updatable area's id (I don't need CSS selectors at this point). And I must tell that I'm saving these queried objects in some data structure for later use, so they are requested just once when the page is rendered, like this: MyData = {div1:jQuery('#id1'), div2:$('id2'), ...}; ... div1.update('content 1'); div2.html('content 2'); So, which is the best practice?

  Read the article

• Optimize INSERT / UPDATE / DELETE operation

  - by clime

  I wonder if the following script can be optimized somehow. It does write a lot to disk because it deletes possibly up-to-date rows and reinserts them. I was thinking about applying something like "insert ... on duplicate key update" and found some possibilities for single-row updates but I don't know how to apply it in the context of INSERT INTO ... SELECT query. CREATE OR REPLACE FUNCTION update_member_search_index() RETURNS VOID AS $$ DECLARE member_content_type_id INTEGER; BEGIN member_content_type_id := (SELECT id FROM django_content_type WHERE app_label='web' AND model='member'); DELETE FROM watson_searchentry WHERE content_type_id = member_content_type_id; INSERT INTO watson_searchentry (engine_slug, content_type_id, object_id, object_id_int, title, description, content, url, meta_encoded) SELECT 'default', member_content_type_id, web_member.id, web_member.id, web_member.name, '', web_user.email||' '||web_member.normalized_name||' '||web_country.name, '', '{}' FROM web_member INNER JOIN web_user ON (web_member.user_id = web_user.id) INNER JOIN web_country ON (web_member.country_id = web_country.id) WHERE web_user.is_active=TRUE; END; $$ LANGUAGE plpgsql; EDIT: Schemas of web_member, watson_searchentry, web_user, web_country: http://pastebin.com/3tRVPPVi. (content_type_id, object_id_int) in watson_searchentry is unique pair in the table but atm the index is not present (there is no use for it). This script should be run at most once a day for full rebuilds of search index.

  Read the article

• Hibernate not loading associated object

  - by Noor

  Hi, i am trying to load a hibernate object ForumMessage but in it contain another object Users and the Users object is not being loaded. My ForumMessage Mapping File: <?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <!-- Generated Jan 4, 2011 10:10:29 AM by Hibernate Tools 3.4.0.Beta1 --> <hibernate-mapping> <class name="com.BiddingSystem.Models.ForumMessage" table="FORUMMESSAGE"> <id name="ForumMessageId" type="long"> <column name="FORUMMESSAGEID" /> <generator class="native" /> </id> <property name="ForumMessage" type="java.lang.String"> <column name="FORUMMESSAGE" /> </property> <many-to-one name="User" class="com.BiddingSystem.Models.Users" fetch="join"> <column name="UserId" /> </many-to-one> <property name="DatePosted" type="java.util.Date"> <column name="DATEPOSTED" /> </property> <many-to-one name="Topic" class="com.BiddingSystem.Models.ForumTopic" fetch="join"> <column name="TopicId" /> </many-to-one> </class> </hibernate-mapping> and I am using the follwing code: Session session = gileadHibernateUtil.getSessionFactory().openSession(); SQL="from ForumMessage"; System.out.println(SQL); Query query=session.createQuery(SQL); System.out.println(query.list().size()); return new LinkedList <ForumMessage>(query.list());

  Read the article

• Is this method a good aproach to get SQL values from C#?

  - by MadBoy

  I have this little method that i use to get stuff from SQL. I either call it with varSearch = "" or varSearch = "something". I would like to know if having method written this way is best or would it be better to split it into two methods (by overloading), or maybe i could somehow parametrize whole WHERE clausule? private void sqlPobierzKontrahentDaneKlienta(ListView varListView, string varSearch) { varListView.BeginUpdate(); varListView.Items.Clear(); string preparedCommand; if (varSearch == "") { preparedCommand = @" SELECT t1.[KlienciID], CASE WHEN t2.[PodmiotRodzaj] = 'Firma' THEN t2.[PodmiotFirmaNazwa] ELSE t2.[PodmiotOsobaNazwisko] + ' ' + t2.[PodmiotOsobaImie] END AS 'Nazwa' FROM [BazaZarzadzanie].[dbo].[Klienci] t1 INNER JOIN [BazaZarzadzanie].[dbo].[Podmioty] t2 ON t1.[PodmiotID] = t2.[PodmiotID] ORDER BY t1.[KlienciID]"; } else { preparedCommand = @" SELECT t1.[KlienciID], CASE WHEN t2.[PodmiotRodzaj] = 'Firma' THEN t2.[PodmiotFirmaNazwa] ELSE t2.[PodmiotOsobaNazwisko] + ' ' + t2.[PodmiotOsobaImie] END AS 'Nazwa' FROM [BazaZarzadzanie].[dbo].[Klienci] t1 INNER JOIN [BazaZarzadzanie].[dbo].[Podmioty] t2 ON t1.[PodmiotID] = t2.[PodmiotID] WHERE t2.[PodmiotOsobaNazwisko] LIKE @searchValue OR t2.[PodmiotFirmaNazwa] LIKE @searchValue OR t2.[PodmiotOsobaImie] LIKE @searchValue ORDER BY t1.[KlienciID]"; } using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails)) using (SqlCommand sqlQuery = new SqlCommand(preparedCommand, varConnection)) { sqlQuery.Parameters.AddWithValue("@searchValue", "%" + varSearch + "%"); using (SqlDataReader sqlQueryResult = sqlQuery.ExecuteReader()) if (sqlQueryResult != null) { while (sqlQueryResult.Read()) { string varKontrahenciID = sqlQueryResult["KlienciID"].ToString(); string varKontrahent = sqlQueryResult["Nazwa"].ToString(); ListViewItem item = new ListViewItem(varKontrahenciID, 0); item.SubItems.Add(varKontrahent); varListView.Items.AddRange(new[] {item}); } } } varListView.EndUpdate(); }

  Read the article

• need help understanding a function.

  - by Adam McC

  i had previously asked for help writing/improving a function that i need to calculate a premium based on differing values for each month. the premium is split in to 12 months and earned on a percentage for each month. so if the policy start in march and we are in jan we will have earned 10 months worth. so i need to add up the monthly earning to give us the total earned. wach company wil have differeing earnings values for each month. my original code is Here. its ghastly and slow hence the request for help. and i was presented with the following code. the code works but returns stupendously large figures. begin set @begin=datepart(month,@outdate) set @end=datepart(month,@experiencedate) ;with a as ( select *, case calmonth when 'january' then 1 when 'february' then 2 when 'march' then 3 when 'april' then 4 when 'may' then 5 when 'june' then 6 when 'july' then 7 when 'august' then 8 when 'september' then 9 when 'october' then 10 when 'november' then 11 when 'december' then 12 end as Mnth from tblearningpatterns where clientname=@client and earningpattern=@pattern ) , b as ( select earningvalue, Mnth, earningvalue as Ttl from a where Mnth=@begin union all select a.earningvalue, a.Mnth, cast(b.Ttl*a.earningvalue as decimal(15,3)) as Ttl from a inner join b on a.Mnth=b.Mnth+1 where a.Mnth<=@end ) select @earningvalue= Ttl from b inner join ( select max(Mnth) as Mnth from b ) c on b.Mnth=c.Mnth option(maxrecursion 12) SET @earnedpremium = @earningvalue*@premium end can someone please help me out?

  Read the article

• Are multiline queries sql-injection safe?

  - by acmatos

  This might be a stupid question. Or maybe my hacking skills are limited (I don't practice them at all). I have a query that looks like this: <?php $query =<<<eot SELECT table_x.field1, table_x.field2, table_y.*, table_z.field4 FROM ( SELECT ... ) as table_y LEFT JOIN table_x ON table_x.field1 = table_y.field_x LEFT JOIN table_z ON table_z.field1 = table_y.field_z WHERE table_x.field3 = '$something' AND table_z.field4 = '1' AND table_z.field5 = '2' eot; ?> I have a lot of other tests on $something before it gets used, like $something = explode(' ',$something); (which later result in a string) none of them intend to prevent injection but they make it hard for the given injection to get as is to the actual query. However, there are ways. We all know how easy it is to replace a space for something else which is still valid.. So, it's not really a problem to make a potentially harmful piece of SQL reach that $something... But is there any way to comment the rest of the original query string if it is multi-line? I can comment AND table_z.field4 = '1' using ;-- but can't comment the following AND table_z.field5 = '2' Is it possible to open a multi-line comment /* without closing it or something looked like and therefore allow the injection to ignore the multi-line query?

  Read the article

• Selecting records in SQL that have the minimum value for that record based on another field

  - by Ryan

  I have a set of data, and while the number of fields and tables it joins with is quite complex, I believe I can distill my problem down using the required fields/tables here for illustration regarding this particular problem. I have three tables: ClientData, Sources, Prices Here is what my current query looks like before selecting the minimum value: select c.RecordID, c.Description, s.Source, p.Price, p.Type, p.Weight from ClientData c inner join Sources s ON c.RecordID = s.RecordID inner join Prices p ON s.SourceID = p.SourceID This produces the following result: RecordID Description Source Price Type Weight ============================================================= 001002003 ABC Common Stock Vendor 1 104.5 Close 1 001002003 ABC Common Stock Vendor 1 103 Bid 2 001002003 ABC Common Stock Vendor 2 106 Close 1 001002003 ABC Common Stock Vendor 2 100 Unknwn 0 111222333 DEF Preferred Stk Vendor 3 80 Bid 2 111222333 DEF Preferred Stk Vendor 3 82 Mid 3 111222333 DEF Preferred Stk Vendor 2 81 Ask 4 What I am trying to do is display prices that belong to the same record which have the minimum non-zero weight for that record (so the weight must be greater than 0, but it has to be the minimum from amongst the remaining weights). So in the above example, for record 001002003 I would want to show the close prices from Vendor 1 and Vendor 2 because they both have a weight of 1 (the minimum weight for that record). But for 111222333 I would want to show just the bid price from Vendor 3 because its weight of 2 is the minimum, non-zero for that record. The result that I'm after would like like: RecordID Description Source Price Type Weight ============================================================= 001002003 ABC Common Stock Vendor 1 104.5 Close 1 001002003 ABC Common Stock Vendor 2 106 Close 1 111222333 DEF Preferred Stk Vendor 3 80 Bid 2 Any ideas on how to achieve this? EDIT: This is for SQL Compact Edition.

  Read the article

• Incorrect usage of UPDATE and ORDER BY

  - by nico55555

  I have written some code to update certain rows of a table with a decreasing sequence of numbers. To select the correct rows I have to JOIN two tables. The last row in the table needs to have a value of 0, the second last -1 and so on. To achieve this I use ORDER BY DESC. Unfortunately my code brings up the following error: Incorrect usage of UPDATE and ORDER BY My reading suggests that I can't use UPDATE, JOIN and ORDER BY together. I've read that maybe subqueries might help? I don't really have any idea how to change my code to do this. Perhaps someone could post a modified version that will work? while($row = mysql_fetch_array( $result )) { $products_id = $row['products_id']; $products_stock_attributes = $row['products_stock_attributes']; mysql_query("SET @i = 0"); $result2 = mysql_query("UPDATE orders_products op, orders ord SET op.stock_when_purchased = (@i:=(@i - op.products_quantity)) WHERE op.orders_id = ord.orders_id AND op.products_id = '$products_id' AND op.products_stock_attributes = '$products_stock_attributes' AND op.stock_when_purchased < 0 AND ord.orders_status = 2 ORDER BY orders_products_id DESC") or die(mysql_error()); }

  Read the article

• How can I combine a LINQ query with an IQueryable<Guid>

  - by John

  I have a LINQ query that uses 1 table + a large number of views. I'd like to be able to write something like this: IQueryable<Guid> mostViewedWriters; switch (datePicker) { case DatePicker.Last12Hours: mostViewedWriters = from x in context.tempMostViewed12Hours select x.GuidId; break; case DatePicker.Last24Hours: mostViewedWriters = from x in context.tempMostViewed12Hours select x.GuidId; break; case DatePicker.Last36Hours: mostViewedWriters = from x in context.tempMostViewed12Hours select x.GuidId; break; } var query = from x1 in context.Articles join x2 in context.Authors on x1.AuthorId == x2.AuthorId join x3 in mostViewedWriters on x2.AuthorId == x3.Id select new { x2.AuthorName, x1.ArticleId, x1.ArticleTitle }; The above C# is pseudo-code written to protect the innocent (me). The gist of the question is this: I have a query that is related to the results of a view. That view, however, could be one of many different views. All the views return the same data type. I thought that I might be able to create an IQueryable that would contain the Ids that I need and use that query. Alas, that effort has stalled.

  Read the article

• Image download from mysql results

  - by rozatrra

  i need to give my users the opportunity to download all the images I display in my project. images are displayed from a mysql query like this: $query = mysql_query("SELECT tl.customername, tl.visitdate, tl.employeename, pz.webpath from table tl inner join pictures pz on pz.visitid = tl.visitid and pz.groupid = tl.groupid inner join agenti ag on ag.idh = tl.employeeid WHERE tl.visitdate >= '$from' AND tl.visitdate <= '$to' AND tl.employeename like '$r_employee' AND tl.customerowner like '$r_customer' AND tl.customername like '$r_customername' AND tl.visitdate like '$r_date' group by pz.webpath order by tl.customername") or die(mysql_error()); while( $associate = mysql_fetch_assoc($query)) { echo '<li> <figure> <img src="../core/includes/timthumb.php?src='.$associate['webpath'].'&w=200&h=200" /> <figcaption> <h3>'.$associate['customername'].'</h3> <h6>'.$associate['employeename'].'</h6> <h6>'.$associate['visitdate'].' </h6> '; echo '<a class="fancybox" rel="gallery" href="'.$associate['webpath'].'" title=" '.$associate['visitdate'].' / '.$associate['customername'].'">Big picture</i></a>'; echo '</figcaption> </figure> </li>'; $zip->addFromString(pathinfo ( urldecode($associate['webpath']), PATHINFO_BASENAME), urldecode($associate['webpath'])); } How can i add a download button which will save all the images as zip on user computer?

  Read the article

• Categorize data without consolidating?

  - by sqlnoob

  I have a table with about 1000 records and 2000 columns. What I want to do is categorize each row such that all records with equal column values for all columns except 'ID' are given a category ID. My final answer would look like: ID A B C ..... Category ID 1 1 0 3 1 2 2 1 3 2 3 1 0 3 1 4 2 1 3 2 5 4 5 6 3 6 4 5 6 3 where all columns (besides ID) are equal for IDs 1,3 so they get the same category ID and so on. I guess my thought was to just write a SQL query that does a group by on every single column besides 'ID' and assign a number to each group and then join back to my original table. My current input is a text file, and I have SAS, MS Access, and Excel to work with. (I could use proc sql from within SAS). Before I go this route and construct the whole query, I was just wondering if there was a better way to do this? It will take some work just to write the query, and I'm not even sure if it is practical to join on 2000 columns (never tried), so I thought I'd ask for ideas before I got too far down the wrong path. EDIT: I just realized my title doesn't really make sense. What I was originally thinking was "Is there a way I can group by and categorize at the same time without actually consolidating into groups?"

  Read the article

• FreeBSD performance tuning. Sysctls, loader.conf, kernel.

  - by SaveTheRbtz

  I wanted to share knowledge of tuning FreeBSD via sysctls, so i'm posting them with comments. Based on Igor Sysoev (author of nginx) presentation about FreeBSD tuning up to 100,000-200,000 active connections. Sysctls are for 7.x FreeBSD. Since 7.2 amd64 some of them are tuned well by default. Prior 7.0 some of them are boot only (set via /boot/loader.conf) or does not exist at all. Highload web server sysctls: # Max. backlog size kern.ipc.somaxconn=4096 # Shared memory // 7.2+ can use shared memory > 2Gb kern.ipc.shmmax=2147483648 # Sockets kern.ipc.maxsockets=204800 # Do not use lager sockbufs on 8.0 # ( http://old.nabble.com/Significant-performance-regression-for-increased-maxsockbuf-on-8.0-RELEASE-tt26745981.html#a26745981 ) kern.ipc.maxsockbuf=262144 # Recive clusters (on amd64 7.2+ 65k is default) # For such high value vm.kmem_size must be increased to 3G #kern.ipc.nmbclusters=229376 # Jumbo pagesize(4k/8k) clusters # Used as general packet storage for jumbo frames # can be monitored via `netstat -m` #kern.ipc.nmbjumbop=192000 # Jumbo 9k/16k clusters # If you are using them #kern.ipc.nmbjumbo9=24000 #kern.ipc.nmbjumbo16=10240 # Every socket is a file, so increase them kern.maxfiles=204800 kern.maxfilesperproc=200000 kern.maxvnodes=200000 # Turn off receive autotuning #net.inet.tcp.recvbuf_auto=0 # Small receive space, only usable on http-server, on file server this # should be increased to 65535 or even more #net.inet.tcp.recvspace=8192 # Small send space is useful for http servers that serve small files # Autotuned since 7.x net.inet.tcp.sendspace=16384 # This should be enabled if you going to use big spaces (>64k) #net.inet.tcp.rfc1323=1 # Turn this off on highspeed, lossless connections (LAN 1Gbit+) #net.inet.tcp.delayed_ack=0 # This feature is useful if you are serving data over modems, Gigabit Ethernet, # or even high speed WAN links (or any other link with a high bandwidth delay product), # especially if you are also using window scaling or have configured a large send window. # You can try setting it to 0 on fileserver with 1GBit+ interfaces # Automatically disables on small RTT ( http://www.freebsd.org/cgi/cvsweb.cgi/src/sys/netinet/tcp_subr.c?#rev1.237 ) #net.inet.tcp.inflight.enable=0 # Disable randomizing of ports to avoid false RST # Before usage check SA here www.bsdcan.org/2006/papers/ImprovingTCPIP.pdf # (it's also says that port randomization auto-disables at some conn.rates, but I didn't tested it thou) #net.inet.ip.portrange.randomized=0 # Increase portrange # For outgoing connections only. Good for seed-boxes and ftp servers. net.inet.ip.portrange.first=1024 net.inet.ip.portrange.last=65535 # Security net.inet.ip.redirect=0 net.inet.ip.sourceroute=0 net.inet.ip.accept_sourceroute=0 net.inet.icmp.maskrepl=0 net.inet.icmp.log_redirect=0 net.inet.icmp.drop_redirect=1 net.inet.tcp.drop_synfin=1 # Security net.inet.udp.blackhole=1 net.inet.tcp.blackhole=2 # Increases default TTL, sometimes useful # Default is 64 net.inet.ip.ttl=128 # Lessen max segment life to conserve resources # ACK waiting time in miliseconds (default: 30000 from RFC) net.inet.tcp.msl=5000 # Max bumber of timewait sockets net.inet.tcp.maxtcptw=40960 # Don't use tw on local connections # As of 15 Apr 2009. Igor Sysoev says that nolocaltimewait has some buggy realization. # So disable it or now till get fixed #net.inet.tcp.nolocaltimewait=1 # FIN_WAIT_2 state fast recycle net.inet.tcp.fast_finwait2_recycle=1 # Time before tcp keepalive probe is sent # default is 2 hours (7200000) #net.inet.tcp.keepidle=60000 # Should be increased until net.inet.ip.intr_queue_drops is zero net.inet.ip.intr_queue_maxlen=4096 # Interrupt handling via multiple CPU, but with context switch. # You can play with it. Default is 1; #net.isr.direct=0 # This is for routers only #net.inet.ip.forwarding=1 #net.inet.ip.fastforwarding=1 # This speed ups dummynet when channel isn't saturated net.inet.ip.dummynet.io_fast=1 # Increase dummynet(4) hash #net.inet.ip.dummynet.hash_size=2048 #net.inet.ip.dummynet.max_chain_len # Should be increased when you have A LOT of files on server # (Increase until vfs.ufs.dirhash_mem becames lower) vfs.ufs.dirhash_maxmem=67108864 # Explicit Congestion Notification (see http://en.wikipedia.org/wiki/Explicit_Congestion_Notification) net.inet.tcp.ecn.enable=1 # Flowtable - flow caching mechanism # Useful for routers #net.inet.flowtable.enable=1 #net.inet.flowtable.nmbflows=65535 # Extreme polling tuning #kern.polling.burst_max=1000 #kern.polling.each_burst=1000 #kern.polling.reg_frac=100 #kern.polling.user_frac=1 #kern.polling.idle_poll=0 # IPFW dynamic rules and timeouts tuning # Increase dyn_buckets till net.inet.ip.fw.curr_dyn_buckets is lower net.inet.ip.fw.dyn_buckets=65536 net.inet.ip.fw.dyn_max=65536 net.inet.ip.fw.dyn_ack_lifetime=120 net.inet.ip.fw.dyn_syn_lifetime=10 net.inet.ip.fw.dyn_fin_lifetime=2 net.inet.ip.fw.dyn_short_lifetime=10 # Make packets pass firewall only once when using dummynet # i.e. packets going thru pipe are passing out from firewall with accept #net.inet.ip.fw.one_pass=1 # shm_use_phys Wires all shared pages, making them unswappable # Use this to lessen Virtual Memory Manager's work when using Shared Mem. # Useful for databases #kern.ipc.shm_use_phys=1 /boot/loader.conf: # Accept filters for data, http and DNS requests # Usefull when your software uses select() instead of kevent/kqueue or when you under DDoS # DNS accf available on 8.0+ accf_data_load="YES" accf_http_load="YES" accf_dns_load="YES" # Async IO system calls aio_load="YES" # Adds NCQ support in FreeBSD # WARNING! all ad[0-9]+ devices will be renamed to ada[0-9]+ # 8.0+ only #ahci_load= #siis_load= # Increase kernel memory size to 3G. # # Use ONLY if you have KVA_PAGES in kernel configuration, and you have more than 3G RAM # Otherwise panic will happen on next reboot! # # It's required for high buffer sizes: kern.ipc.nmbjumbop, kern.ipc.nmbclusters, etc # Useful on highload stateful firewalls, proxies or ZFS fileservers # (FreeBSD 7.2+ amd64 users: Check that current value is lower!) #vm.kmem_size="3G" # Older versions of FreeBSD can't tune maxfiles on the fly #kern.maxfiles="200000" # Useful for databases # Sets maximum data size to 1G # (FreeBSD 7.2+ amd64 users: Check that current value is lower!) #kern.maxdsiz="1G" # Maximum buffer size(vfs.maxbufspace) # You can check current one via vfs.bufspace # Should be lowered/upped depending on server's load-type # Usually decreased to preserve kmem # (default is 200M) #kern.maxbcache="512M" # Sendfile buffers # For i386 only #kern.ipc.nsfbufs=10240 # syncache Hash table tuning net.inet.tcp.syncache.hashsize=1024 net.inet.tcp.syncache.bucketlimit=100 # Incresed hostcache net.inet.tcp.hostcache.hashsize="16384" net.inet.tcp.hostcache.bucketlimit="100" # TCP control-block Hash table tuning net.inet.tcp.tcbhashsize=4096 # Enable superpages, for 7.2+ only # Also read http://lists.freebsd.org/pipermail/freebsd-hackers/2009-November/030094.html vm.pmap.pg_ps_enabled=1 # Usefull if you are using Intel-Gigabit NIC #hw.em.rxd=4096 #hw.em.txd=4096 #hw.em.rx_process_limit="-1" # Also if you have ALOT interrupts on NIC - play with following parameters # NOTE: You should set them for every NIC #dev.em.0.rx_int_delay: 250 #dev.em.0.tx_int_delay: 250 #dev.em.0.rx_abs_int_delay: 250 #dev.em.0.tx_abs_int_delay: 250 # There is also multithreaded version of em drivers can be found here: # http://people.yandex-team.ru/~wawa/ # # for additional em monitoring and statistics use # `sysctl dev.em.0.stats=1 ; dmesg` # #Same tunings for igb #hw.igb.rxd=4096 #hw.igb.txd=4096 #hw.igb.rx_process_limit=100 # Some useful netisr tunables. See sysctl net.isr #net.isr.defaultqlimit=4096 #net.isr.maxqlimit: 10240 # Bind netisr threads to CPUs #net.isr.bindthreads=1 # Nicer boot logo =) loader_logo="beastie" And finally here is my additions to GENERIC kernel # Just some of them, see also # cat /sys/{i386,amd64,}/conf/NOTES # This one useful only on i386 #options KVA_PAGES=512 # You can play with HZ in environments with high interrupt rate (default is 1000) # 100 is for my notebook to prolong it's battery life #options HZ=100 # Polling is goot on network loads with high packet rates and low-end NICs # NB! Do not enable it if you want more than one netisr thread #options DEVICE_POLLING # Eliminate datacopy on socket read-write # To take advantage with zero copy sockets you should have an MTU of 8K(amd64) # (4k for i386). This req. is only for receiving data. # Read more in man zero_copy_sockets #options ZERO_COPY_SOCKETS # Support TCP sign. Used for IPSec options TCP_SIGNATURE options IPSEC # This ones can be loaded as modules. They described in loader.conf section #options ACCEPT_FILTER_DATA #options ACCEPT_FILTER_HTTP # Adding ipfw, also can be loaded as modules options IPFIREWALL options IPFIREWALL_VERBOSE options IPFIREWALL_VERBOSE_LIMIT=10 options IPFIREWALL_DEFAULT_TO_ACCEPT options IPFIREWALL_FORWARD # Adding kernel NAT options IPFIREWALL_NAT options LIBALIAS # Traffic shaping options DUMMYNET # Divert, i.e. for userspace NAT options IPDIVERT # This is for OpenBSD's pf firewall device pf device pflog # pf's QoS - ALTQ options ALTQ options ALTQ_CBQ # Class Bases Queuing (CBQ) options ALTQ_RED # Random Early Detection (RED) options ALTQ_RIO # RED In/Out options ALTQ_HFSC # Hierarchical Packet Scheduler (HFSC) options ALTQ_PRIQ # Priority Queuing (PRIQ) options ALTQ_NOPCC # Required for SMP build # Pretty console # Manual can be found here http://forums.freebsd.org/showthread.php?t=6134 #options VESA #options SC_PIXEL_MODE # Disable reboot on Ctrl Alt Del #options SC_DISABLE_REBOOT # Change normal|kernel messages color options SC_NORM_ATTR=(FG_GREEN|BG_BLACK) options SC_KERNEL_CONS_ATTR=(FG_YELLOW|BG_BLACK) # More scroll space options SC_HISTORY_SIZE=8192 # Adding hardware crypto device device crypto device cryptodev # Useful network interfaces device vlan device tap #Virtual Ethernet driver device gre #IP over IP tunneling device if_bridge #Bridge interface device pfsync #synchronization interface for PF device carp #Common Address Redundancy Protocol device enc #IPsec interface device lagg #Link aggregation interface device stf #IPv4-IPv6 port # Also for my notebook, but may be used with Opteron #device amdtemp # Support for ECMP. More than one route for destination # Works even with default route so one can use it as LB for two ISP # For now code is unstable and panics (panic: rtfree 2) on route deletions. #options RADIX_MPATH # Multicast routing #options MROUTING #options PIM # DTrace options KDTRACE_HOOKS # all architectures - enable general DTrace hooks options DDB_CTF # all architectures - kernel ELF linker loads CTF data #options KDTRACE_FRAME # amd64-only # Adaptive spining in lockmgr (8.x+) # See http://www.mail-archive.com/[email protected]/msg10782.html options ADAPTIVE_LOCKMGRS # UTF-8 in console (9.x+) #options TEKEN_UTF8 #options TEKEN_XTERM # NCQ support # WARNING! all ad[0-9]+ devices will be renamed to ada[0-9]+ #options ATA_CAM # FreeBSD 9+ # Deadlock resolver thread # For additional information see http://www.mail-archive.com/[email protected]/msg18124.html #options DEADLKRES PS. Also most of FreeBSD's limits can be monitored by # vmstat -z and # limits PPS. variety of network counters can be monitored via # netstat -s In FreeBSD-9 netstat's -Q option appeared, try following command to display netisr stats # netstat -Q PPPS. also see # man 7 tuning PPPPS. I wanted to thank FreeBSD community, especially author of nginx - Igor Sysoev, nginx-ru@ and FreeBSD-performance@ mailing lists for providing useful information about FreeBSD tuning. So here is the question: What tunings are you using on yours FreeBSD servers? You can also post your /etc/sysctl.conf, /boot/loader.conf, kernel options, etc with description of its' meaning (do not copy-paste from sysctl -d). Don't forget to specify server type (web, smb, gateway, etc) Let's share experience!

  Read the article

• saslauthd + PostFix producing password verification and authentication errors

  - by Aram Papazian

  So I'm trying to setup PostFix while using SASL (Cyrus variety preferred, I was using dovecot earlier but I'm switching from dovecot to courier so I want to use cyrus instead of dovecot) but I seem to be having issues. Here are the errors I'm receiving: ==> mail.log <== Aug 10 05:11:49 crazyinsanoman postfix/smtpd[779]: warning: SASL authentication failure: Password verification failed Aug 10 05:11:49 crazyinsanoman postfix/smtpd[779]: warning: ipname[xx.xx.xx.xx]: SASL PLAIN authentication failed: authentication failure ==> mail.info <== Aug 10 05:11:49 crazyinsanoman postfix/smtpd[779]: warning: SASL authentication failure: Password verification failed Aug 10 05:11:49 crazyinsanoman postfix/smtpd[779]: warning: ipname[xx.xx.xx.xx]: SASL PLAIN authentication failed: authentication failure ==> mail.warn <== Aug 10 05:11:49 crazyinsanoman postfix/smtpd[779]: warning: SASL authentication failure: Password verification failed Aug 10 05:11:49 crazyinsanoman postfix/smtpd[779]: warning: ipname[xx.xx.xx.xx]: SASL PLAIN authentication failed: authentication failure I tried $testsaslauthd -u xxxx -p xxxx 0: OK "Success." So I know that the password/user I'm using is correct. I'm thinking that most likely I have a setting wrong somewhere, but can't seem to find where. Here is my files. Here is my main.cf for postfix: # See /usr/share/postfix/main.cf.dist for a commented, more complete version # Debian specific: Specifying a file name will cause the first # line of that file to be used as the name. The Debian default # is /etc/mailname. myorigin = /etc/mailname # This is already done in /etc/mailname #myhostname = crazyinsanoman.xxxxx.com smtpd_banner = $myhostname ESMTP $mail_name #biff = no # appending .domain is the MUA's job. #append_dot_mydomain = no readme_directory = /usr/share/doc/postfix # TLS parameters smtpd_tls_cert_file = /etc/postfix/smtpd.cert smtpd_tls_key_file = /etc/postfix/smtpd.key smtpd_use_tls = yes smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache # Relay smtp through another server or leave blank to do it yourself #relayhost = smtp.yourisp.com # Network details; Accept connections from anywhere, and only trust this machine mynetworks = 127.0.0.0/8 inet_interfaces = all #mynetworks_style = host #As we will be using virtual domains, these need to be empty local_recipient_maps = mydestination = # how long if undelivered before sending "delayed mail" warning update to sender delay_warning_time = 4h # will it be a permanent error or temporary unknown_local_recipient_reject_code = 450 # how long to keep message on queue before return as failed. # some have 3 days, I have 16 days as I am backup server for some people # whom go on holiday with their server switched off. maximal_queue_lifetime = 7d # max and min time in seconds between retries if connection failed minimal_backoff_time = 1000s maximal_backoff_time = 8000s # how long to wait when servers connect before receiving rest of data smtp_helo_timeout = 60s # how many address can be used in one message. # effective stopper to mass spammers, accidental copy in whole address list # but may restrict intentional mail shots. smtpd_recipient_limit = 16 # how many error before back off. smtpd_soft_error_limit = 3 # how many max errors before blocking it. smtpd_hard_error_limit = 12 # Requirements for the HELO statement smtpd_helo_restrictions = permit_mynetworks, warn_if_reject reject_non_fqdn_hostname, reject_invalid_hostname, permit # Requirements for the sender details smtpd_sender_restrictions = permit_mynetworks, warn_if_reject reject_non_fqdn_sender, reject_unknown_sender_domain, reject_unauth_pipelining, permit # Requirements for the connecting server smtpd_client_restrictions = reject_rbl_client sbl.spamhaus.org, reject_rbl_client blackholes.easynet.nl, reject_rbl_client dnsbl.njabl.org # Requirement for the recipient address smtpd_recipient_restrictions = reject_unauth_pipelining, permit_mynetworks, reject_non_fqdn_recipient, reject_unknown_recipient_domain, reject_unauth_destination, permit smtpd_data_restrictions = reject_unauth_pipelining # require proper helo at connections smtpd_helo_required = yes # waste spammers time before rejecting them smtpd_delay_reject = yes disable_vrfy_command = yes # not sure of the difference of the next two # but they are needed for local aliasing alias_maps = hash:/etc/postfix/aliases alias_database = hash:/etc/postfix/aliases # this specifies where the virtual mailbox folders will be located virtual_mailbox_base = /var/spool/mail/vmail # this is for the mailbox location for each user virtual_mailbox_maps = mysql:/etc/postfix/mysql_mailbox.cf # and this is for aliases virtual_alias_maps = mysql:/etc/postfix/mysql_alias.cf # and this is for domain lookups virtual_mailbox_domains = mysql:/etc/postfix/mysql_domains.cf # this is how to connect to the domains (all virtual, but the option is there) # not used yet # transport_maps = mysql:/etc/postfix/mysql_transport.cf # Setup the uid/gid of the owner of the mail files - static:5000 allows virtual ones virtual_uid_maps = static:5000 virtual_gid_maps = static:5000 inet_protocols=all # Cyrus SASL Support smtpd_sasl_path = smtpd smtpd_sasl_local_domain = xxxxx.com ####################### ## OLD CONFIGURATION ## ####################### #myorigin = /etc/mailname #mydestination = crazyinsanoman.xxxxx.com, localhost, localhost.localdomain #mailbox_size_limit = 0 #recipient_delimiter = + #html_directory = /usr/share/doc/postfix/html message_size_limit = 30720000 #virtual_alias_domains = ##virtual_alias_maps = hash:/etc/postfix/virtual #virtual_mailbox_base = /home/vmail ##luser_relay = webmaster #smtpd_sasl_type = dovecot #smtpd_sasl_path = private/auth smtpd_sasl_auth_enable = yes smtpd_sasl_security_options = noanonymous broken_sasl_auth_clients = yes #smtpd_sasl_authenticated_header = yes smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination #virtual_create_maildirsize = yes #virtual_maildir_extended = yes #proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks $virtual_mailbox_limit_maps #virtual_transport = dovecot #dovecot_destination_recipient_limit = 1 Here is my master.cf: # # Postfix master process configuration file. For details on the format # of the file, see the master(5) manual page (command: "man 5 master"). # # Do not forget to execute "postfix reload" after editing this file. # # ========================================================================== # service type private unpriv chroot wakeup maxproc command + args # (yes) (yes) (yes) (never) (100) # ========================================================================== smtp inet n - - - - smtpd submission inet n - - - - smtpd -o smtpd_tls_security_level=encrypt -o smtpd_sasl_auth_enable=yes -o smtpd_client_restrictions=permit_sasl_authenticated,reject # -o milter_macro_daemon_name=ORIGINATING #smtps inet n - - - - smtpd # -o smtpd_tls_wrappermode=yes # -o smtpd_sasl_auth_enable=yes # -o smtpd_client_restrictions=permit_sasl_authenticated,reject # -o milter_macro_daemon_name=ORIGINATING #628 inet n - - - - qmqpd pickup fifo n - - 60 1 pickup cleanup unix n - - - 0 cleanup qmgr fifo n - n 300 1 qmgr #qmgr fifo n - - 300 1 oqmgr tlsmgr unix - - - 1000? 1 tlsmgr rewrite unix - - - - - trivial-rewrite bounce unix - - - - 0 bounce defer unix - - - - 0 bounce trace unix - - - - 0 bounce verify unix - - - - 1 verify flush unix n - - 1000? 0 flush proxymap unix - - n - - proxymap proxywrite unix - - n - 1 proxymap smtp unix - - - - - smtp # When relaying mail as backup MX, disable fallback_relay to avoid MX loops relay unix - - - - - smtp -o smtp_fallback_relay= # -o smtp_helo_timeout=5 -o smtp_connect_timeout=5 showq unix n - - - - showq error unix - - - - - error retry unix - - - - - error discard unix - - - - - discard local unix - n n - - local virtual unix - n n - - virtual lmtp unix - - - - - lmtp anvil unix - - - - 1 anvil scache unix - - - - 1 scache # # ==================================================================== # Interfaces to non-Postfix software. Be sure to examine the manual # pages of the non-Postfix software to find out what options it wants. # # Many of the following services use the Postfix pipe(8) delivery # agent. See the pipe(8) man page for information about ${recipient} # and other message envelope options. # ==================================================================== # # maildrop. See the Postfix MAILDROP_README file for details. # Also specify in main.cf: maildrop_destination_recipient_limit=1 # maildrop unix - n n - - pipe flags=DRhu user=vmail argv=/usr/bin/maildrop -d ${recipient} # # ==================================================================== # # Recent Cyrus versions can use the existing "lmtp" master.cf entry. # # Specify in cyrus.conf: # lmtp cmd="lmtpd -a" listen="localhost:lmtp" proto=tcp4 # # Specify in main.cf one or more of the following: # mailbox_transport = lmtp:inet:localhost # virtual_transport = lmtp:inet:localhost # # ==================================================================== # # Cyrus 2.1.5 (Amos Gouaux) # Also specify in main.cf: cyrus_destination_recipient_limit=1 # cyrus unix - n n - - pipe user=cyrus argv=/cyrus/bin/deliver -e -r ${sender} -m ${extension} ${user} # # ==================================================================== # Old example of delivery via Cyrus. # #old-cyrus unix - n n - - pipe # flags=R user=cyrus argv=/cyrus/bin/deliver -e -m ${extension} ${user} # # ==================================================================== # # See the Postfix UUCP_README file for configuration details. # uucp unix - n n - - pipe flags=Fqhu user=uucp argv=uux -r -n -z -a$sender - $nexthop!rmail ($recipient) # # Other external delivery methods. # ifmail unix - n n - - pipe flags=F user=ftn argv=/usr/lib/ifmail/ifmail -r $nexthop ($recipient) bsmtp unix - n n - - pipe flags=Fq. user=bsmtp argv=/usr/lib/bsmtp/bsmtp -t$nexthop -f$sender $recipient scalemail-backend unix - n n - 2 pipe flags=R user=scalemail argv=/usr/lib/scalemail/bin/scalemail-store ${nexthop} ${user} ${extension} mailman unix - n n - - pipe flags=FR user=list argv=/usr/lib/mailman/bin/postfix-to-mailman.py ${nexthop} ${user} #dovecot unix - n n - - pipe # flags=DRhu user=vmail:vmail argv=/usr/lib/dovecot/deliver -d ${recipient} Here is what I'm using for /etc/postfix/sasl/smtpd.conf log_level: 7 pwcheck_method: saslauthd pwcheck_method: auxprop mech_list: PLAIN LOGIN CRAM-MD5 DIGEST-MD5 allow_plaintext: true auxprop_plugin: mysql sql_hostnames: 127.0.0.1 sql_user: xxxxx sql_passwd: xxxxx sql_database: maildb sql_select: select crypt from users where id = '%u' As you can see I'm trying to use mysql as my authentication method. The password in 'users' is set through the 'ENCRYPT()' function. I also followed the methods found in http://www.jimmy.co.at/weblog/?p=52 in order to redo /var/spool/postfix/var/run/saslauthd as that seems to be a lot of people's problems, but that didn't help at all. Also, here is my /etc/default/saslauthd START=yes DESC="SASL Authentication Daemon" NAME="saslauthd" # Which authentication mechanisms should saslauthd use? (default: pam) # # Available options in this Debian package: # getpwent -- use the getpwent() library function # kerberos5 -- use Kerberos 5 # pam -- use PAM # rimap -- use a remote IMAP server # shadow -- use the local shadow password file # sasldb -- use the local sasldb database file # ldap -- use LDAP (configuration is in /etc/saslauthd.conf) # # Only one option may be used at a time. See the saslauthd man page # for more information. # # Example: MECHANISMS="pam" MECHANISMS="pam" MECH_OPTIONS="" THREADS=5 OPTIONS="-c -m /var/spool/postfix/var/run/saslauthd -r" I had heard that potentially changing MECHANISM to MECHANISMS="mysql" but obviously that didn't help as is shown by the options listed above and also by trying it out anyway in case the documentation was outdated. So, I'm now at a loss... I have no idea where to go from here or what steps I need to do to get this working =/ Anyone have any ideas? EDIT: Here is the error that is coming from auth.log ... I don't know if this will help at all, but here you go: Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql auxprop plugin using mysql engine Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin Parse the username [email protected] Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin try and connect to a host Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin trying to open db 'maildb' on host '127.0.0.1' Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin Parse the username [email protected] Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin try and connect to a host Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin trying to open db 'maildb' on host '127.0.0.1' Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: begin transaction Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin create statement from userPassword user xxxxxx.com Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin doing query select crypt from users where id = '[email protected]'; Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin create statement from cmusaslsecretPLAIN user xxxxxx.com Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin doing query select crypt from users where id = '[email protected]'; Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: commit transaction Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin Parse the username [email protected] Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin try and connect to a host Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin trying to open db 'maildb' on host '127.0.0.1' Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin Parse the username [email protected] Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin try and connect to a host Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin trying to open db 'maildb' on host '127.0.0.1' Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin Parse the username [email protected] Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin try and connect to a host Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin trying to open db 'maildb' on host '127.0.0.1' Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin Parse the username [email protected] Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin try and connect to a host Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin trying to open db 'maildb' on host '127.0.0.1' Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: begin transaction Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin create statement from userPassword user xxxxxx.com Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin doing query select crypt from users where id = '[email protected]'; Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin create statement from cmusaslsecretPLAIN user xxxxxx.com Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin doing query select crypt from users where id = '[email protected]'; Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: commit transaction Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin Parse the username [email protected] Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin try and connect to a host Aug 11 17:19:56 crazyinsanoman postfix/smtpd[9503]: sql plugin trying to open db 'maildb' on host '127.0.0.1'

  Read the article

< Previous Page | 162 163 164 165 166 167 168 169 170 171 172 173  | Next Page >