Generate invoice number from 0 to so on for ordered products in orders table in php mysqli I am trying to generate invoice number in orders table, like if user select 1 or multiple products and add it to cart then place order then invoice number should be same in our orders table for that 1 order in multiple rows according to products.
I was using function mt_rand(); but it generates randomly. I want to generate numbers from 0 to so on.
Like, 1, 2, 3, 4, 5, 6, 7 and so on.
I've also tried this to generate invoice number.
$i++

$squery = "SELECT * FROM cart WHERE user_id = '$user_id'";
$sresult = $this->db->select($squery);
if($sresult){
    $totalPrice = 0;
    $i = 0;
    while($svalue = $sresult->fetch_assoc()){
        $i++;
        $pro_id = $svalue['pro_id'];
        $pro_name = $svalue['pro_name'];
        $quantity = $svalue['quantity'];
        $pro_price = $svalue['pro_price'] * $quantity;
        $pro_image = $svalue['pro_image'];
        $query = "INSERT INTO orders(user_id, pro_id, invoice_no, pro_name, status, date) "
                . "VALUES('$user_id', '$pro_id', '$i', '$pro_name', 'processing', NOW())";
        $result = $this->db->insert($query);
        if($result){
            header("location: orders-list.php");
        } else {
            $msg = "
Something's went wrong. Please try again.
";
            return $msg;
        }
    }
    return true;
}

But it doesn't inserts same number in multiple ordered products rows of 1 order.
Please help me how can i generate invoice number from 0 to so on.

Okay, As per our discussion, what you want is
the same invoice number to, one order(one order can have multiple products but from one User).

So I think you need one table which can save the Invoice Number So when you are inserting into order table before that you fetch the Invoice Number and increment it, then you in order table

$INV_NUM = "SELECT INVOICE_NUMBER FROM INVOICE";
$INV_NUM++;

$squery = "SELECT * FROM cart WHERE user_id = '$user_id'";
$sresult = $this->db->select($squery);
if($sresult){
    $totalPrice = 0;
    $i = 0;
    while($svalue = $sresult->fetch_assoc()){
....................
....................
....................
 $query = "INSERT INTO orders(user_id, pro_id, invoice_no, pro_name, status, date) "
                . "VALUES('$user_id', '$pro_id', '$INV_NUM', '$pro_name', 'processing', NOW())";
....................
....................

This strategy might help you. Please let me know, it works or not?


For states (col) receiving exactly X number of items (row), sort the items by weight (col) I have been trying to figure this out for a few hours now but haven't gotten it. Each row in the table is a package.

I am able to get the States with the exact number of packages, but don't know how to then query only the packages with one of those states.

SELECT State FROM testing
 GROUP BY State
 HAVING COUNT(State) = 7;

Can anyone point me in the right direction?

So from the data below, it would return the 10 packages from GA and MI, sorted by weight.

-------------------
| State | Weight  |
-------------------
| AL    | 29.745  |
| AZ    | 54.4081 |
| CA    | 3.79151 |
| CA    | 94.0579 |
| CT    | 68.6374 |
| CT    | 80.6644 |
| CT    | 55.8415 |
| FL    | 5.49145 |
| FL    | 76.6451 |
| FL    | 7.02167 |
| GA    | 84.3802 |
| GA    | 5.51498 |
| GA    | 12.9253 |
| GA    | 88.4529 |
| GA    | 38.8692 |
| MN    | 82.009  |
| MN    | 33.893  |
| MO    | 10.9912 |
| MS    | 41.8613 |
| MT    | 21.3695 |
| MI    | 18.9998 |
| MI    | 71.6003 |
| MI    | 72.0666 |
| MI    | 67.8444 |
| MI    | 72.2397 |
| ND    | 5.66484 |
| ND    | 80.8316 |
| NJ    | 81.8038 |
| NJ    | 14.91   |
| NJ    | 55.238  |

I finally figured it out!

SELECT State, Weight FROM testing
WHERE State = ANY (
    SELECT State FROM testing
    GROUP BY State
    HAVING COUNT(*) = 7
)
ORDER BY Weight DESC;


Create database role to allow read access to all tables; write access to some We have a database with over 1000 tables. I need to create a role that allows read only on all tables, as well as write access to two specific tables.

I was messing around with database roles, but whenever I went to add the tables, I had to hand select all 1000... is there a better way to do this?

Granting permissions on the schema (e.g. dbo) will cascade to all the objects in that schema. For individual exceptions you can just list those explicitly:

GRANT SELECT ON SCHEMA::dbo TO [role];
GO

GRANT INSERT, UPDATE --, DELETE
  ON dbo.table_they_can_write_to TO [role];

DENY SELECT ON dbo.table_they_cannot_read TO [role];


Can bad table-scan queries slow down nice index-covered queries at the same instance? Let's say we have an instance used by two clients: me and my friend Ivan. Each of us has his own database. Each of us is only reading.

I'm a bad guy, so I'm relentlessly shooting table scans at the db. My tables are also huge, so they don't fit into memory.
Ivan is a nice guy, so all of his queries are covered by an index, so he has no reasons to hit the disk at all.

Will he notice any problems?

AFAIK, I'll have a bunch of threads sitting there in SUSPENDED state waiting for disk, and not generating too much of a load otherwise, so Ivan should do just fine.

I can definitely say "Yes, your bad query can impact Ivan's query" due to resource competition at instance level.

I have multiple databases in one production sql server box, I have seen many times that a bad query (actually a SP) in one database that caused both CPU and I/O usage skyrocketed and impact all other user sessions connected to other databases.

Besides CPU and I/O usage, one particular case is the negative impact a bad query brings to TempDB performance, and when TempDB is heavily utilized, it is a direct hit to all other queries that need to use TempDB for their executions.


Windows Service Management Node Won't Start I'm trying to set up a basic Cluster on my Win7 PC for testing. But I am stuck at the first hurdle. I have installed the Management Node as a Service:

C:\MySQLCluster\ManagementNode1\bin\ndb_mgmd.exe --install --config-file=C:\MySQLCluster\ManagementNode1\config.ini

But when I try and start the service it instantly stops. Surely as the management node it should run as a service?

What else do I need to do?

I am trying to start it from the Services Window, but I just get

  The MySQL Cluster Management Service on Local Computer Started and then stopped. Some Services Stop automatically if they are not in use by other services or programs

Alternatively from the command line:

NET START ndb_mgmd

  The MySQL Cluster Management Server service is starting.
  The MySQL Cluster Management Server service could not be started.
  The service did not report an error.

DISCLAIMER : Not a CMCDBA, Not a MySQL Cluster Expert
According to the Book

Page 175 describes ndb_mgmd as follows:

The ndb_mgmd binary represents the management server. This program reads in the cluster configuration file (config.ini) and then sends the configuration to any of the later starting nodes.It is also used for management (for example, restarting nodes, starting backups) and monitoring the health of the cluster. It also creates the cluster log, which is a central location for all cluster activities to be logged to.

Just from that brief description, you need to do the following:

Make sure there are no Storage Nodes and SQL Nodes running before starting ndb_mgmd
You need to locate the cluster log and find any vital error messages
Check to make sure config.ini has this:

config.ini
[NDB_MGMD]
id=(some number)
HostName=(DNS Name or IP Address)

Check to make sure config.ini can see every other node

Sorry, this is not really an answer. This is what I would do in your situation.
Here are the MySQL Cluster Experts in the DBA StackExchange:

Frazer Clement
Mat Keep
Andrew Morgan

Until someone can fully address this, I would defer to them to come up with an answer.


SQLite Studio - Update with Join I have read that update joins in SQLite are not supported, is there a way to convert a SQL update to SQLite?
Update Items
    Set Monitored = '1'

from Items
left join ItemsLoc on ItemsLoc.ItemId = Item.Id

where ItemsLoc.Loc is null

I am looking to update a column from Items table where the table/column ItemsLoc.Loc has a null value

Looks like I figured it out.
Update Items
Set Monitored ='1'
Where Items.Id in (Select Items.Id from Items Left Join ItemsLoc on ItemsLoc.ItemID = Items.Id where ItemsLoc.Loc is null)


Is it good practice to create a separate table for views count? I have created a blog table where I'm having a field called views_count but I have heard that updating the views_count field on each page view is strain. So I have created now a seperate table for views count as below:

views:
id,
blog_id,
ip_address,
counter

Now I'm storing unique visits in views table. And when I save record in view table I also update blog field views_count field, so is this a good approach? Or is there a better alternative?

Full Creates Schema:

CREATE TABLE `video_blog` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` int(11) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `sub_title` varchar(255) DEFAULT NULL,
  `slug` varchar(255) NOT NULL,
  `video_embed_code` text,
  `video_thumbnail` varchar(255) DEFAULT NULL,
  `video_thumbnail_alt` varchar(255) DEFAULT NULL,
  `description` text,
  `views` int(11) UNSIGNED NOT NULL,
  `is_active` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- Table structure for table `video_blog_category`

CREATE TABLE `video_blog_category` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` varchar(255) DEFAULT NULL,
  `order_by` int(11) UNSIGNED DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- Table structure for table `video_blog_views_tracker`

CREATE TABLE `video_blog_views_tracker` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `video_blog_id` int(11) UNSIGNED DEFAULT NULL,
  `user_ip_address` varchar(255) DEFAULT NULL,
  `counter` int(11) UNSIGNED DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Note: Our website blog is getting millions of visitors daily. So the new table will get updated frequently.

At a mere 10 updates per second, this is a non-issue.
When you get to 100/sec, and are still using HDD, then we can discuss further.  Or 1000/sec with SDD.
At higher rates, yes, violate the textbook principles and have the view-counter in a separate table (with only the count, plus page_id as the PK page_id).  The reason is to avoid conflicts with non-counter accesses to the main table.
If you are keeping track of each 'view' as in a table of "who viewed what, when", the problem gets messier.  On the one hand, there are INSERTs into that table (again, 10/sec is not a problem).  On the other hand, SELECT COUNT(*) ... will have extremes -- a count of 100 is no problem, but a count of a million can be.
"Likes" have similar issues.
For more extreme traffic, you need to gather up the updates/inserts, consolidate them, then apply them.  This might get you another 10x speedup, at the expense of some complexity and a few seconds delay in updating the counters.
But, by that time, you will have outgrown a single server, and other solutions will be needed for all your problems.  Sharding is a likely part of this next level of design.
For any system that starts small, and grows to be huge, you must expect to do a major redesign every so often.  For you (today), moving the counters out is premature.  However, doing so could forestall (for a while) the next major redesign.
Rehash
Plan A:  (all in one)
CREATE TABLE Blog (
    id INT UNSIGNED AUTO_INCREMENT,
    lots of meta info -- title, etc
    view_ct INT UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
);

Plan B:  (split out just the counter)
CREATE TABLE Blog (
    id INT UNSIGNED AUTO_INCREMENT,
    lots of meta info -- title, etc
    PRIMARY KEY (id)
);
CREATE TABLE BlogViews (
    blog_id INT UNSIGNED,   -- not A_I; for joining to Blog
    view_ct INT UNSIGNED NOT NULL DEFAULT '0',
    ts TIMESTAMP NOT NULL,   -- optional -- time of last viewing??
    PRIMARY KEY(blog_id)
);

Discussion of Plan A:

Simpler
Good enough for "low traffic" website -- say 10 views/sec.

Advantages of B:

Needs JOIN, but only when both meta and count are needed together.  This JOIN is NOT a big burden.
Updating the count hits only BlogViews, thereby not interfering with any queries that need only meta info, especially UPDATEs to such.
Needed for busy website -- say peak loading of 1000 views/sec.

When to use C:

Thousands of views/sec.
C involves collecting views, consolidating them, then updating a structure like Plan B.
This further isolates both Blogs and BlogViews from interference.
View counts may be slightly delayed (seconds).
(Further details can be discussed elsewhere)

Plans A2, B2, C2, D2:

These are modifications to the other plans, wherein you keep track of 'who' views the blogs 'when'.
This needs to worry about SELECT COUNT(*) instead of merely SELECT view_ct.
SELECT COUNT(*) can be costly if you have a million views.
These extensions are best handled with "Summary Table" design concepts, which I cover here.

Plan E (Now that the actual schemas are presented, I'll call that E):
For video_blog_views_tracker, get rid of id, and have
PRIMARY KEY(video_blog_id, user_ip_address)  -- should be unique

That should be optimal for the counter query:
SELECT SUM(counter) FROM video_blog_views_tracker
    WHERE video_blog_id = ?

Yes, rolling that into video_blog.views via a TRIGGER or CRON job is possible.  But I would not do it until the need is determined.


Why do different accounts connecting to the same database see different data? At my work, we have an Oracle database that I can connect to using different accounts.

The server name and port are identical between the accounts, but each account sees different data, and can see different table schemas. (I do mean different columns in different tables. i.e. if I add a column to a table in one account, I don't see that change reflected in the other account.)

Is there a name for this practice? I would like to read up on it, but I could not find anything in google.

Database version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

I see two possibilities, and they are not mutually exclusive.

First is that you are not really looking at the same table.  If you connect as SCOTT and select from EMP without qualifying the table name, you are selecting from SCOTT.EMP.  If you connect as FRED and do the same, you will be selecting from FRED.EMP.  Two different schemas with the same table name.  The two tables are totally unrelated and could very well have different structures in addition to certainly having different data.

The other is Virtual Private Database (VPD).  Two users actually selecting from the same table, but only seeing the rows they are allowed to see, based on the VPD policies.

With SQLplus or SQL Developer, connect with one account and SELECT TABLE_NAME FROM USER_TABLES;  Do the same with the other account.  Be sure to select from USER_TABLES, not ALL_TABLES or DBA_tables.  That will show you just the tables owned by the connected user.


automatic transactional replication failover to noce2 with AG failover I am working on to set up high availability group like
SQL01,SQL02,SQL03(DR) and SQLRPT01 as the reporting server which has our reporting DB.
* From SQL01 to RPT01, I am running transnational replication.
*As of now replication works good,if SQL01 goes of AG immediately goes to SQL02 and comes in good health asap.

Question is once I have fail over from sql01 to sql02, do we have the procedure to fail-over the replication agents to SQL02 and replication resume automatically.(without Using fail-over cluster instance, because we don't have any shared storage planning for this.)
I am not sure how this sp_redirect_publisher etc. is going to work.
Do we have a step by step procedure to set up this configuration??

During AG failover from one server(POCSQL01) to second server(POCSQL02) , how replication agents gets re-directed.

With the introduction of AG, there were new SP's added to support replication and  replication agents like Snapshot, Log Reader and Merge supports below new SP's :

sp_redirect_publisher
sp_get_redirected_publisher
sp_validate_redirected_publisher
sp_validate_replica_hosts_as_publishers

The interesting one is sp_redirect_publisher

Specifies a redirected publisher for an existing publisher/database pair. If the publisher database belongs to an AlwaysOn Availability Group, the redirected publisher is the availability group listener name associated with the availability group.

If you go into details of how that will work - the log reader agent's default behavior is that it will not process any log records until those records have been hardened across all AG replicas.
sp_redirect_publisher will tell replication that if it is not able to connect to the original publisher, connect to the AG Listener allowing the subscribers to reconnect to the publisher after an AG failover.
Run at the distributor :
Use Distribution
Go

Exec sys.sp_redirect_publisher
       @Original_publisher = 'Originap_Pub_Name',
       @Publisher_db = 'Pub_DB_In_AG',
       @Redirected_Publisher = 'ListenerName';

-- Confirm that the new meta data table has been created

Select *
From MSRedirected_Publishers;

Go

Configure Replication for AlwaysOn Availability Groups has all detailed steps on how you will configure replication and alwaysON.
As always test your layout with all possible scenarios !!


How to build a query that gets its values from a file I'm trying to build a query that uses the values in a file

select Epm,Emp_num,Emp_Addr From Dir_Rimco_Table where Emp in (Dir_Emp_Rsed.txt)

Constraints

-I can't create a new table

-I can't modify Dir_Rimco_Table

-Current Privileges

Any idea how I can do that?

If the restriction on creating tables includes "external tables", then you need to get management to lift that restriction.  As stated, you can't expect oracle (or any rdbms product, for that matter) to just magically be able to recognize some external file as a data source and be able to process it with SQL statements.  External tables is how oracle does that.  If you read up on external tables in the oracle docs, you will see that it is not a table that contains data within the database, but simply a description of an external file, and that allows oracle to treat that file as if it were an actual table.


Will a WHERE query do checks on simpler comparisons (i.e. bit) before running more arduous comparisons (i.e. varchar)? If I write a query that includes a compound WHERE clause, e.g.:

SELECT *
FROM MyTable
WHERE BitField = 1
    AND VarcharField = 'asdf'

and the inclusion of that bit comparison simply excludes the same fields that the varchar comparison will exclude, will the presence of that bit field comparison render me a performance improvement?

It is important to recognize that SQL is a declarative language. The SELECT query you wrote specifies the logical results that should be returned. It is up to the database engine, specifically the query optimizer, to determine an efficient physical strategy to return those results.

The final physical execution plan will depend on the optimizer's reasoning abilities, the amount of time it is prepared to spend on the problem, the availability of suitable access methods (primarily indexes and materialized views), representative statistics information, and the particular code path your query specification takes through the optimization code.

In general, if your database design is relational, you provide good access methods & accurate statistical information, and the query is well-written, the optimizer will normally find a reasonable physical execution strategy without you needing to worry too much about the written form of the query specification too much.

There will always be cases where expressing the same logic requirement using different (but semantically identical) syntax will affect the physical execution plan , but that should be a secondary concern. Again, generally, only consider expressing the query differently if runtime characteristics are unacceptable, once all the basics mentioned above have been covered.

It would be rare in the extreme for the written order of simple conjunctive WHERE clause predicates (as in the question) to affect the physical execution plan in any measurable way. In short, this is not something you should spend time worrying about. Get the database design, indexes, and statistical information right first.

To answer the question directly (eventually!), adding the extra redundant condition might improve performance, but only if it enables use of a more efficient access method - for example if there is only an index on (BitField, VarcharField). If there were already an index on (VarcharField), it would add only overhead.

As an implementation detail, no, SQL Server does not consider the cost of comparisons depending on data type or apparent computational complexity. In fact, there is precious little costing of scalar operations at all, but that leads on to a whole separate topic.

Related questions:

Logical operators OR AND in condition and order of conditions in WHERE
SQL Server 2008 and constant expressions
Bitwise operators affecting performance
Strange SQL Statement Behavior


oracle huge hash_area_size I am wondering what is the maximum setting for hash_area_size parameter?
I'm trying to perform a huge join which ends up allocating 10Gb of temp space and completes in an hour.  10Gb is not that much by today's standards.  I have the RAM available in the system.  Should be possible to process that in memory.  Is there a way to tell oracle to do that?  When I try to set hash_area_size to 10Gb it complains because it seems to be limited to 32bit integers.

I've also tried
alter session set "_smm_max_size" = 10000000; -- 8 zeros

because I read that it is in Kb, but it doesn't seem to make a difference.

UPDATE:
11g Enterprise Edition Release 11.2.0.3.0 - 64bit
The query is a join of 3 wide tables of equal size by 4 columns.  Each column has about 10M rows. No indexes.  The plan is 2 HASH JOINs.  Hash joins are performed in TEMP space.

If you have the Partitioning Option then consider hash partitioning the two tables into 32, 64, or 128 etc partitions on the join key. Whether you use parallel or serial query, your memory consumption will be very much reduced.


LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process? MySQL version: 5.5.13

The query that I'm using:

SET AUTOCOMMIT=0;
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date FIELDS TERMINATED BY  ',';
COMMIT;

Normally it takes 3s to complete. But sometime, the insert query takes about 3-7 minutes to run:

     Id: 97
   User: tom
   Host: 192.168.6.31:27059
     db: test
Command: Query
   Time: 155
  State: logging slow query
   Info: COMMIT

and my database is locked completely (very slow when connecting). The process Id 97 continues running after I try to kill it. The init script shows the [ FAILED ] flag when trying to restart MySQL, so I must use kill -9 and start again.

I will try to disable the unique_checks follow this guide. But I'm trying to find out why it's locked.

There are some Waiting for table metadata lock states on temporary tables:

     Id: 180098
   User: jerry
   Host: 192.168.6.31:54909
     db: test
Command: Query
   Time: 142
  State: Waiting for table metadata lock
   Info: DROP TABLE IF EXISTS norep_locationtmp

and a Waiting for table level lock state:

     Id: 180233
   User: bob
   Host: 192.168.6.31:43537
     db: test
Command: Query
   Time: 65
  State: Waiting for table level lock
   Info: SELECT COUNT(DISTINCT A.`campaignid`)  INTO _c
FROM `ox_campaigns` A
INNER JOIN `selfserving_users` B ON B.`user_id` = A.`uid`
INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
WHERE A.`revenue_type` = 5 AND A.`deleted` = 0 AND A.`expire` = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY)
AND A.`isExpired` = 0 AND IF( NAME_CONST('_permitid',3) = -1, 1=1, IF( NAME_CONST('_permitid',3) = 0, A.`uid` IN (SELECT C.`user_id` FROM `selfserving_users` C WHERE C.`groupid` =  NAME_CONST('_groupid',12) ) ,A.`uid` =  NAME_CONST('userid',388)))

There isn't anything related to the above table - v3_zone_date and no deadlock detected in SHOW ENGINE INNODB STATUS. Where should I take a look at first to troubleshoot this case? Let me know if you need any further information.

Reply to @RolandoMySQLDBA:

  The scary part about the query is the self-reference

  You have selfserving_users acting in a self serving manner against
  itself.

I will ask the developer to rewrite it.

  You are hammering the InnoDB Buffer
  Some memory swapping may be going on

I have 40GB RAM and:

innodb_buffer_pool_size     = 20G
innodb_buffer_pool_instances    = 8

The memory graph exhibit that there is no increase.

  Possible full table locking issues that are affecting data pages
  outside the v3_zone_date table (such was with the selfserving_users
  table)

Do you have any ideas to avoid the full table locking issue?

  There may be a way to throttle the LOAD DATA INFILE process on an
  InnoDB table. I cannot give you a solid answer on this one, but try
  this link from Baron Schwartz.

I will trying the fifo method and let you know the result.

UPDATE: Wed Feb 22 12:19:03 ICT 2012

Below is snippet of the SHOW ENGINE INNODB STATUS output when hanging:

---TRANSACTION 1EF3CC26, ACTIVE (PREPARED) 332 sec
68 lock struct(s), heap size 14776, 6933 row lock(s), undo log entries 3465
MySQL thread id 4088, query id 11411947 192.168.6.31 bob
COMMIT
Trx read view will not see trx with id >= 1EF3CC27, sees < 1EF06C8E

Based on the thread id, I found out the culprit is a .NET process which is loading data into the database by following steps:

LOCK TABLES;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
LOAD DATA;
COMMIT;
UNLOCK TABLES;
SET unique_checks=1;
SET foreign_key_checks=1;

It seems that the insert processing hangs at COMMIT step, the table cannot be unlock and freezing my database. Should I swap the COMMIT and UNLOCK TABLES step?

According to the MySQL document:

  Beginning with MySQL 5.5.5, it is no longer possible to set
  @@session.sql_log_bin within a transaction or subquery. (Bug #53437)

As my understanding, 400K rows is not big enough, cannot find out why it locked my db?

Any ideas?

The query under process ID 180233 looks like it is in distress.
Here is the query itself
SELECT COUNT(DISTINCT A.`campaignid`)  INTO _c
FROM `ox_campaigns` A
INNER JOIN `selfserving_users` B ON B.`user_id` = A.`uid`
INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
WHERE A.`revenue_type` = 5 AND A.`deleted` = 0
AND A.`expire` = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY)
AND A.`isExpired` = 0
AND IF( NAME_CONST('_permitid',3) = -1, 1=1,
IF( NAME_CONST('_permitid',3) = 0, A.`uid` IN
(SELECT C.`user_id` FROM `selfserving_users` C
WHERE C.`groupid` =  NAME_CONST('_groupid',12) ) ,
A.`uid` =  NAME_CONST('userid',388)));

The scary part about the query is the self-reference
You have selfserving_users acting in a self serving manner against itself.
Sometimes, the MySQL Query Optimizer will play a bait-and-switch, smoke-and-mirrors games with data, especially with a self reference, in order to formulate the best EXPLAIN plan possible. While mysql is very capable of completing sub-SELECTs, it can be still be expensive.
However, this is just a symptom that manifested because of Process ID 97. What is really the issue here?
LOAD DATA INFILE against an InnoDB table could make mysqld a little punch drunk. I don't believe (or at least I don't exercise full confidence) you can encapsulate it as a normal transaction although this was addressed back in MySQL 5.0.
Just picture it:

You are hammering the InnoDB Buffer
Some memory swapping may be going on
Possible full table locking issues that are affecting data pages outside the v3_zone_date table (such was with the selfserving_users table)

There may be a way to throttle the LOAD DATA INFILE process on an InnoDB table. I cannot give you a solid answer on this one, but try this link from Baron Schwartz.
UPDATE 2012-02-22 12:00 EST
There is open bug report in MySQL 5.5.7 called Deadlock when DDL under LOCK TABLES WRITE, READ + PREPARE. At the bottom of the report, a person complained about a block problem cause by the explicit LOCK TABLES.
Launching a COMMIT on locked rows in a table would hang because of trying to unraveling MVCC data assocaited with the locked rows. Based on the InnoDB Status you have shown, there would exist 6933 row locks on the table you are importing. I know that in Oracle, when introducing new rows to a table, MVCC is still generated because the previous version of the newly inserted row is a nonexistent row. The same must be occurring for InnoDB.
UPDATE 2012-02-22 12:42 EDT
In your question you stated the following about your .NET process

LOCK TABLES;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
LOAD DATA;
COMMIT;
UNLOCK TABLES;
SET autocommit=1;
SET unique_checks=1;

All of these events are running within the same DB Session. This is also happening within one DB Connection. Thus, this is not a deadlock in the traditional sense. It is just a case of blocking your COMMIT within a given DB Connection/Session because the tables were locked within the same DB Connection/Session.
UPDATE 2012-02-23 19:00 EDT
I would change the sequence to be this:

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
LOCK TABLES;
LOAD DATA;
UNLOCK TABLES;
COMMIT;
SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;

Please remember, a COMMIT cannot proceed if you have the tables locked in serial fashion. Therefore, UNLOCK TABLES must precede COMMIT.


delete old 'Monitoring Agent' metrics and 'Automation Agent' Debug logs via scheduler We use Ops Manager 1.6.2.251

# mongo
MongoDB shell version: 2.6.10
connecting to: test
> show dbs;
admin                   (empty)
alerts                  0.203GB
apiv3                   0.578GB
backupagent             0.078GB
backupalerts            0.078GB
backupbilling           0.078GB
backupconfig            0.078GB
backupjobs              0.078GB
backuplogs              2.077GB
backupoplogs            0.203GB
backupsnapshot          0.078GB
cloudconf               0.203GB
local                   0.078GB
mmsdb                   0.078GB
mmsdbagenterrors        0.328GB
mmsdbagentlog           1.078GB
mmsdbanalytics          0.078GB
mmsdbautomation         0.578GB
mmsdbautomationlog      1.578GB
mmsdbbilling            0.078GB
mmsdbconfig             1.078GB
mmsdbdays               0.078GB
mmsdbhours              0.203GB
mmsdbjobs               0.078GB
mmsdbminutes-even       0.203GB
mmsdbminutes-odd        0.203GB
mmsdbpings              0.578GB
mmsdbprofilerstats      0.078GB
mmsdbprovisioning       0.078GB
mmsdbprovisionlog       0.203GB
mmsdbqueues             0.078GB
mmsdbratelimit          0.078GB
mmsdbrrdcache           0.078GB
mmsdbrrdsampling        0.078GB
mmsdbserverlog          0.203GB
mongo-distributed-lock  0.078GB

Our MMS DB use 15 GB of 150 GB available. We can't extend the volume.
We don't use MMS for backup. We use mongodump on other node.

  Ops Manager Application Database This database contains application
  metadata used by the Ops Manager Application. The database stores: •
  Monitoring data collected from Monitoring Agents. • Metadata for Ops
  Manager users, groups, hosts, monitoring data, and backup state. For
  topology and specifications, see Ops Manager Application Database
  Hardware.

At the moment we only enabled Monitoring Agent on Prod. But we wish to enable it on all environments, but cleanup metrics data older than 30 days.

In Oracle you can do something like this. This statement can be scheduled jobs with Oracle Scheduler.

 DELETE
  FROM YOUR_TABLE
 WHERE creation_date <= TRUNC(SYSDATE) - 30

The same we wish to to with Automation agent DEBUG logs. Howto do this with MongoDB MMS meta databases?

From FAQ

  What is the data retention policy for Ops Manager? Ops Manager retains
  two distinct types of data: metrics, which describe usage; and
  snapshots, which back up your data.

  Data-retention policies, as defined in the Terms of Service, are
  always subject to change.

  As of this writing, Ops Manager preserves:

  Minute-level metrics for 48 hours. Hourly metrics for 94 days.
  Snapshots according to their retention policy.

>use mmsdbagentlog
switched to db mmsdbagentlog
> show collections
app.agentLogs
> db.app.agentLogs.stats()
{
    "ns" : "mmsdbagentlog.app.agentLogs",
    "count" : 1711298,
   "size" : 524287948,
    "avgObjSize" : 306,
    "storageSize" : 71323648,
    "capped" : true,
    "max" : -1,
    "maxSize" : 524288000,
    ... }

Looking in OpsManager Database , they use capped size collection to store the agent log , it seems we don't need to worry about the volume.
If the collection confuse you is not capped size , you could try to schedule a job with crontab in Linux(just an example) to remove those records :

db.collection.remove({_id : {$lt : ObjectId('55b0d1c40000000000000000')}})

first 8 character is hexadecimal timestamp can be generate in Linux Shell with command :

Today=`date +%F`
T=`date --date="$Today 00:00:00" +%s`
Stamp=`printf "%X" $T`''0000000000000000
echo $Stamp


How Mysql Query Plan is generated? Mysql before executing a query with multiple joins. It creates multiple query plan and find the lowest cost plan to execute the query(approx).

My questions are

How the query plan is generated on what basis(like no of fields in the where condition for a particular table)
Is there any formula(or steps) to generate query plan by ourself

The answer is evolving.  More "cost" analysis went into 5.7, still more into 8.0.  There is more to go, such as "histograms" instead of a single number for cardinality.

In the case of joins, MySQL has always pursued all permutations of ordering for the tables.  However, it has an aggressive pruning strategy that keeps it from actually doing "all".  (Key-value (EAV) stores defeat this, but there is a tunable to help.)

The main statistic used is an estimate of the cardinality for each index.  This is found by 'randomly' probing the BTree for the index.  These probes and the retention of the data was significantly improved in 5.6.

Generally, when you are joining multiple tables, the WHERE clause has a lot of sway in saying which table to start with.  If there is no WHERE, then the "smallest" table is picked as the first.  (This is sometimes not the best.)

There are a few tricks to guide the Optimizer.  These are generally a bad idea.  What helps today may hurt tomorrow.

STRAIGHT_JOIN -- do the joins in the order given.
USE/IGNORE INDEX -- give preference to/against specified index(es).
FORCE INDEX -- If at all possible, use this index.

And 8.0 has more.

And there are tricks.  A 'derived' table is allow to ignore ORDER BY.  But you can trick it into doing the sort by adding a LIMIT with a huge value.


Improve pgBouncer configuration What is the best configuration for pgBouncer in my case?

I have only 1 database, 1 user and the max_connections in postgresql.conf is 1024.

The currently config of my pgbouncer.config is:

max_client_conn = 1024
default_pool_size = 1024
min_pool_size = 32
;max_db_connections = 50
;max_user_connections = 50

It would be great to know how many connections you spect to handle at the same time.

I would size this depending on your server hardware use expectations

If you expect 1024 concurrent connections as much this setting is correct.

max_client_conn = 1024

But here are the changes that worked for me. If you change the pooling mode you can reduce the default_pool_size, this increased my performance

pool_mode = transaction
default_pool_size = 50

Take a look at the different pooling modes to see if you can benefit from a more efficient pooling mode than the default value session pool mode.

  Session pooling
  Most polite method. When client connects, a server connection will be assigned to it for the whole duration it stays connected. When client disconnects, the server connection will be put back into pool. This mode supports all PostgeSQL features.

  Transaction pooling Server connection is assigned to client only
  during a transaction. When PgBouncer notices that transaction is over,
  the server will be put back into pool.
  This mode breaks few session-based features of PostgreSQL. You can use
  it only when application cooperates by not using features that break.
  See the table below for incompatible features.

  Statement pooling Most
  aggressive method. This is transaction pooling with a twist -
  multi-statement transactions are disallowed. This is meant to enforce
  "autocommit" mode on client, mostly targeted for PL/Proxy.


Know when a database has been restored I'm using SQL Server 2008. I would like to know if a specific database has been restored from a device and when did it happened. Is it possible?

In the msdb database, there is a table named restorehistory:

declare @DB sysname = 'MyDB';
select * from msdb.dbo.restorehistory where destination_database_name = @DB;

This is a table that people with sufficient privileges can clear out, but if the restore was recent and you don't have a job which clears out this table (or if it hasn't run between then and now), you should be able to see the login which performed the restore.

EDIT

You can also join it to a couple of other tables (if they have relevant data):  backupset and backupmediafamily.  If there are records there--with the same retention caveats as before--they'll tell you more about the backup file(s) used to restore:

declare @DB sysname = 'MyDB';
select
    rh.destination_database_name,
    rh.user_name,
    bs.name as backup_set_name,
    bs.user_name as backup_set_username,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.database_name as backup_set_database_name,
    bs.server_name,
    bs.machine_name,
    bmf.physical_device_name,
    bmf.device_type,
    case bmf.device_type
        when 2 then 'Disk'
        when 5 then 'Tape'
        when 7 then 'Virtual Device'
        when 105 then 'Permanent backup device'
        else 'UNKNOWN'
    end as device_type_desc
from
    msdb.dbo.restorehistory rh
    left outer join msdb.dbo.backupset bs on rh.backup_set_id = bs.backup_set_id
    left outer join msdb.dbo.backupmediafamily bmf on bs.media_set_id = bmf.media_set_id
where
    rh.destination_database_name = @DB;

That way, you could also filter on device type or file location.


pg_stat_statements causing DB to run out of memory I enabled pg_stat_statements on an AWS RDS postgres 9.6.3 instance with 30GB RAM.

We got the error message below in postgres log, and an event saying OS killed DB server process due to excessive memory consumption.

UTC::@:[32358]:LOG:  server process (PID 9689) was terminated by signal 9: Killed"

We opened a case with AWS and they said server is too small for the load and to increase server size, we increase to a larger server with 64GB but after few hours (took slightly longer than last time) here also the DB went into recovery as OS killed DB process.

Disabling pg_stat_statements fixes the issue, we saw the same issue in other RDS instances also.

It seems there is some memory leak with pg_stat_statements as based on our setting below this extension should not use more than 100MB of RAM.

1.  pg_stat_statements.max=100000
2.  pg_stat_statements.save=1
3.  pg_stat_statements.track_utility=1
4.  Create extension from psql command line utility.
5.  shared_preload_libraries=pg_stat_statements

Any ideas how to fix this, as we want to use pg_stat_statements to look at DB performance.

Also is there an Oracle AWR equivalent on postgres.

It seems very surprising that you see an out of memory issue due to pg_stat_statements with the default kept statement count (5000). It makes somewhat more sense with a very high count, especially if you tried to query the view frequently.

One scenario, without going through the pgss code in detail, that I could imagine causing a memory issue, is the query text handling itself (not the statistics tracking), especially in the light of a lot of unique entries.

Given this is RDS we unfortunately can't check the size of the pgss file, but if you are open to trying this again (maybe you can reproduce a similar workload on a staging environment?), I think it would be useful to run a simple "SELECT * FROM pg_stat_statements" and visually look at the entries.

In particular I'd look for very large entries in the "query" field, as well as a lot of seemingly identical queries in pg_stat_statements (some ORMs tend to cause problems like this).

Also I'd recommend posting about this to the Postgres mailing lists - possibly to pgsql-bugs and/or pgsql-performance.

(For context: I built and operate pganalyze where we process a lot of pg_stat_statements data - memory issues like this are very rare)


Can hardcoding parallelism in Oracle hints lead to sub-optimal performance We have a large Oracle 11.2.0.4 database (data warehouse). Our developers hard code parallelism into their queries e.g. (PARALLEL(16)). Multiple queries can be running at once, all with large hard coded PARALLEL hints. Is this limiting Oracle's ability to optimize degree of parallelism based on available resources?

(Trying to figure out if we should stop this practice and let Oracle optimize/maximize for us).

Yes, yes it can. If the number of running parallel threads exceeds the number of CPU cores on the host machine, each of these threads will compete for CPU resources. This can be extremely inefficient and harm performance.


How to alter column to make it primary key when one already exists mysql8? id         | bigint unsigned | NO   | PRI | NULL    | auto_increment |
 | title      | varchar(255)    | NO   |     | NULL    |                |
 | category   | varchar(255)    | NO   |     | NULL    |                |
 | summary    | varchar(10000)  | NO   |     | NULL    |                |
 | detail     | mediumtext      | NO   |     | NULL    |                |
 | created_at | timestamp       | YES  |     | NULL    |                |
 | updated_at | timestamp       | YES  |     | NULL    |                |
 | image      | mediumblob      | YES  |     | NULL    |                |
 | image1     | mediumblob      | YES  |     | NULL    |                |
 | image3     | mediumblob      | YES  |     | NULL    |                |
 | phold      | varchar(50)     | NO   |     | NULL    |                |
 +------------+-----------------+------+-----+---------+----------------+

I have this database now,I want to make a one-one relationship with with another table with where I use column ( title ) to link specific data. When I run
mysql> alter table tours add primary key (id,title);

OR
mysql> alter table tours add constraint pk_tours primary key (id, title);

The error:
ERROR 1068 (42000): Multiple primary key defined

on both queries.
I want another table named itinery where I use to link it with former table with help of volumn title ;
I am using mysql 8 with laravel 8.
Thanks

If you would like to have other tables reference just the title field, then you can add a UNIQUE index.
First, edit the tours table to add a NOT NULL constraint to the title field:
ALTER TABLE `tours` MODIFY `title` VARCHAR(255) NOT NULL;

Now you can create the index:
CREATE UNIQUE INDEX `udx_title` ON `tours` (`title`);

Be sure to confirm that all values in title are unique before doing this. Once done, you will be able to have other tables reference the field.

Hat tip to Vérace for pointing out that title could be NULL, which we do not want.


Why do I have to set MySQL user password with mysql_native_password to connect with Workbench? I've noticed if I set the mysql user's password like below, I cannot connect with MySQL Workbench.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'myPassword'
If I use this below I can connect to it:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'myPassword';
I remember at MySQL 8 there was a default password policy change.
But still after years of that change, do the drivers (c/c++, node.js etc) not support the new password authentication?
Does MySQL's own Workbench don't support the new way of authentication?
Or am I missing something?

MySQL Workbench 8 accepts, if mysql server is set to legacy mode, also native_passwords.
But it  also accepts users that use cashing_sha2_password or sh2 passwords.
So you must check the users roles and for which. first try to use % in LImit to host matching and DBA as role, so that your user has all privileges to test.


Adding new columns to Table with Clustured Columnstore Index I have a daily facts table 'FactsTable1' with 240 columns on which I am exploring 'Clustured Columnstore Index'(CCI).

I want to know,

What happens when I add another ~50 columns to the table?
Will they automatically added to CCI? Or I have to rebuild CCI once
again?
What should be the best way to handle such conditions?
If I have to rebuild index to make new columns to be part of CCI
then what will happen if I dont rebuild? How will it affect my query
performance?

Additional information :

FactsTable1 contains 400000+ rows & 240 columns
Its partitioned on date column
It is heavily queried for analytic data.
@@version : Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)

What happens when I add another 50 columns to the table?

These columns will be added to the table with NULL values if you have not specified a default value. If you have added a default value, the compressed rowgroups will stay as they are and no updates will be done.

Will they automatically be added to the CCI?

Yes

What should be the best way to handle such conditions?

If you are going to update all the values of the new columns, you will get a lot of compressed row groups with more deleted rows and open delta stores (heaps), when doing a massive update the tuple mover will kick in and turn these delta stores into compressed segments if they are marked as closed (size 1 000 000 rows). To get rid of the compressed segments with only a few rows left you will have to do a REORGANIZE statement

Additional Information

--> You need 1 000 000 rows at least to get CCI to work well, otherwise you will be stuck in your heap delta store. (you only have 400 000)

This will require you to do a rebuild or reorganize of the index to get all rows into a compressed segment

ALTER INDEX idx_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

You should also upgrade you SQL Server 2016 to SP1

Try this

Because you do not have so many rows in your Fact table, it might be better to use the NCCI trick. You can create an empty filtered nonclustered columnstore index on your table. This will have a zero cost on your table, but will trigger the batch mode executions plans which will give you a lot of gains for analytical queries.

CREATE NCCI_TEST on TESTABLE (ALL COLUMNS)
WHERE ID = -1 and ID = -2

Examples

CREATE TABLE dbo.test
(
Bla datetime,
blabla varchar(200)
)
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCI_TEST on DBO.TEST
GO

And then insert some rows

INSERT INTO dbo.test
select DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0),cast(ABS(CHECKSUM(NEWID())) as varchar)
GO 10000

insert into dbo.test
select * from dbo.test
GO 5

If you then go and check your columnstore elements

select * from sys.column_store_row_groups

You see that you now have a delta store, which means this is a heap table without indexes.

If you then rebuild your index with the compress all rowgroups

ALTER INDEX CCI_TEST on DBO.TEST REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)

The tombstone elements are the previous deltastore/compressed segments which still linger they will be removed after about 5 minutes

You now have your columnstore compression (which will avoid locking issues)

If we now add a new column

alter table dbo.test add nondefaultvalue varchar(20)

And we check the row groups again

you still have the good layout of your row groups.

If we add another column with a default value and check the rowgroups

alter table dbo.test add defaultvalue varchar(20) not null default('DEFAULT')

If you then however decide to update those values.

UPDATE dbo.test
set nondefaultvalue = 'DEFAULT'

Things change

We no longer have a compressed segment we can use. If we then try to reorganize this index

ALTER INDEX CCI_TEST on DBO.TEST REORGANIZE

We get our open delta store again, which will cause locking.
We can however get it back to a compressed segment by issuing the following command we saw previously

ALTER INDEX CCI_TEST on DBO.TEST REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)

Now we have to good columnstore segment again.

So in your case you will have to do a reorganize with compress all row groups, to get it working fine. This should not take long on a table with only 400 000 rows but it is necessary to avoid locking.

If you do bulk inserts the story changes again, but you will have to do 100 000 rows at once to get everything straight into a compressed segment.

Dictionaries

When adding another column a new dictionary will be made for your columnstore index (sort of mapping to the column elements inside your columnstore segments).

select * from sys.column_store_dictionaries

alter table dbo.test add defaultvalue2 varchar(20) not null default('DEFAULT')

select * from sys.column_store_dictionaries

For more information I suggest you go to http://www.nikoport.com/ he has all the extra information you need on columnstore on his blog.


Import MySQL table from a textfile to a remote host with compression I can import data.sql onto my foreign host with this command:

mysql -u username -p -h foreign-host.example.org -A DATA-BASE-NAME < data.sql

Which works fine for small export files, but I cannot find an explanation how to import a large file, so the transfer will be compressed.

Use the --compress flag.

mysql --compress -u username ...

  --compress, -C

  Compress all information sent between the client and the server if both support compression.

  http://dev.mysql.com/doc/refman/5.6/en/mysql-command-options.html#option_mysql_compress

Virtually any MySQL client and server will support compression.


Call dynamically a function using plpgsql I create a function that returns the value of a field when the id is given :

CREATE OR REPLACE FUNCTION get_dcteam(id int) RETURNS text
AS $$
#print_strict_params on
DECLARE
dc_team text;
BEGIN
    SELECT monitoring_table.dc_team INTO STRICT dc_team
        FROM monitoring_table WHERE monitoring_table.id = get_dcteam.id;
    RETURN dc_team;
END
$$ LANGUAGE plpgsql;

I call it this way :

select * from get_dcteam((select id from monitoring_table where id=30))

What I want to do is call the function  dynamically. I don't want to precise a known id(given value in parameter) but any existing value in my table. The result of this query I will use it in a trigger.
I'm a newbie and I'm struggling to learn dynamic SQL.
Your help would be precious.
Best regards.

Don't ever call it like that. Because it returns a single value (RETURNS text) Call it like this instead, it's cleaner.

SELECT id, get_dcteam(id)
FROM monitoring_table
WHERE id = 30;

Now if you want it for more id's remove the WHERE Clause.

SELECT id, get_dcteam(id)
FROM monitoring_table;

Also you should probably rewrite that to use regular SQL, as it may be inlined.

CREATE OR REPLACE FUNCTION get_dcteam(id int)
RETURNS text
AS $$
    SELECT monitoring_table.dc_team
    FROM monitoring_table
    WHERE monitoring_table.id = get_dcteam.id;
$$ LANGUAGE sql
IMMUTABLE;

But one step further, I'd just delete the function entirely. It's basic SQL and it's just going to slow you down. Look at what you're doing: get_dcteam is querying monitoring_table, and you're calling it on a query that is already visiting that table! This will be much faster and clearer:

SELECT id, dc_team
FROM monitoring_table
WHERE id = 30;

Having previously answered this you said

  thanks for your answer. This call will give me several rows. What I Want is use this function in a where clause of an other one, but without passing the id in parameters. – zakaria mouqcit 3 hours ago

I'm not even sure what that means. Perhaps you could explain.


What would be best data model for finding difference within n, n-1 and n-2 elements? This is from betting domain which has something that is called a long list: a list of a "home team win/draw/away team win"  markets for 13 games.
A punter can select any combination of the possible outcomes which are encoded in a following way:

1 - home team wins
2 - draw
4 - away team wins
3 - home team wins or draw
5 - home team wins or away team wins
6 - draw or away team wins
7 - home team wins or draw or away team wins

Meaning a [3, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7] represents a selection where punter put following bets:

"home team wins or draw" in a first game
"home team wins"  in games 2-12
"home team wins or draw or away team wins"  in 13th game

After games are finished there will be another 13 elements list representing winning outcomes, for example: [4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1] means that in a first game away team won and in all other games home team won.

Let's take this bet [3, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7] and this query as an example [4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1].

Next we have a following correspondence between selected outcomes and actual results

1 - 1, 3, 5, 7
2 - 2, 3, 6, 7
4 - 4, 5, 6, 7

Meaning for an actual result of 13 games [4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]  following bets considered to be a winning bets:

[4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
[4, 3, 1, 5, 1, 7, 1, 1, 1, 1, 1, 1, 1]
[5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
[6, 1, 7, 1, 1, 5, 1, 1, 3, 3, 3, 3, 1]
[7, 1, 5, 5, 5, 1, 1, 1, 1, 7, 7, 7, 1]

The question is what is the best way to model this in database for following use cases:

find bets where all 13 game results were guessed correctly
find bets where 12 game results were guessed correctly
find bets where 11 game results were guessed correctly

Examples of queries for following use-cases will be highly appreciated ;))

Possible realization:

id SERIAL PRIMARY KEY,
punter_id INT,
bet_number INT,
guess_number TINYINT, -- from 1 to 13
guess TINYINT, -- from 0 (or 1) to 7
UNIQUE (punter_id, bet_number, guess_number)

Each bet consists from 13 records for each punter.

The result is placed into the table with similar structure:

id SERIAL PRIMARY KEY,
bet_number INT,
result_number TINYINT, -- from 1 to 13
result TINYINT, -- from 0 (or 1) to 7
UNIQUE (bet_number, result_number)

So you join these tables by (bet_number, guess_number)=(bet_number, result_number), group by punter_id (or by (punter, bet_number, result_number) when you'll obtain the result for a lot of bets in one query), and calculate SUM( CASE guess & result WHEN 0 THEN 0 ELSE 1 END ). This sum is the amount of positive guesses per bet.


Is there any way to set stored procedure options like ANSI_NULLS to ON or OFF outside of creation? I'd like to set ANSI NULLs and quoted identifier properties on a stored procedure after it has been created.  Is this possible?

For some context, I'm using Visual Studio Database Projects / SSDT, and it appears these values can only be set at the database level, not on the procedure level.  However, we're using an existing database with inconsistently created procedures, so I'd like to preserve the current behavior until we can normalize them.

Haven't tested this on other versions of Visual Studio, but with version 12.0.3 (Update4) there is an option to override the ANSI Nulls config at the database-level specification for a single procedure in an SSDT project.

Just right-click the procedure in solution explorer and choose properties...


Nested set model: retrieve sub-tree containing nodes that match condition I'm using the "nested set model" (the backend used by our web application is CakePHP and the Project model uses the TreeBehavior).

This is my "project" table:

+----+-------+-----------+-----+------+
| id | name  | parent_id | lft | rght |
+----+-------+-----------+-----+------+
|  1 | P1    | NULL      |   1 |    6 |
|  2 | P2    | NULL      |   7 |   12 |
|  3 | P2.1  | 2         |   8 |    9 |
|  4 | P2.2  | 2         |  10 |   11 |
|  5 | P1.1  | 1         |   2 |    3 |
|  6 | P1.2x | 1         |   4 |    5 |
|  7 | P3x   | NULL      |  13 |   18 |
|  8 | P3.1  | 7         |  14 |   15 |
|  9 | P3.2  | 7         |  16 |   17 |
+----+-------+-----------+-----+------+

I need to write a query that will return all projects which name match a certain string and also their parent or children.

So if the string used is "x" ( project.name like '%x%') then the query should return P3.x, P3.1, P3.2, P1.2x and P1.

You can assume that there won't be more than one level but if you have a solution that works for unlimited levels that is even better :)

I have a query that works but it is probably also the least efficient and does not take advantages of the fact that I'm using the nested set model.

SELECT
  *
FROM sr_projects
WHERE id IN (SELECT
  id
FROM sr_projects
WHERE parent_id IN (SELECT
  id
FROM sr_projects
WHERE name LIKE '%x%'))
OR id IN (SELECT
  id
FROM sr_projects
WHERE id IN (SELECT
  parent_id
FROM sr_projects
WHERE name LIKE '%x%'))
OR id IN (SELECT
  id
FROM sr_projects
WHERE name LIKE '%x%');

If cannot use stored procedure because CakePHP does not support them.

The nested set model makes this kind of queries, for unlimited levels, easy to write:

SELECT *
FROM sr_projects
WHERE name LIKE '%x%'

UNION ALL

SELECT p.*                                 -- the parents
FROM sr_projects AS p
  JOIN
    ( SELECT lft
      FROM sr_projects
      WHERE name LIKE '%x%'
    ) AS x
    ON p.lft < x.lft AND x.lft < p.rght

UNION ALL

SELECT ch.*                                -- the children
FROM sr_projects AS ch
  JOIN
    ( SELECT lft, rght
      FROM sr_projects
      WHERE name LIKE '%x%'
    ) AS x
    ON x.lft < ch.lft AND ch.lft < x.rght ;

Test at SQLfiddle-1

It might be more efficient if you use a temp table, because the subquery with the unsargable condition (name LIKE '%x%') will only be run once. But that depends on the version of MySQL you are using. Some newer versions are capable of subquery optimizations (and can actually cache and reuse the subquery results and even automatically index them):

CREATE TEMPORARY TABLE temp_x AS
SELECT *
FROM sr_projects
WHERE name LIKE '%x%' ;

and then:

SELECT *
FROM temp_x

UNION ALL

SELECT p.*                                 -- the parents
FROM sr_projects AS p
  JOIN
    temp_x AS x
    ON p.lft < x.lft AND x.lft < p.rght

UNION ALL

SELECT ch.*                                -- the children
FROM sr_projects AS ch
  JOIN
    temp_x AS x
    ON x.lft < ch.lft AND ch.lft < x.rght ;

DROP TABLE temp_x ;

Test at SQLfiddle-2


Error moving SQL Server 2000 to SQL Server 2005 NOLOCK Error I need to move a database from the  SQL Server 2000 (32 bit) to SQL Server 2005.But,When I attacted my database to SQL Server 2005,it's gives me error.(NOLOCK error)

Also , After I running dbcc check db command for database errors,I am receiving the following error.

Server: Msg 8929, Level 16, State 1, Line 1

Object ID 2: Errors found in text ID 727515136
owned by data record identified by RID    =   (1:391:14) id = 491864819 and indid = 9.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 727646208
owned by data record identified by RID = (1:391:16) id = 491864819 and indid = 11.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 768802816
owned by data record identified by RID = (1:2243:15) id = 1796253504 and indid = 54.
Server: Msg 8929, Level 16, State 1, Line 1

CHECKDB found 0 allocation errors and 16 consistency errors in database 'XXXXX'.
DBCC execution completed. If DBCC printed error messages,
contact your system    administrator.

The best online resource for database corruption in SQL Server world is Paul Randal's web page. He is our world's number one star in corruption matters. And that's because he wrote all dbcc check commands in SQL Server (and he's the only one who can understand them properly :-).
What I'd do in your place now would be to try the backup-restore method of moving the database to new SQL Server or try dbcc with repair option (not before making a backup of the database).
There is some good info also on MSDN about how to treat these errors. Check the article Troubleshooting DBCC error 2570 in SQL Server 2005.


SSIS ACE.OLEDB.12.0 Driver issue We have a SSIS package which is using a Excel destination. It was brought to my notice that the job was failing with the common error message 'Microsoft.ACE.OLEDB.12.0' is not registered. I checked the server and could locate this driver in the registry. I exported the package into my local and execute it and it ran successfully. I thought of checking to rebuild the package again and test it from the server. Is there any other idea/solution? Please suggest.

A common cause of this problem can be the difference between 32 bit and 64 bit installations.

If your SSIS package is running in 64 bit the Access driver needs to be installed in 32 bit too.

My guess would be that on your machine you installed the 64 bit driver, but on the server the 32 bit driver has been installed.

You could test this by executing the package from the 32 bit dtexec found here:

C:\Program Files (x86)\Microsoft SQL Server\<version>\DTS\Binn

And trying to execute it from the 64 bit dtexec found here

C:\Program Files\Microsoft SQL Server\<version>\DTS\Binn

See the documentation for more information dtexec Utility (SSIS Tool)

Then it's just a matter of installing the correct drivers (32 or 64 bit)


What's the difference between EXECUTE AS USER and EXECUTE AS LOGIN that makes the second one behave unexpectedly? Recently I've been using EXECUTE AS LOGIN to check if a certain person could or couldn't use a table function in one of our databases. The tests showed that he could but he repeatedly reported failure.

The person uses login 'WEB' to connect to the database and he has the user 'WEB' in that database associated with login 'WEB' so I tried the following scripts:

-- part 1
EXECUTE AS USER = 'WEB'
GO
SELECT
    USER_NAME() AS 'user_name'
    ,SUSER_NAME() AS 'suser_name'
    ,SUSER_SNAME() AS 'suser_sname'
    ,SYSTEM_USER AS 'system_user'
GO
REVERT
GO

and

-- part 2
EXECUTE AS LOGIN = 'WEB'
GO
SELECT
    USER_NAME() AS 'user_name'
    ,SUSER_NAME() AS 'suser_name'
    ,SUSER_SNAME() AS 'suser_sname'
    ,SYSTEM_USER AS 'system_user'
GO
REVERT
GO

The first part was fine with the result of:

  WEB | WEB | WEB | WEB

But the second result was a bit confusing:

  dbo | WEB | WEB | WEB

What's the difference between EXECUTE AS USER and EXECUTE AS LOGIN that makes the second one fail? Besides, of course, the first one being database level and the second one being server level impersonation, of what I'm aware of and does not explain the situation here.

A member of sysadmin group is always mapped to dbo, even if it has an explicit user in the database. So it looks like the login WEB is member of sysadmin, hence USER_NAME() correctly returns dbo in the second case.


Updating SQL table from Excel sheet (using powershell) I am using the below code to complete a bulk import into SQL, but I think I have run into a problem with my method.

$serverName = "pjim-app-02\tsg";
$databaseName = "User_Data" ;
$tableName = "dbo.AD$" ;
$filepath = "\\pjim.bne.catholic.edu.au\emmauscollege\media\cardexchange\carddata.xlsx";

#create object to open Excel workbook
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($filepath)
$Worksheet = $Workbook.Worksheets.Item(1)
$startRow = 2

#create System.DataTable
    $dt = new-object "System.Data.DataTable"
    [void]$dt.Columns.Add("StaffStudentID", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("SurName", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("FirstName", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("Year", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("HomeGroup", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("DOB", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("StaffStudent", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("email", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("ImageName", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("CardKey", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("F11", [System.Type]::GetType("System.String"))
    Do {
        $ColValues1 = $Worksheet.Cells.Item($startRow, 2).Value()
        $ColValues2 = $Worksheet.Cells.Item($startRow, 3).Value()
        $ColValues3 = $Worksheet.Cells.Item($startRow, 4).Value()
        $ColValues4 = $Worksheet.Cells.Item($startRow, 5).Value()
        $ColValues5 = $Worksheet.Cells.Item($startRow, 6).Value()
        $ColValues6 = $Worksheet.Cells.Item($startRow, 7).Value()
        $ColValues7 = $Worksheet.Cells.Item($startRow, 8).Value()
        $ColValues8 = $Worksheet.Cells.Item($startRow, 9).Value()
        $ColValues9 = $Worksheet.Cells.Item($startRow, 10).Value()
        $ColValues10 = $Worksheet.Cells.Item($startRow, 11).Value()
        $ColValues11 = $Worksheet.Cells.Item($startRow, 12).Value()
        $startRow++
        $dt.Rows.Add($ColValues1,$ColValues2,$ColValues3,$ColValues4,$ColValues5,$ColValues6,$ColValues7,$ColValues8,$ColValues9,$ColValues10,$ColValues11)
        }
    While ($Worksheet.Cells.Item($startRow,2).Value() -ne $null)

$Excel.Quit()

#connect to SQL Server and import the system.data.table
$SQLServerConnection = "Data Source=$serverName;Integrated Security=true;Initial Catalog=$databaseName;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $SQLServerConnection
$bulkCopy.DestinationTableName = $tableName
$bulkCopy.WriteToServer($dt)

This will only import everything, not just the changes.
I thought I would be able to get around this by enforcing a unique key, so that it would not import the records that already exist, but instead it just errors out during its bulkcopy stage.

Can someone please advise if there is a way using the above code that can complete this as an update?

As a work around I was going to import it into a new table, sync the new table with the existing one inside of SQL, then wipe the data from the new table ready for the next time the script runs.

This is for a rather simple recording database, and will only need to be run once a day.

Can anyone advise if I can re-use this script (as its already partly in place), or if there is a better way to achieve this (I have been unable to get the excel sheet setup as a linked table due to permissions)?

Thank you,
Wofen

Bulk load processes do just that - load data. They are not intended for updating existing data, or even for adding rows that don't already exist. As you surmised, the best method for those things is generally to load the data to a staging table, run a process that moves staging data to the main table (whether that's updating or replacing existing rows adding new rows, or some combination of the above), then clearing the staging table.

I'd set up a stored procedure in SQL to handle the data in the staging table, and run that after loading the staging table. You could use your existing script, but would need to add the code to run the stored procedure (and, of course, would need to create the stored procedure on your SQL Server).


when can AG primary replica be a different host to WFC primary? To set up an Availability Group with two servers, the servers both participate in a Windows Failover Cluster.

I've seen cases after certain failover modes where the primary IP address in the cluster (in Windows Server Manager -> Failover Cluster Manager -> Cluster Core Resources) can be the address associated with the secondary replica in the AG.

Is this a problem – does it matter at all?

More generally, is there any reference on how the WFC relates to the AG, and to the Availability Group Listener?

This is easiest to explain if you think about having multiple Availability Groups in your environment rather than just one. Each AG listener would have its own name and IP, and it could be failed over independently to either physical node.

In that environment, there are a few different parts:

The physical nodes - each of which has its own IP address
Each Availability Group listener - each of which has its own virtual name and address, and fails over back and forth between the nodes depending on who owns that particular Availability Group at that moment in time
The cluster's management point - which also has its own virtual name and address, and can be different than the AG listener.

See, one of the nodes has to own the cluster. This becomes more and more important as your cluster grows out - for example, to multiple physical nodes rather than just two. That cluster manager keeps control of the cluster's state, which nodes own which moving parts, and so on.

All Windows clusters - even a simple 2-node, 1-AG setup - have this separate name and IP address for management. Most of the time, it doesn't matter who owns the cluster, but in a multi-node cluster (say, 3 nodes in your primary data center and 2 nodes in your disaster recovery data center) where you want to get down to last-man-standing as things fail, then it does start to matter.


what does a "/" mean after a sql statement I have a script that we are using to update our ERP database to a new version,

UPDATE ENUM_CODES SET COLUMN_NAME = 'PAY_STATUS' WHERE TABLE_NAME = 'PAYABLE' and COLUMN_NAME = 'STATUS'
/

but i dont know why it has the / after the update portion.
Does anyone know what that / means?

/ is the Oracle equivalent of SQL Server's GO...

It would seem that your ERP supports multiple RDBMS platforms, and you're looking at a script originally written for an Oracle database.

As it is currently written, you'll probably get a compilation error on SQL Server. Trade that / for a GO and you'll be in business (assuming the rest of the script is correct, of course).


Creating a report that fills in when the user makes a data selection from a combobox? I want to make a report that acts as an empty template, but when the user makes a selection from a combo box or pop up window, the template fills with all of that data related to that primary key.

For example, my database stores information about purchases. I want the user to be able to select a vendor from a drop down list and see all the pertinent information such as how much money we spend with them, how long they have been an active vendor, and charts that show trends over time.

How can this be done if the vendor name is a primary key in the table?

There are many ways to filter and produce reports based on user input within Access.  Access is flexible enough that to explain even one approach can either require entire tutorials or be too specific to be generally useful.  A lazy response would be to simply mark this question as too broad, but similar questions have been asked repeatedly on Stack Exchange forums (i.e. Stack Overflow), but with incomplete answers, so I think it justifies at least some direction here.

First, search the web for something like "access report from combobox" and you should get links to various websites and video tutorials that have plenty of examples and hints.

A vary loose outline of how I would go about this:

The general technique should be to base the report on a query that references a ComboBox control on a Form.

For example, a Form named Vendor Detail with a ComboBox control named Vendor Name could be referred to in the Report query like ... WHERE MyTable.Vendor = [Forms]![Vendor Detail]![Vendor Name] (This is of course only a snippet of the complete query SQL.)
DO NOT place the ComboBox on the report itself, even though Access will allow you to do this.  Such attempts will likely lead to unexpected, buggy behavior.
This technique requires that the Form remain open with a valid value selected in the ComboBox.  If the Report were to be opened directly without the Form, it would display an "ugly" prompt asking for the [Forms]![Vendor Detail]![Vendor Name] value.  You could optionally type in a value and continue producing the report, but that is not very user friendly.

A Form can include a button--alongside the ComboBox--that opens and/or refreshes the report when a different Vendor is selected.

The button event can execute either a Macro or a VBA code to open the report.  The details for this are beyond the scope of this answer, but can be found by searching the web and/or Stack Overflow.
The report can either be displayed on screen as a preview before printing, or it can be printed directly.

The other details of the report creation or the query it is based on are also beyond the scope of this answer, but there are no other special requirements, so all other aspects can be handled like any other Access Report.


Very slow query on table with Blob column I have a table with approximately 3500 rows.  This table contains a longblob column with an average blob size of 750kb.  My query performance really deteriorates when trying to filter on the primary key column.

Select * from tblphotos  yields a response in 1 second, returns 3500 rows
Select * from tblphotos where ActLogId = 20992   yields a response in 130 seconds, returns 6 rows.

I'm looking for any sort of rationale as to why this is so slow and how to make this quicker.  Moving from ROW_FORMAT=COMPACT to DYNAMIC improved performance about 30%.

CREATE TABLE `tblphotos` (
  `PhotoID` int(10) NOT NULL,
  `ActLogID` int(10) DEFAULT NULL,
  `Order` smallint(5) DEFAULT NULL,
  `PhotoFile` longblob,
  `PhotoA` varchar(5) DEFAULT NULL,
  `Caption` varchar(100) DEFAULT NULL,
  `Entered` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

--
-- Dumping data for table `tblphotos`
--

INSERT INTO `tblphotos` (`PhotoID`, `ActLogID`, `Order`, `PhotoFile`, `PhotoA`, `Caption`, `Entered`) VALUES
(1, 19264, 1, blobvalued, 'JPG', 'Nameplate', NULL);

ALTER TABLE `tblphotos`
  ADD PRIMARY KEY (`PhotoID`),
  ADD KEY `ActLogID` (`PhotoID`,`ActLogID`,`Order`,`PhotoA`,`Caption`,`Entered`,`PhotoFile`(20)) USING BTREE;

-- AUTO_INCREMENT for table `tblphotos`
--
ALTER TABLE `tblphotos`
  MODIFY `PhotoID` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3421;

You need an index starting with ActLogId.

The indexes with lots of columns are likely to be not very useful.  Recommend getting rid of them, and redesign the indexes.

"Prefix" indexing (PhotoFile(20)) is rarely useful.

Read this cookbook for tips on creating the optimal index for a query.

where PhotoID > 0 AND ActLogId = 20992 needs a composite INDEX(ActLogId, PhotoID), in that order (= first).


Covering Index - Include columns I have a table :

[sch].[Parent]
(
     ID Identity [INT] (PK),
     Name VARCHAR(100),
     Email VARCHAR(250),
     Active TINYINT,
     InsertedBy VARCHAR(100),
     InsertedDTM DATETIME,
     UpdatedBy VARCHAR(100),
     UpdatedDTM DATETIME,
)

This table has PK on the ID column and a UNIQUE NONCLUSTERED CONSTRAINT on Name.

When I query a normal SQL without a WHERE clause:

SELECT ID, Name
FROM sch.Parent

This query uses a Index Scan and this is the case when I use a INNER JOIN with another CHILD table which has an FK in another table

What covering index should I use so that there is Index Seek in case of normal query and INNER JOIN with another table as well?

SELECT
     b.Id AS 'ParentsChildId'
    , b.[Name] AS 'ParentsChildName'
    , a.Id AS 'ParentId'
    , a.[Name] AS 'ParentName'
FROM
    [sch].[ParentsChild] b
INNER JOIN
    [sch].[Parent] a
ON  a.Id = b.Id
WHERE
    a.Active = 1 AND b.Active = 1

(I'm assuming SQL Server in this answer, please clarify which RDBMS you're using if it's not SQL Server)

The index you want is:

CREATE INDEX [IX_Parent_Id]
ON [sch].[Parent] (Id) INCLUDE (Name)

You use INCLUDE when a column isn't being filtered on (isn't in the WHERE clause) but is being selected. INCLUDE means that the column will only be included at the leaf level of the index (it's a small efficiency saving, essentially). Also as noted, if you have a SELECT statement without a where clause, indexes will never be used (faster to just scan the table rather than muck about with an index). Index seek is only used when you're returning a comparatively small number of rows, for your sample query if most of your ParentsChild and Parents rows are marked as Active then the optimiser may well decide to index scan instead of seek. A query that would be more likely to get an index seek instead of scan would be something like:

SELECT
     b.Id AS 'ParentsChildId'
    , b.[Name] AS 'ParentsChildName'
    , a.Id AS 'ParentId'
    , a.[Name] AS 'ParentName'
FROM
    [sch].[ParentsChild] b
INNER JOIN
    [sch].[Parent] a
ON  a.Id = b.Id
WHERE
    a.Active = 1 AND
    b.Active = 1 AND
    a.Id = 2

(getting the children of Parent ID 2)


Will altering order of columns in GROUP BY affect performance? I have a SQL query like this:

SELECT A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END), COUNT(D)
FROM SomeTable
GROUP BY A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END)

On a huge dataset the actual execution plan attributes most of the time to a node that corresponds to logical operation Aggregate and physical operation HashMatch.

Will changing the order in which the columns and conditions are listed in the GROUP BY list affect performance?

Despite what MSDN documentation says, no, it doesn't matter for GROUP BY queries.

You can test it here, at: SQL-Fiddle test (SQL-Server 2012)

CREATE TABLE test
( id INT IDENTITY(1,1)
, a  INT NOT NULL
, b  INT NOT NULL
, c  INT NOT NULL
, d  INT NOT NULL
, PRIMARY KEY (id)
) ;

CREATE NONCLUSTERED INDEX a_b_c_include_d_index
    ON test (a, b, c)
    INCLUDE (d) ;

INSERT INTO test
(a, b, c, d)
VALUES
 ... some 50K rows

The queries:

SELECT COUNT(*) AS num
     , MIN(cnt) AS min_count
     , MAX(cnt) AS max_count
     , MIN(min_d) AS min_min_d
     , MAX(min_d) AS max_min_d
     , MIN(max_d) AS min_max_d
     , MAX(max_d) AS max_max_d
FROM
  ( SELECT a
         , b
         , c
         , COUNT(d) AS cnt
         , MIN(d) AS min_d
         , MAX(d) AS max_d
    FROM test
    GROUP BY a, b, c             --- or with:  `GROUP BY b, a, c`
  ) AS grp ;

produce the same execution plan:

MICROSOFT SQL SERVER 2005 XML SHOWPLAN
SELECT
Compute Scalar
Cost: 0%
Stream Aggregate
(Aggregate)
Cost: 0%
Compute Scalar
Cost: 10%
Stream Aggregate
(Aggregate)
Cost: 10%
Index Scan
[a_b_c_include_d_index].[test]
Cost: 89%

Now if you change that Group by to:

    GROUP BY a, b, c
      WITH ROLLUP

it does make a difference and it does produce different execution plans (and different result sets of course). It still uses the index though, at least in that SQL-Fiddle test. It the mismatching order case, it gets the data from the index but it has to do an intermediate sort (to calculate the rollup values).


MySQL innodb_log_file_size change is not being recognized I am trying to set the innodb_log_file_size option in a MySQL 14.14 (distrib. 5.6.30) installation on Ubuntu 15.10 to solve an issue (importing a database with large BLOB fields)
 however the log size keeps defaulting back to 48MB. I have read a number of posts about this but have not yet found a solution. Any help is appreciated.

My process:

SET GLOBAL innodb_fast_shutdown = 0; (in a MySQL session)
/etc/init.d/mysql stop
rm /var/lib/mysql/ib_logfile0 and ib_logfile1
(edit my.cnf)
/etc/init.d/mysql start

In /etc/mysql/my.cnf (which is a symlink to /etc/alternatives/my.cnf which is a symlink to /etc/mysql/my.cnf.migrated):

[mysqld]
...
innodb_buffer_pool_size = 1000M
innodb_force_recovery = 0
innodb_file_per_table = 1
innodb_file_format = barracuda
innodb_read_io_threads = 16
innodb_log_file_size   = 1G
innodb_log_files_in_group = 4

I also set this variable in /etc/mysql/mysql.conf.d/mysqld.cnf in case that was taking priority.

The log file reads:

2016-05-19 09:25:23 24141 [Note] Plugin 'FEDERATED' is disabled.
2016-05-19 09:25:23 24141 [ERROR] Function 'innodb' already exists
2016-05-19 09:25:23 24141 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2016-05-19 09:25:23 24141 [ERROR] Function 'federated' already exists
...
2016-05-19 09:25:23 24141 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-05-19 09:25:23 24141 [Note] InnoDB: The InnoDB memory heap is disabled
2016-05-19 09:25:23 24141 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-05-19 09:25:23 24141 [Note] InnoDB: Memory barrier is not used
2016-05-19 09:25:23 24141 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-05-19 09:25:23 24141 [Note] InnoDB: Using Linux native AIO
2016-05-19 09:25:23 24141 [Note] InnoDB: Using CPU crc32 instructions
2016-05-19 09:25:23 24141 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-05-19 09:25:23 24141 [Note] InnoDB: Completed initialization of buffer pool
2016-05-19 09:25:23 24141 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2016-05-19 09:25:23 24141 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2016-05-19 09:25:23 24141 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2016-05-19 09:25:23 24141 [Warning] InnoDB: New log files created, LSN=2798779859498
2016-05-19 09:25:23 24141 [Note] InnoDB: Highest supported file format is Barracuda.

We tried the same procedure on an Ubuntu workstation with MySQL 5.7.x on Ubuntu 16.04 and it worked without issue.

Based on this, we decided to upgrade our server's OS to Ubuntu 16.04 which upgraded MySQL to v5.7.12. After changing my.cnf to reflect changes in some option names and restarting the service, the log files now have the correct sizes and the problem is resolved.


Stored procedure deadlocking itself I have a strange situation, seen from the log:

Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807.
Process 53807 waits for ExclusiveLock on advisory lock [16421,999999,12864395,2]; blocked by process 37278.
Process 37278: SELECT * FROM zel_api.insert_event ($1 ,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24)
Process 53807: SELECT * FROM zel_api.insert_event ($1 ,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24)",
"See server log for query details.",,,"SQL statement ""SELECT pg_advisory_xact_lock(999999, format('zel_event.%I', p_table_name)::regclass::oid::integer)""

This is already strange in itself, as it looks like two processes block the same advisory lock but neither can actually grab it.

The function which tries to acquire the lock is the following:

CREATE OR REPLACE FUNCTION zel_event.create_new_partition(
    p_table_name text
)
RETURNS void AS
$BODY$
DECLARE
    _schema text;
BEGIN
    IF NOT EXISTS (table from catalog)
    THEN
        PERFORM pg_advisory_xact_lock(999999, format('zel_event.%I', p_table_name)::regclass::oid::integer);

        IF NOT EXISTS (table from catalog)
        THEN
            EXECUTE format('
                CREATE TABLE %1$I.%2$I (
                    LIKE zel_event.%2$I INCLUDING ALL
                ) INHERITS (zel_event.%2$I)', _schema, p_table_name);
        END IF;
    END IF;
    RETURN;

END;
$BODY$
LANGUAGE plpgsql
SECURITY DEFINER;

To my eyes none of the 'classic' deadlock causes stands here as there is only one logic...
The idea behind it is to create the new table on demand where the demand can come at a really high frequency from several connections.  It is called when an insertion function does the insert aimed at the parent table, being diverted by a dispatcher trigger to the appropriate child table.

This is how the transaction looks like:

INSERT INTO parent (zel_api.insert_event())
        |
     trigger
        |
        +----> child exists? ---no---> CREATE TABLE child
                      |                         |
                     yes                        |
                      |                         |
                      V                         V
              INSERT INTO child (zel_event.create_new_partition())

The PostgreSQL version is 9.2

It would be very interesting to have some insights how this can happen (and mitigated, of course).

It's not the same advisory lock.  Your log mentions a different table oid (off by 10) for the first process as the second.  This looks like a classic deadlock situation where two processes are seeking the same locks just in a different order.  The third number in the first is 12864385 while the third number in the second is 12864395.

So my suspicion is that both processes are trying to create the same partitions, just in a different order and so they deadlock.

One thing I have found helpful in puzzling situations like this is to go through and compare one digit at a time all the way through the series of numbers because with advisory locks it is way too easy to miss one digit subtly off like this.


HOW to "SUM" and "JOIN" for single query of a poll results? I have a poll database as

CREATE TABLE poll_answer
answer_id,
answer varchar(255),
poll_id int(11),
FOREIGN KEY(poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE,
PRIMARY KEY(answer_id)
) ENGINE=InnoDB

CREATE TABLE poll_results
vote_id int(11),
user_id int(11),
answer_id int(11),
poll_id int(11),
FOREIGN KEY(answer_id) REFERENCES poll_answers(answer_id) ON DELETE CASCADE,
FOREIGN KEY(poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE,
PRIMARY KEY(vote_id)
) ENGINE=InnoDB

How can I run a query to sum the votes for each answer to get results for the list of answers in the poll?

SELECT ... FROM poll_results ... WHERE poll_results.poll_id='x'

answer_id  |  answer  |  SUM of Votes  |  % of votes

Assuming redundant poll_result.poll_id added intentionally to avoid extra join, you can get desired output for a given pool(parameter ':value') with

SELECT a.answer_id, a.answer, COUNT(1)  as num_votes,
IF(b.total = 0,0,100*COUNT(1)/b.total ) as pcnt
FROM poll_answer a
LEFT JOIN poll_results b ON (b.answer_id = a.answer_id)
INNER JOIN (select count(1) as total from poll_results WHERE poll_id = :value)b on (1=1)
WHERE a.poll_id = :value
GROUP BY a.answer_id, a.answer
-- I know mysql is ok with just GROUP BY a.answer_id, but just to make it more standard,
-- I use GROUP BY a.answer_id, a.answer


You have UNIQUE(end_time, instr), correct?  Then the fast query (SELECT * FROM cs.cs_15 where instr like '%X_T%' and end_time>'2016-09-22' LIMIT 0, 1000) will be performed this way.  (Confirm it by doing EXPLAIN SELECT ....)

Reach into that index at '2016-09-22'.
Scan forward, looking for instr like '%X_T%'
peel off the first 1000 that match.

Note: Apparently many of the rows have X_T, so the query ends quickly.

The slow query (SELECT * FROM cs.cs_15 where instr like '%X_T%' limit 1) will be performed this way.  (Confirm it by doing EXPLAIN SELECT ....)

No index is useful, so scan the entire table, looking for 1 row with instr like '%X_T%'.

Note: Apparently there is no such row early in the table.  That is, old end_times never had X_T.

There is not good way to speed up the slow query.  The leading wildcard in the LIKE make it useless to index that column.


Deploying a cube where database name has been changed I have an issue where I have renamed the database for which my cube deploys to from, say, SalesAndStuff to Sale.

This has been no issue, I have changed the database name in Management Studios etc.

My issue is that when I try to deploy my cube now from Visual Studio I get the error that

  The database with the name of 'SalesAndStuff' already exists in the 'abc'
  server

Which I have read is because SSAS figures that the database I wish to deploy my cube to should be to the database with the same name as the database ID created when I created the cube originally.

This name is SalesAndStuff. I have configured my cube under Project -> Cube properties -> Deployment -> Deploy to database Sale but for some reason this isn't recognized.

Any ideas how I can avoid this issue? Am I required to change my deployment properties to the original database, and subsequently rename the database in management studio?

The cube is used by many people who uses it for Excel reports etc. so I don't want to alter any Connection strings towards the cube.

This is basically because the Sale cube still has SalesAndStuff as the ID, renaming your database only changed the name

It will probably be the easiest if you just click "view code" in visual studio and change the <id> node to match your new name.

If that doesn't work out or you think that's too risky you can follow this procedure on msdn.

Make sure you only edit the ID for the database though. If you edit ID's for other objects such as dimensions you will mess up a lot of objects.

Create a backup of your database and solution before you try this.


How can I do a query that shows any row that have more than twice in the same table without using GROUP BY or COUNT I want to select all same item that occurs in the same table twice without GROUP BY or COUNT sentence. How to write the query?

For example, if the table is:

City
---------
New York
Los Angeles
London
Washington
London
Peking
London
San Fransisco
New York
Tokyo
Dubai

The result set should be like that:

City
---------
New York
London

(because 'New York' occurs 3 times and 'London' occurs twice in the original table)

with data as
(
select 'New York' as city from dual union all
select 'Los Angeles' from dual union all
select 'London' from dual union all
select 'Washington' from dual union all
select 'London' from dual union all
select 'Peking' from dual union all
select 'London' from dual union all
select 'San Fransisco' from dual union all
select 'New York' from dual union all
select 'Tokyo' from dual union all
select 'Dubai' from dual
)
select
  distinct city
from
(
  select
    city,
    row_number() over (partition by city order by null) as rn
  from
    data
)
where
  rn > 1
;

CITY
-------------
London
New York

No GROUP BY, no COUNT.

But we have DISTINCT, ROW_NUMBER(), PARTITION BY, ORDER BY.

Is it simpler/better than?

with data as
(
select 'New York' as city from dual union all
select 'Los Angeles' from dual union all
select 'London' from dual union all
select 'Washington' from dual union all
select 'London' from dual union all
select 'Peking' from dual union all
select 'London' from dual union all
select 'San Fransisco' from dual union all
select 'New York' from dual union all
select 'Tokyo' from dual union all
select 'Dubai' from dual
)
select
  city
from
  data
group by
  city
having
  count(*) > 1
;

No, I guess.


How can I stop Oracle Shutdown command? I used Shutdown normal, instead of Shutdown immediate.

Now I want to stop the process, because its taking too long.

How can I do it?

It seems like the obvious answer is that you can't stop a shutdown that is happening, but you can do either a shutdown immediate or shutdown abort from a different session if you did a shutdown normal. As long as you have enough online redo log groups and each group has a log that is large enough, then you can recover from either a shutdown immediate or shutdown abort. If you ask someone what is the exact risk that someone faces for doing a shutdown abort, they may struggle to answer you. On shutdown immediate transactions are rolled back as sessions are killed. On shutdown abort, all transactions are rolled back when the database is started up. Hence the same thing should be happening in both cases, just in a different order. Once you start a shutdown normal, either ask everyone to log out, or just do shutdown immediate in a different session. Either way, Oracle won't allow a database to open, if it isn't in a consistent state.


ORA-47500: XE edition memory parameter invalid or not specified Recently i tried to increase the memory_max_target in Spfile as well through cmd.But After restart the instance it shows "ORA-47500: XE edition memory parameter invalid or not specified".I couldn't start the instance,but later i started by create SPfile from pfile.why this error comes after change the memory?Should i need enterprise edition??

XE is limited to 1 GB, you can not increase its memory.

Oracle Database XE RAM Limitation

  The maximum amount of RAM that an Oracle Database XE database uses
  cannot exceed 1 gigabyte, even if more is available.


List column value for all databases that have a specific table and column What I am really looking for is the name of each database with table ApplicationVersion and column [Version] as well as the value in [Version]. Some of my databases don't have the specified table column and should be ignored. The table always has only one row.

Database    ApplicationVersion
DB_001      5.123.0.234
DB_002      5.123.0.234
DB_003      6.223.0.435

I really, really, really recommend against sp_MSforeachdb. It's easy, sure, but it is undocumented, unsupported, and buggy as all get out.

Here is a dynamic SQL solution:

CREATE TABLE #x([Database] sysname, ApplicationVersion varchar(32));

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
  BEGIN TRY
    INSERT #x EXEC ' + QUOTENAME(name) +N'.sys.sp_executesql
    N''SELECT DB_NAME(), MAX(Version) FROM dbo.ApplicationVersion'';
  END TRY
  BEGIN CATCH
    PRINT CHAR(32);
  END CATCH;'
FROM sys.databases
WHERE state = 0; -- database is online

EXEC sys.sp_executesql @sql;

SELECT [Database], ApplicationVersion FROM #x;

DROP TABLE #x;


SQL Server 2012 x64 - cannot safely allocate more than 50% RAM We're running SQL Server 2012 SP1 CU4 on a 64-bit 12-core, 2 socket server using NUMA architecture server. OS is Windows Server 2008 R2 x64.

Whenever we allocate more than 50% of the physical RAM to SQL server, the machine becomes unstable or unresponsive.

The symptoms are typical of OS memory starvation - i.e. process fail to start, GUI object fail to render, applications misbehave, remote desktop sessions become unresponsive, etc.

We’ve seen this on 2 different machines – one with 192GB RAM, the other with 256GB RAM…  as soon as SQL is given more than 50% of the respective physical total, the symptoms appear.

Has anyone else seen this behavior?

--- EDIT ---

The SQL service runs under an account with LPIM (Lock Pages in Memory) privileges.

McAfee antivirus is, sadly, imposed on the server, though it is at least set up with exclusions for all SQL files.

When the RAM utilization is allowed to exceed 50%, the common behavior we see is:

High RESOURCE_SEMAPHORE wait times.
Inability to connect to SQL server or run queries.
All CPUs maxed out with low disk I/O when running query workloads.

-- EDIT 2 ---

We've tried re-installing SQL (SP1 CU4),  and have checked that nothing else is chomping the RAM. Generally, there's at least 100GB of the total 256GB free at any time.
When we turn off LPIM, we get see the problem "the operating system has swapped at a significant portion of SQL's memory", which is why we have it turned on.

I'd check the perfmon metrics for per-cpu CPU busy, as well as the perfmon metrics instance-wide and per NUMA node for PLE, database pages, etc.
Two recent SQL Server KBs

FIX: SQL Server performance issues in NUMA environments
FIX: You may experience performance issues in SQL Server 2012

However, for some workloads, especially with higher core count, high concurrent query count, and lots of database disk IO, using trace flags 8015 (disable NUMA support at SQL Server level) and 8048 (remove per-socket query memory allocation botlleneck) will provide even better results than the fixes contained in SQL Server 2012 SP1 CU4. (I confirmed this on our test equipment with simulations of our workload - YMMV)

Trace flag 8015 deserves thorough evaluation before deployment.  Memory affinity and attending lower memory latency is sacrificed for managing a single large bpool.  It also results in one lazy writer instead of one per NUMA node, and the idea of affinitizing a connection endpoint within a single SQL Server instance to each NUMA node also disappears.  But for some workloads the benefits are undeniable.  Don't use 8015 without 8048.

I've never seen or heard of any measurable cost to adding trace flag 8048, and if CMEMTHREAD waits and associated spinlocks are triggered by query memory allocation - its the only reliable way to eliminate them.


ola-hallengren index rebuild with multi thread and multiple days Ola scripts for index maintenance work really well for smaller databases. For multi terabyte databases with big indices, many partitions, and a small interval of schedule times I observed that the script always tends to rebuild same indices and never finishes.

How can we implement the solution to capture and then run the index optimization for certain duration of days and then reset. Can we also have it run in multiple threads?

We did develop a solution to achieve it, but the only problem we have is updating it on regular basis.

For multi terabyte databases with big indices, many partitions, and a small interval of schedule times I observed that the script always tends to rebuild same indices and never finishes.

This is very common for VLDBs. You can be a bit smatter and follow Paul Randal's Quick list of VLDB maintenance best practices. You can rebuild indexes ONLINE in Enterprise edition.

In your case since you are using Ola's Index Optimization scripts, you are running into endless index defragmentation which you can solve by being analyzing CommandLog table

I have given few more thoughts on Is a weekly rebuild of indexes a good idea? covering maxdop option.

I would say, if your databases are extremely big (TB size), atleast do a reorg instead of rebuild and manually update stats. This will put you in a good reasonable state since a reorg will start from where it got killed.

For running in parallel threads, you can drive of commandLog table and use powershell runspace (be careful of not spawing many threads - see Notes on usage of using runspace).