texts:
I have a table that represents a specific type of action. Let's call it [ACTION].
An action can be performed by a user ([USER]), so I have the relation [USER] 1:N [ACTION].
The user can have children ([CHILD]), so [USER] 1:N [CHILD].
The problem starts by the fact the action must be performed by the user, however it can be performed for the user or one of its children.
If every user always had one or more child, I could simply do [CHILD] 1:N [ACTION] instead of [USER] 1:N [ACTION], because over the child I will find the user who performed the action.
If I simply add [CHILD] 0...1:N [ACTION], so the child becomes an optional field in the action table, it's possible a child is added that does not belong to the user that is added on the action.
Questions

What is this scenario called, typically?

How to overcome this problem or what is the best way I can go?

I would not like to check this in code, a database-only (SQL Server 2012) solution would be perfect.
Example
The action is an investment. The user will always take the investment, but he/she can decide he/she is investing the money for him/her or for one of his/her children. In this scenario children are underage, always. If a child is old enough to do investments of its own, it will register and become a user, instead. So this is why a child cannot be a user. They need to stay two different entities.
So I kinda need two relations that stay stable to each other. I need to know who (user) took the action (investment) and I need to know for who (user or one of his/her children) the action (investment) is taken.
What I'm seeking is a solution where an action (investment) cannot be made for a child that the user has no relationship to. In other words, the father/mother can only invest for him/her or his/her child, not for the child of someone else.


texts: Oracle ODBC connection failure (using oracle's stock ODBC drivers)

texts:
I want to use an Oracle-ODBC connection in Visual Studio 2017/ SSIS as it's much faster than OLE DB during tests.

Problem:

I follow Oracle's steps to the letter.

  Install instant client (v18, also tried v12).

  Download/ extract ODBC download in same library. Run odbc_install.exe.

  See the Driver in 'ODBC Data Sources/ Admin' in Windows 10.

  Add new User Data Source. TNS Service names pull up fine. Test
  Connection (User/ Pass) -- it works!! The Connection works!!

I tried this with 64 bit in Oracle, their instant client v18.3 or 12.2 both. All works in Window's "Oracle Source Administrator" via test connections.

I tried this with 32 bit downloads as well. All is good.

Now, open Visual Studio. First tried 64 bit (my Windows OS is 64 bit, but Visual Studio Data Tools is only 32 bit). Had a hunch it wouldn't work.

Error message "system architecture and client is not the same" or such. Gotcha.

Tried the 32 bit Oracle ODBC driver (User Source). I keep getting the same message (tried 18_3 and 12_2 versions).

Now .... SQLORA32.dll is in the very file path it named. It's right there! Why can't it be found? The test connection in ODBC Source Admin works! What is going on here?

And I'm unsure if I have to "register" something via the command line, I had to do that once before, maybe it was an unrelated issue.

To boot, when I tried a 3rd party "Devart Oracle ODBC connector" -- it's a simple 5-second install wizard that works flawlessly instantly. Problem is it's a 30-day trial and costs $150 at least. How can I can get an Oracle-created ODBC connector (Oracle being world-renowned for janky-azz products) to actually work?

Devart, and probably Attunity Oracle ODBC: 5 second installs

Oracle's own: Harder to install than breaking into Fort Knox/ learning Mandarin Chinese. Please advise.


texts:
Possible Duplicate:
  How to revive SQL Server 2008R2 performance counters?

On one machine, I have an instance of SQL Server 2008R2 Enterprise, Visual Studio 2010 and VS 11 Developer Preview. I tried to install SQL 2012 RC0, but it failed due to come conflict with VS 11, so I removed it. Unfortunately, the process seems to broken performance counters. They're missing from permon, as well as sys.dm_os_performance_counters.

On another machine, I upgraded from SQL 2005 Standard to SQL 2008R2, and perf counters also vanished there in the process.

Is there anything I can do to restore the counters, short of removing and then re-installing SQL Server?


texts:
I recently tried to restore to my local development SQL Server a backup from a network instance.  To my surprised I received the following error message:

  Msg 12824, Level 16, State 1, Line 3 The sp_configure value 'contained
  database authentication' must be set to 1 in order to restore a
  contained database.  You may need to use RECONFIGURE to set the
  value_in_use. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is
  terminating abnormally.

What steps must I follow to successfully restore the database?


texts: When do i need to DEALLOCATE a prepared statement in a MySQL stored procedure?

texts:
My question is simple. In what situations is it better to deallocate the prepared statement and thus removing it from memory and in what situations is it better to keep it in memory?

I have written a stored procedure that uses 2 prepared statements. One of the first few lines is to prepare the statement like so:

PREPARE selectStatementByJournalEntryAndLanguageQuery FROM "INSERT INTO results(id, content, title, language_id, journalentry_id) SELECT * FROM JournalEntryTitleAndContent jetc WHERE jetc.language_id = ? AND jetc.journalentry_id = ? LIMIT 1";
PREPARE selectStatementByJournalEntryQuery FROM "INSERT INTO results(id, content, title, language_id, journalentry_id) SELECT * FROM JournalEntryTitleAndContent jetc WHERE jetc.journalentry_id = ? LIMIT 1";

Now this stored procedure and thus these prepared statements could be used many times a day by many users. In this case it is better to not call DEALLOCATE on both prepared statements right? Because then the prepared statements remain in memory and do not have to be recompiled when someone else uses the stored procedure. But that raises a question about the 2 PREPARE statement lines. If i do not deallocate the prepared statements at the end and someone else starts the stored procedure, will the database then try to PREPARE the prepared statements again or will it ignore these 2 code lines when it detects that those prepared statements have already been prepared during another database stored procedure query session.

Thank you


texts: SELECT all substrings (n-grams) of length n?

texts:
How could I select all n-grams, ie. substrings of length n from a string using SQL? For example, the 3-grams of string example are exa, xam, amp, mpl, ple.

I'm using PostgreSQL to be more precise.


texts: InnoDB files created even innodb_file_per_table is OFF in mysql-5.7

texts:
We have installed mysql 5.7 in ubuntu 16.04 and we got big surprise because databases are being stored individually in each folder even parameter innodb_file_per_table is 0.

We decided to change innodb_file_per_table to 0 because there are over 6000 databases with 40 tables in each of them. We have to migrate to this solution actually because current solution with innodb_file_per_table=ON is causing too many performance issues giving "too many open files" at error log frequently.

In this current server We are not able to do a simple backup (dump) because mysql-server-5.6 is crashing in --all-databases parameter.

We think if we configure a new mysql-server-5.7 with innodb_file_per_table=OFF we will get better performance..

Any advice/help will be welcome.


texts: Ok to remove SQL Server Compact 3.5 from a SQL 2008 R2 Server?

texts:
We still have one production SQL 2008 R2 Server.  Yes, there are plans to upgrade soon, just not immediately.  In the meantime, our Vulnerability reports are flagging the server as vulnerable because it has End of Life software:

EOL/Obsolete Software: Microsoft Visual Studio 2008 Detected
EOL/Obsolete Software: Microsoft SQL Server Compact 3.5 Detected

So we want to see if we can remove these components to resolve the vulnerability.
However, doing due diligence, I noticed that the install date for these under Add/Remove Programs is the same as the install date for SQL Server 2008 R2 itself.
So now I'm thinking these are "part of" SQL Server 2008 R2, and maybe it's not safe to remove them.  Googling doesn't reveal anything definitive, with a lot of the found articles no longer available.
My question is can the be removed without negative impact to SQL Server 2008 R2, and if not, can they be upgraded to a version that's not EOL?


texts: Schema for stores and groups of stores and companies

texts:
Perhaps a silly question. I am building a database where I keep a number of stores in a Store table. Each store obviously have some data as geo coordinates, address etc. Right now these stores are stores with actual products in Product table.

My dilemma is that some of these stores are part of a larger group for example A companies group has B store group and B store group has 100 stores. I am not sure exactly what would be the best way to organize this.

First option is perhaps to create a parent_id column to Store table and add a store without any products and make it parent to many other stores, and add company groups as parent to those store entries. I can add an extra boolean column store_group to mark such entries as groups. However I feel this may be having too many different logical entities in the same table perhaps?

Second option is to introduce a StoreGroup table where I keep the companies (A) and their store groups (B). Within StoreGroup I self-reference to itself using a parent_id column to associate (A) with (B). Then use a parent_id column in Store table to point stores (C) to store group (B).
This seems to be more logical as companies and store groups are logically different than stores. As they do not have products directly.

Could there be a better name? Company instead of StoreGroup perhaps?

Any ideas on how this can be made better?

Thanks!

Update: Perhaps a more realistic example could be Walmart Inc. (A). They have Walmart U.S., Walmart International, and Sam's Club U.S. store groups (B). Totaling up to 11k stores (C).


texts: SQL Server AlwaysOn cluster having issues seeding large availability database creating strange behavior

texts:
I set up a 2-node SQL Server 2016 AlwaysOn Availability Group cluster (1 primary, 1 secondary).  The underlying hardware is fairly powerful; 4 CPUs, 16 GB of RAM and local-attached SSD disk.  Then I restored 10 databases on the primary.  One of these databases was large, about 38 GB in size, while the others were relatively compact being in the 0.5 - 2 GB range in size.  The restore happened quickly - then I added these 10 databases, all at the same time, as Availability Databases and chose Seeding as the method to initialize the replicas.

This is when the strange behavior happened.  The first 6 databases were copied across as expected and appear as Synchronized in the list of databases on the secondary replica - including the 38 GB database.  The CPU utilization on the secondary replica went from 0% to about 20-50%.  Then the remaining 4 databases never appeared.  So I ran this query:

SELECT
 r.session_id, r.status, r.command, r.wait_type
 , r.percent_complete, r.estimated_completion_time
FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s
 ON r.session_id = s.session_id
WHERE r.session_id <> @@SPID
AND s.is_user_process = 0
AND r.command like 'VDI%'

And here is the output of the above query:

So it seems that the other databases are not getting seeded because they are sitting in the queue with status 'background'.  So I decided to wait 16 hours.  16 hours later, the CPU usage on the secondary replica is still in the 20-50% range.  There are no queries being issued on the cluster.  The Primary Replica is in Synchronized state for all of the databases and is at 0% CPU utilization (and is running on the same type of hardware).  However, the 4 databases are showing as Not Synchronized on the secondary replica.  The cluster is using a Cloud Witness for Quorum mode.

So my questions are:

Why are the 4 small databases still not seeded onto the secondary replica?
Why does the secondary replica seem to be stuck doing some kind of large CPU or I/O bound task?
What should I do now?  Should I keep waiting (it's already been 12 hours) to see if it finishes?

Any assistance here would be greatly appreciated.


texts: SQL Server 2014 schema changes in 24/7 multi user environment

texts:
We have SQL Server 2014 Enterprise installed to run a database which should be available 24/7. Our database is huge enough (200gb +). Also we have a lot of services that hits our database every minute to read, update or insert new data. We want to provide a "hot" redeploy feature for our clients and make our daily updates (.net and schema updates) transparent to clients. We have found a solution based on cluster with load balancer to update binaries of our app, but we still have some misunderstanding about database`updates deployment process and what are the best practices to solve this issue.

For schema changes, bring one server down, apply schema changes, bring it back up, and then apply same changes to the second instance.
Can it be accomplished with SQL Server tools, and is this a common approach? How sync data after server is backed up? Or am i completely thinking into the wrong direction and are there better solutions?

Our common Schema changes: add/drop column, add/delete stored procedure


texts: One database in AlwaysOn Group not truncating log file

texts:
I have been stuck with this issue for two days now and am not hoping someone here might know the answer.

I have multiple databases in one availability group, all of the databases follow the same backup plan. (Full Recovery, Full Backups & hourly Log backups).

One database, and only one, is currently refusing to truncate (reuse) it's log file. It has now grown slowly to 33GBs where the actual database itself is less than 512MB (so tiny).

I checked if there are any running transactions (there aren't) if the log backups run (they do) and everytime I manually do a log backup to check why the truncate isn't happening I get; AVAILABILITY_REPLICA as the reason.

Point is that when looking at the availability dashboard everything is green, there are no Log Queues, no Redo Queues, everything looks as if it is good to go.

As these are hosted on a managed environment where I cannot add/remove databases from the AG myself I've created a ticket to ask for this particular database to be removed from it and then added again. However I'm not sure if this;

a) will fix the problem
b) will not just be a temp fix (truncate once, then slowly start growing again)

Does anyone here have any suggestions as to what to look at?

Extra info; SQL Server 2016, Running on Windows Server


texts: Displaying Distinct Max Value autonumber rows from tables in MySql

texts:
i am trying to execute the distinct records from mysql table having max value of it sno column. I have tried the following query but having no luck.

SELECT DISTINCT p.sno AS sno, p.short_order,p.fir_sno
FROM pre_trials p, fir f
WHERE p.fir_sno = f.sno
GROUP BY p.fir_sno
ORDER BY p.sno ASC

For the above query i have the following table:

It should return sno 4,5 because both having the maximum value of sno column.
I don't know how to retrieve the record according to my requirements?
Any help will be appreciated. thanks

EDIT: Show Create Table fir,pre_trials


texts:
I have a table for email messages. Then, I have a table that has the message parts.

The Parts table contains a

   field_id,  message_id, and data

At the time I used Parts to name the table and yet used field_id for the column. Just an FYI

So for example, a part_id of 2 would be the subject of the message.
I have parts for subject, date, htmlbody, and textbody. Due to this structure, I have approximately 2 more queries per email (one for the parts, and another for the email addresses associated to the email) than if I were to push all the data into the messages tables. I found this structure to be best, but I'm beginning to think it might be wrong and not best for performance.

My question is, will it be in my best interests to restructure the database? Id rather not.

I was thinking about moving the htmlbody and textbody and subject and date to the messages table. Another solution would be to grab all the emails and their data from the Parts table in one query. I could grab all the ids in one query and then do an IN(ids) for the second query.

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `account_folder_id` int(10) NOT NULL,
  `hash` varchar(255) NOT NULL,
  `uid` int(10) NOT NULL,
  `seen` tinyint(1) NOT NULL,
  `flagged` tinyint(1) NOT NULL,
  `date_created` int(11) NOT NULL DEFAULT '0',
  `last_modified` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash` (`hash`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `message_parts_data` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `message_id` int(10) NOT NULL,
  `field_id` int(10) NOT NULL,
  `data` text NOT NULL,
  `date_created` int(11) NOT NULL DEFAULT '0',
  `last_modified` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `fields` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

INSERT INTO `fields` (`id`, `name`) VALUES
(1, 'To'),
(2, 'Subject'),
(3, 'Date'),
(4, 'From'),
(5, 'CC'),
(7, 'ReplyTo'),
(8, 'textHtml'),
(9, 'textPlain'),
(11, 'Forward');

Thanks


texts: How does subpartitioning actually work and what physical files are created?

texts:
I can't understand how the subpartitioning works in mysql.
I am reading the following example:

create table invoices (
customer_id int not null,
product_id int not null,
amount decimal (8,2) not null,
entry_date date not null default '2006-01-01',
store_id int not null
)
partition by range (year(entry_date))
subpartition by hash(customer_id)
subpartitions 4 (
partition p0 values less than (2008),
partition p1 values less than (2009),
partition p2 values less than (2010),
partition p3 values less than (2011),
partition plast values less than MAXVALUE
);

I can not understand or visualize how this is formed in the HD.
I think that 4 * 5 = 20 physical files are created. Is this correct?
And what data are stored together? Is there a "clustering" of 2008 dates for all customers with the same hash? I am confused on that. Also I am confused how the data are fetched.
Is the customer_id hash used first to determine the range partition or the opposite. What are the exact steps to fetch the data?

UPDATE
Another example. I did the following:

mysql> create table nums_composite (
    -> id int, happened datetime not null,
    -> primary key (id, happened)
    -> ) engine=InnoDB
    -> partition by range (hour(happened))
    -> subpartition by hash(id) subpartitions 2
    -> (
    -> partition p0 values less than (10),
    -> partition p1 values less than (20),
    -> partition p2 values less than MAXVALUE
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> select partition_name, subpartition_name as sub, partition_method as method,   partition_description ,table_rows from information_schema.partitions where table_name='nums_composite';
+----------------+-------+--------+-----------------------+------------+
| partition_name | sub   | method | partition_description | table_rows |
+----------------+-------+--------+-----------------------+------------+
| p0             | p0sp0 | RANGE  | 10                    |         17 |
| p0             | p0sp1 | RANGE  | 10                    |         24 |
| p1             | p1sp0 | RANGE  | 20                    |         20 |
| p1             | p1sp1 | RANGE  | 20                    |         17 |
| p2             | p2sp0 | RANGE  | MAXVALUE              |         13 |
| p2             | p2sp1 | RANGE  | MAXVALUE              |          9 |
+----------------+-------+--------+-----------------------+------------+
6 rows in set (0.00 sec)

It seems that I am right it creates hash_no* subpartition files but where are they?

root@jim-Linux:/# find . -iname "*_composite*"
./var/lib/mysql/partioning_chapter_test/nums_composite.frm
./var/lib/mysql/partioning_chapter_test/nums_composite.par

Why can't I see the actual files?


texts: Get ALL information about spatial index using SQL, including CELLS_PER_OBJECT and USING GEOGRAPHY_AUTO_GRID

texts:
When I create a spatial index I can specify some extra parameters, e.g.
CREATE TABLE dbo.Test(
    Id bigint not null,
    Polygon geography not null
)
GO

CREATE SPATIAL INDEX IX_Test_Polygon ON dbo.Test
(
    Polygon
)
USING GEOGRAPHY_AUTO_GRID
WITH (CELLS_PER_OBJECT = 12)
GO

If I then go to SSMS and script this index, then the script will contain these USING GEOGRAPHY_AUTO_GRID and WITH (CELLS_PER_OBJECT = 12). However, the view sys.indexes does not contain this information.
How can I get this information using SQL, not SSMS.


texts:
I have read many articles now about natural vs surrogate primary keys, and came to the conclusion there is no single best practice.

I have a table that will have around 2000 definite unique values, each value will range from 5 characters to 40 in length.

This seems like a partial choice as a natural key, although the values which are 40 characters in length may cause some performance and storage issues when they are referenced elsewhere.

As the total maximum rows in this table is fixed as 2000 and 35% of these rows contain value length of 25-40 characters(65% have length 6-25), shall I go with a natural key here?

With your experience, what would you do here?


texts: The server principal "" is not able to access the database "" under the current security context

texts:
When I try to install a new database, I'm getting this error from SQL Server 2008.

The server principal "<mydbuser>" is not able to access the database "<mydatabase>" under the current security context.

I looked it up and it says it has something to do with orphaned users when doing a restore.  This connection has nothing to do with a restore, it's a brand new database, user and connection, so why am I getting this problem?

Also the process I'm using to create the database and user/login, is the same for all of my databases, so why does this occur sometimes?

Any ideas?

Thanks.

EDIT: per request here's some code that fails, but understand that this works with every other application I've built.  Also this is built into an ASP.NET MVC application.

System.Web.Management.SqlServices.Install(
    config.Database.DataSource.Trim(),
    config.Database.UserName,
    config.Database.Password,
    config.Database.InitialCatalog,
    System.Web.Management.SqlFeatures.All);

if (!Roles.RoleExists(role.Name))
    Roles.CreateRole(role.Name);  <-- this fails

Oh, another thing that doesn't make sense is that the database in the error message is not the database I'm trying to install to.  It is one of the databases in this SQL Server instance (along with a bunch of others) but it's completely different.  I can't figure out where it's getting this.

Example: the database I'm trying to install to is 'Rapper' and the database shown in the error message is 'GPS'.  Why?


texts:
i've realized that one of my mysql slaves has missing rows.
select count on specific table on master:
9605010
on slave:
9593831

slave is completely sync,
no seconds_behind_master,
no errors,
no slow log

version on both master and slaves:
mysql  Ver 14.14 Distrib 5.6.35-80.0, for debian-linux-gnu (x86_64) using  6.3

sync_binlog=1,
row-based-replication

i'm not sure about using pt-table-checksum because, this is a prod system.
What could be cause the data drift on rbr anyway?


texts: How does one create a Matrix Report to convert sql columns as rows?

texts:
I'm trying to create a product listing. Naturally Sql prints the data vertically forming rows after rows like this:

Rowa. product_img#1|Product_name|Product_price|Product_short_description
Rowb. product_img#2|Product_name|Product_price|Product_short_description
Rowc. product_img#3|Product_name|Product_price|Product_short_description
Rowd. product_img#4|Product_name|Product_price|Product_short_description

and so on...

I'm trying to create a query so that I can display 3 products per rows like this:

Product_img#1|             Product_img#2|            Product_img#3|
Product_name|              Product_name|             Product_name|
Product_price|             Product_price|            Product_price|
Product_description|       Product_description|      Product_description|

Looking up the web I found that what i'm trying to do is called pivot table. It was suggested to me to use a service report like SQL Server Reporting Services and create a Matrix Report to print the data the way I want it.

To be honest, I have no idea how to use those services and where to start... This is totally gibberish to me. I'm using Netbeans joined with Mysql. Where do I install those services and how do I use them?

Is there such kind of service integrated in Workbench? Any help/tricks/tips on how to achieve this would be gladly appreciated!


texts: Does the Inserted table contain Identity values?

texts:
I have an issue with this AFTER INSERT trigger that I'm using for auditing:

  Cannot insert the value NULL into column 'TermID', table
  'AuditTerms'; column does not allow nulls. INSERT fails.

TermKeys table definition:

TermID int Identity PK
Key    int
Action int

AuditTerms table definition:

TermID     int PK
UpdatedAt  datetime

Trigger definition:

ALTER TRIGGER [dbo].[TRG_TermKeys_AuditTerms_IUD] ON [dbo].[TermKeys]
AFTER INSERT, UPDATE, DELETE NOT FOR REPLICATION AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @termid int;
  IF EXISTS(SELECT * FROM Deleted) SELECT @termid = TermID FROM Deleted;
  IF EXISTS(SELECT * FROM Inserted) SELECT @termid = TermID FROM Inserted;
  IF EXISTS(SELECT TermID FROM AuditTerms WHERE (TermID = @termid))
  BEGIN
    UPDATE AuditTerms SET UpdatedAt = getdate() WHERE (TermID = @termid);
  END
  ELSE
  BEGIN
    INSERT INTO AuditTerms (TermID, UpdatedAt) VALUES (@termid, getdate());
  END;
END;

TermID is an Identity field and the PK, does the Inserted table contain the Identity value at this point?
Should I be using @@Identity or some other way?


texts: Overcome MERGE JOIN(INDEX SCAN) with explicit single KEY value on a FOREIGN KEY

texts:
Added 7/11 The issue is deadlocks occur because of index scan during MERGE JOIN. In this case a transaction trying to get S lock on the whole index at the FK parent table, but previously another transaction puts X lock on a key value of the index.

Let me start with a small example (TSQL2012 DB from 70-461 cource used):

CREATE TABLE [Sales].[Orders](
[orderid] [int] IDENTITY(1,1) NOT NULL,
[custid] [int] NULL,
[empid] [int] NOT NULL,
[shipperid] [int] NOT NULL,
... )

Columns [custid], [empid], [shipperid] are corelated parameters for [Sales].[Customers], [HR].[Employees], [Sales].[Shippers] accordingly. In each case we have a clustered index on a referred column in a parrent table.

ALTER TABLE [Sales].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([custid]) REFERENCES [Sales].[Customers] ([custid])
ALTER TABLE [Sales].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Employees] FOREIGN KEY([empid]) REFERENCES [HR].[Employees] ([empid])
ALTER TABLE [Sales].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY([shipperid])REFERENCES [Sales].[Shippers] ([shipperid])

I am trying to INSERT [Sales].[Orders] SELECT ... FROM another table called [Sales].[OrdersCache] which has the same structure as the [Sales].[Orders] except foreign keys.
Another thing might be important to mention the table [Sales].[OrdersCache] is a clustered index.

CREATE CLUSTERED INDEX idx_c_OrdersCache ON Sales.OrdersCache ( custid, empid )

As expected when I`m trying to insert a low volumes of data LOOP JOIN works fine making index seek on the foreign keys.

With high volumes of data MERGE JOIN is used by query optimizer as a most efficient way to maintain foregn key in the query.

And there is nothing to do with it exept using OPTION (LOOP JOIN) in our case with foreign keys or INNER LOOP JOIN in explicit JOIN case.

Below is the query I`m trying to run in my environment:

INSERT Sales.Orders (
        custid, empid, shipperid, ... )
SELECT  custid, empid, 2, ...
FROM Sales.OrdersCache

Looking at the plan we can see that all 3 foreing keys validated with MERGE JOIN. It`s not an appropriate way for me since it uses INDEX SCAN with whole index locking.

Using OPTION (LOOP JOIN) isn`t suitable since it costs almost 15% more than MERGE JOIN (I think the regression will be greater with data volumes growing).

In the SELECT statement you can see single value for shipperid attribute  for the entire inserted set. In my opinion there must be a way to make the validation phase for the inserted set faster at least for the immutable attribute.
Something like:

make LOOP JOIN,MERGE JOIN,HASH JOIN if we have undefined subset for
JOIN validation
if there is just a single explicit value of the validated column we make the validation only once (INDEX SEEK).

Is there any common pattern to overpass the situation above using code structures, additional DDL objects, etc?

Added 20/07. Solution. Query Optimizer already makes a 'single key - foreign key' validation optimization by using MERGE JOIN. And makes only for Sales.Shippers table, leaving LOOP JOIN for another joins in the query at the same time. Since I have a few rows in the parent table Query Optimizer uses Sort-merge join algorithm and compare each row in the inner table with parent table only once. So that is the answer on my question if there is any particular mechanism to effectively process single values in a set during single key validation. That is not so perfect decision but that`s the way SQL Server optimizes the case.

Performance affect investigation revealed that in my case MERGE JOIN and LOOP JOIN insert statement became approximately equal with 750 simultaneously inserted rows with the following superiority of the MERGE JOIN (in CPU time resource).
So using OPTION (LOOP JOIN) is an appropriate solution for my business process.


texts:
I'm loading the panama papers into neo4j using this tutorial,and the last step, loading the edges, has been running for 12 hours. This is the import statement.

USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM 'file:/path/all_edges.csv' AS csvLine
MATCH (n1 { id: toInt(csvLine.node_1)}),(n2 { id: toInt(csvLine.node_2)})
CREATE (n1)-[:ACCOC {role: csvLine.rel_type}]->(n2)

The edges file is 89MB. Is this normal, or how can I check on the status of this process?


texts: is shared storage safe in Postgres if all servers are read-only?

texts:
Description of shared storage in Postgres: https://wiki.postgresql.org/wiki/Shared_Storage

Related question: https://serverfault.com/a/620315/137464

Both of these make it clear that it's not safe to have one write instance and many readers.  What about if all instances are read-only?


texts: Merge Replication: Where are rowguids of conflict losers stored?

texts:
I'm  trying to obtain the row guid of all the merge replication conflict losers listed in my conflict viewer.  The bug that was allowing the conflicts to occur has been identified and resolved but now I need to go and remove the conflict losers from the subscribers so that they quit trying to upload the rows in conflict with each new sync.

I can go through each conflict individually to obtain the rowguid but there are a couple thousand of them.  So, surely there's got to be a query I can write to obtain this data in one swoop.

Anyone have experience querying the merge replication system tables for conflict info or know which system table I should be looking in.  I'm not finding much help out on the interwebs.


texts: How to Retrieve Credit Debit and Balance From More Than One Table in SQL Server

texts:
I have more than 1 table such as INVOICE, PAYMENT, RETURN_INVOICE, RETURN_PAYMENT, etc. i want to retrieve  data CREDIT, DEBIT AND BALANCE each customer wise

below are tables and insert query statements,

CREATE TABLE Customer
(
    CustomerId INT IDENTITY(1,1),
    RegistrationDate DATE,
    CustomerName VARCHAR(45),
    OpeningBalance MONEY,
    PRIMARY KEY(CustomerId)
)
INSERT INTO Customer (CustomerName, RegistrationDate, OpeningBalance) VAlUES ('JOHN', '2020-01-15', 1000)

CREATE TABLE Invoice
(
    InvoiceId INT IDENTITY(1,1),
    InvoiceDate DATE,
    CustomerId INT,
    InvoiceTotal MONEY,
    PRIMARY KEY(InvoiceId)
)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-16', 1, 2000)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-17', 1, 500)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-17', 1, 250)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-20', 1, 1000)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-22', 1, 2250)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-24', 1, 1750)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-28', 1, 3000)

CREATE TABLE Payment
(
    PaymentId INT IDENTITY(1,1),
    PaymentDate DATE,
    CustomerId INT,
    PaymentTotal MONEY,
    PRIMARY KEY(PaymentId)
)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-18', 1, 750)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-20', 1, 2000)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-23', 1, 5000)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-26', 1, 200)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-28', 1, 500)

CREATE TABLE ReturnInvoice
(
    ReturnInvoiceId INT IDENTITY(1,1),
    ReturnInvoiceDate DATE,
    CustomerId INT,
    ReturnInvoiceTotal MONEY,
    PRIMARY KEY(ReturnInvoiceId)
)
INSERT INTO ReturnInvoice (ReturnInvoiceDate, CustomerId, ReturnInvoiceTotal) VAlUES ('2020-01-25', 1, 500)
INSERT INTO ReturnInvoice (ReturnInvoiceDate, CustomerId, ReturnInvoiceTotal) VAlUES ('2020-01-28', 1, 300)
INSERT INTO ReturnInvoice (ReturnInvoiceDate, CustomerId, ReturnInvoiceTotal) VAlUES ('2020-01-29', 1, 1000)

CREATE TABLE ReturnPayment
(
    ReturnPaymentId INT IDENTITY(1,1),
    ReturnPaymentDate DATE,
    CustomerId INT,
    ReturnPaymentTotal MONEY,
    PRIMARY KEY(ReturnPaymentId)
)
INSERT INTO ReturnPayment (ReturnPaymentDate, CustomerId, ReturnPaymentTotal) VAlUES ('2020-01-21', 1, 500)
INSERT INTO ReturnPayment (ReturnPaymentDate, CustomerId, ReturnPaymentTotal) VAlUES ('2020-01-27', 1, 2000)

I want to retrieve the output like this,

All the table should be inner join to customer (CustomerId Is Foreign Key)
Need to retrieve extra column Description and mention the table transaction name and No. (3rd column in the picture above)
Customer OPENING BALANCE should come on top Row that is from Customer Table
Ordered by date and its no under it.

How to get the Expected output on above picture.


texts:
I have a table that holds JSON data. In a query with a GROUP BY clause, I'd like to get an array of all of the JSON field names in the result set.

I tried a query like this:

SELECT array_agg(jsonb_object_keys(data))
FROM table
GROUP BY some_id
WHERE some_id = 3

For an input data like

some_id | data
--------|---------------
3       | {"foo": "bar"}
4       | {"baz": 3}
3       | {"bar": 4}

I'd like to receive:

array_agg
--------------
{'foo', 'bar'}

But it returns an error: ERROR:  set-valued function called in context that cannot accept a set

It seems like I need to somehow convert setof text, which is what jsonb_object_keys returns, into array but I don't know how.


texts: Query slowness after migration from sql server 2000 to 2014

texts:
I'm not a DBA. I have two servers. One of them runs on sql server 2000, the other on sql server 2014. Both have almost identical hardware (2014 has more RAM). My job is to migrate all databases and activity from the first server to the second one.

To achieve this I made database backups on SQL 2000 and restored them on a server running on SQL 2008. Then made another set of backups and restored them on the 2014 server. You can't restore 2000 backups on a 2014 server - you need an intermediary - in my case it was 2008. Anyway, now that I have all the databases on the 2014 server I began testing some stored procedures and queries. Some queries run as good as on the 2000 server. Unfortunately, there are some that take ages - what takes about 20 seconds on the SQL 2000 server, takes a couple of hours on the 2014 server which is not acceptable.
I made several steps to improve the performance:

I rebuilt/reorganized indexes
I ran Update Statistics table_name with FULLSCAN
I ran sp_updatestats
I ran DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS
I altered between database compatibility levels 2014, 2012 and 2008 (2005 displays an error and there is no option to select 2000)

Nothing worked. Am I missing something? Optimizing the problematic queries is not a real option - there are too many of them - the problem needs to be addressed globally.

Example query which works fine:

select
MKPF.MANDT,
MKPF.MBLNR,
MKPF.MJAHR,
MSEG.BWART,
MSEG.MATNR,
MSEG.LGORT,
MKPF.BUDAT,
MKPF.CPUDT,
MKPF.CPUTM,
MKPF.AEDAT,
MSEG.SHKZG

from PI2.PKT.pkt.MSEG AS MSEG
join PI2.PKT.pkt.MKPF AS MKPF
on MKPF.mandt=MSEG.mandt and MKPF.MJAHR = MSEG.MJAHR and MKPF.MBLNR = MSEG.MBLNR
join PI2.PKT.pkt.MARA MARA
on MARA.MANDT=MSEG.MANDT and MARA.MATNR = MSEG.MATNR
and MARA.spart ='01'

However, when I add a WHERE to the query it takes a couple of hours to finish instead of 20 seconds like on SQL Server 2000:

select
MKPF.MANDT,
MKPF.MBLNR,
MKPF.MJAHR,
MSEG.BWART,
MSEG.MATNR,
MSEG.LGORT,
MKPF.BUDAT,
MKPF.CPUDT,
MKPF.CPUTM,
MKPF.AEDAT,
MSEG.SHKZG

from PI2.PKT.pkt.MSEG AS MSEG
join PI2.PKT.pkt.MKPF AS MKPF
on MKPF.mandt=MSEG.mandt and MKPF.MJAHR = MSEG.MJAHR and MKPF.MBLNR = MSEG.MBLNR
join PI2.PKT.pkt.MARA MARA
on MARA.MANDT=MSEG.MANDT and MARA.MATNR = MSEG.MATNR
and MARA.spart ='01'
WHERE
MSEG.Mandt='500' and MSEG.MATNR like '00000000%' and
MKPF.CPUDT between @datefrom and @dateto
AND MSEG.BWART in ('101','561')

FYI @datefrom and @dateto are defined beforehand (I'm sure they don't affect the preformance). PI2 is a linked server. The linked server has the exact same properties on 2014 as it does on 2000.

Any ideas on what I should do? At this point I'm not sure whether it's an issue with the SQL Server 2014 configuration or maybe there's an issue with the linked server (maybe the linked server had a better network connection with the old sql server 2000? )


texts: MySQL: Create multiple tables with one query

texts:
Can you list multiple table names in a MySQL CREATE TABLE query?

CREATE TABLE table1, table2, table3 LIKE table_template;

If so, can you do it with the IF NOT EXISTS clause?

CREATE TABLE IF NOT EXISTS table1, table2, table3 LIKE table_template;

I know you can do this with DROP TABLE:

DROP TABLE IF EXISTS table1, table2, table3;

The docs for DROP TABLE explicitly show that you enter a string of names:

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

The docs for CREATE TABLE don't show a table name string:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

More Syntax Variations ...

Can anyone point me to a reference that states explicitly that multiple tables can or can't be created with one query?

I searched the CREATE TABLE Syntax doc for "string", "tbl_name", and "names", but no luck.


texts: log_reuse_wait_desc says ACTIVE TRANSACTION but DBCC OPENTRAN reports nothing

texts:
I'm using AdventureWorks database in Simple Recovery Model on SQL Server 2016 Ent, and I'm examining the active VLFs.

Running select * from sys.databases shows ACTIVE_TRANSACTION under log_reuse_wait_desc

but when I run dbcc opentran to figure out which session, i get "No Active Open Transactions"

There does seem to be a background task running, which prevents me from taking the database offline

  Status           DBName           Command
BACKGROUND    AdventureWorks     XTP_OFFLINE_CKPT

There is also a full-text catalog (which gets crawled regularly I guess?)

How do I find out what transaction is holding up my log VLFs ?


texts: MongoDb linearizable read concern restrictions

texts:
Could someone explain me some part of Mongodb linearizable read concern documentation:

  Linearizable read concern guarantees only apply if read operations specify a query filter that uniquely identifies a single document.

Is it mean that i have to have unique index on fields that presented in query filter?

For example let's answer on 4 questions:

I have collection test without unique index on A field. db.test.find({A:"someValue"}).readConcern("linearizable").maxTimeMS(10000)

Is it linearizable and i can't get stale read? If answer yes, is it mean that there no reason to use linearizable read concern in reads by fileds which not presented in unique index?
I have collection test with unique index on A field.
db.test.ensureIndex({A:1}, {unique:true});
db.test.find({A:"someValue"}).readConcern("linearizable").maxTimeMS(10000);

Is it linearizable and i can't get stale read?
I have collection test with unique index on A field.
db.test.ensureIndex({A:1}, {unique:true});
db.test.find({A:"someValue", B:"someValue2"})
       .readConcern("linearizable").maxTimeMS(10000);

Is it linearizable and i can't get stale read?
I have collection test without unique index on A field. But find method return only one document in result.
db.test.find({A:"someValue"}).readConcern("linearizable").maxTimeMS(10000); //returned {_id:"someId", A:"someValue"}

Is it linearizable and i can't get stale read?


texts: Sqlite, DB clients & BLOB's: Practical DB Use

texts:
I'm looking for an SQLite client that can help me to make practical use of the DB. The plan is to insert mp3's as BLOBs (or perhaps just as references to external files).  Each record will also include transcripts/notes/tags.

I'd like to be able to access this DB in such a way that I can click on the mp3 and listen to it, then add text data in the other fields, and finally search on text in the DB. LibreOffice Base is a nice client that allows me to define forms, but I'm not clear if simple access to the mp3 is possible. Are there other options?

I should say up front that I can do bash scripting and C, but I don't have python.


texts: Use MySQL to regularly do multi-way joins on 100+ GB tables?

texts:
Background:
I’ve created a web application that I would like to be able to scale reasonably well. I know I'm not Google or Twitter, but my app uses a fairly large amount of data for each user and thus has fairly high data requirements. I want to be ready to scale reasonably well without having to re-architect everything later.

I consider myself a software developer, not a database expert. That’s why I am posting here. Hopefully someone with a lot more database expertise can give me advice.

With a relatively large number of users, but nothing like Facebook numbers, I expect to have a DB that looks like this:

One "Big table":

250 million records
20 columns
Approximately 100 GB of data
Has an indexed bigint(20) foreign key
Has an indexed varchar(500) string_id column
Has an int(11) "value" column

4 other tables:

10 million records each
Approximately 2 - 4 GB of data each
each of these tables has 4 - 8 columns
one column is datetime date_created
one column is the varchar(500) string_id column
one or two columns from each of these tables will be selected in a join

One of these tables is used for storing averages -- its schema is bigint(20) id, varchar(20) string_id, datetime date_created, float average_value

What I want to do -- two relatively expensive queries:

Calculate new average values:

Using a foreign key, select up to several million separate records from the big table.
Calculate a new average, grouping by the string_id .
Insert results into the averages table.
As currently constructed, this query uses two joins.

Create de-normalized, read-only records for serving users:

Use a foreign key to select anywhere from 1,000-40,000 records from the big table.
Join with each of the other four tables on the newest record with the string id column.
Insert the results into a de-normalized table.
These records are for use by the front-end to display information to users.
As currently constructed, this query uses four joins.

I plan to run each of these expensive queries on a batch back-end database that will push its results to a real-time front-end DB server which handles requests from users.
These queries will be run at regular intervals. I haven't decided how often. The average query could be done perhaps once per day. The de-normalize query will need to be more frequent -- perhaps every few minutes.

Each of these queries currently runs in a few seconds in MySQL on a very low-end machine with a dataset with 100K records in the “big table.” I am concerned about both my ability to scale and the costs of scaling.

Questions:

Does this approach seem sound? Is there anything obviously wrong with it from a big-picture perspective?
Is a RDBMS the right tool, or should I look at other "big data" solutions like something in the Hadoop family? My inclination is to use a RDBMS because the data is structured and fits nicely into the relational model. At a certain point though, it is my understanding that I may no longer be able to use a RDBMS. Is that true? When would this switch be needed?
Will it work? Can these queries be run in a reasonable amount of time? I can wait perhaps hours for query #1, but query #2 should finish in minutes.
What should I consider from a hardware perspective? What are my RAM and CPU bottlenecks likely to be? I assume keeping indexes in RAM is important. Is there anything else I should consider?
At some point I will probably have to partition my data and use multiple servers. Does my use case seem like it is already in that category, or will I be able to scale a single machine vertically for a while? Will this work with 10x the data? 100x?


texts: How does SSIS Balanced Data Distributor decide to split records?

texts:
I have been using the balanced data distributor control in SSIS, but am coming across an issue in which my parallel destinations (Salesforce bulk api via TaskFactory) are getting in lock conflicts.  I was told by Salesforce support to try sorting such that there are no conflicting locks in separate batches.

How does the BDD control distribute?  Does it split using fetch/limit, or does it do a modulus split across all records?


texts: When should a primary key be declared non-clustered?

texts:
While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED

When would you use a NONCLUSTERED primary key as opposed to a CLUSTERED primary key?

Thanks in advance


texts: Design problem with placing proper constrains

texts:
I have been trying to solve this problem but in every solution there seems to be something missing. So we have organization for collecting donations from donors for people that need help.

Member can create many action for collecting donations and people in need can have many actions for them. So we have following entities : donors, donations, members, people_in_need.

Now the key thing is that donor can donate only once for a particular action. That means I cant have action table with 3 (pk/fk) keys for member, people_in_need, donation. It seems that I am focusing on creating this middle table with 3 keys and I can't think of solution because of it.

Any ideas? My design so far is really simple at the moment:

Donor (DonorID, Name, Age)

Donations (DonationID, Amount)

Member (MemberID, Name)

PeopleInNeed (PeopleID, Name)

My best design was to create a relationship between Donor and Donation where I have Donor as FK in Donation , and then I created 'middle' table Action where I have composite key (MemberID, DonationID, PeopleID, Date). Now problem is that same donor can place more than one donation in Action and that is really the only problem I am facing.

Edit 2:

Actually, one particular action may be related to only one people in need. I meant that one people in need can have more that one different actions for them. That is way I put both MemberID and PeopleID as PK's in Action table.


texts:
I have this SQL:

CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB);

INSERT INTO test(data) VALUES
   ('{"parent":null,"children":[2,3]}'),
   ('{"parent":1,   "children":[4,5]}'),
   ('{"parent":1,   "children":[]}'),
   ('{"parent":2,   "children":[]}'),
   ('{"parent":2,   "children":[]}');

That would give:

 id |                 data
----+--------------------------------------
  1 | {"parent": null, "children": [2, 3]}
  2 | {"parent": 1, "children": [4, 5]}
  3 | {"parent": 1, "children": []}
  4 | {"parent": 2, "children": []}
  5 | {"parent": 2, "children": []}

When doing normal one to many, it would show something like this:

SELECT *
FROM test x1
  LEFT JOIN test x2
    ON x1.id = (x2.data->>'parent')::INT;
 id |                 data                 | id |               data
----+--------------------------------------+----+-----------------------------------
  1 | {"parent": null, "children": [2, 3]} |  2 | {"parent": 1, "children": [4, 5]}
  1 | {"parent": null, "children": [2, 3]} |  3 | {"parent": 1, "children": []}
  2 | {"parent": 1, "children": [4, 5]}    |  4 | {"parent": 2, "children": []}
  2 | {"parent": 1, "children": [4, 5]}    |  5 | {"parent": 2, "children": []}
  5 | {"parent": 2, "children": []}        |    |
  4 | {"parent": 2, "children": []}        |    |
  3 | {"parent": 1, "children": []}        |    |

How to join based on children (using LEFT JOIN or WHERE IN)? I've tried:

SELECT data->>'children' FROM test;
 ?column?
----------
 [2, 3]
 [4, 5]
 []
 []
 []

SELECT json_array_elements((data->>'children')::TEXT) FROM t...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT json_array_elements((data->>'children')::JSONB) FROM ...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT json_to_record((data->>'children')::JSON) FROM test;
ERROR:  function returning record called in context that cannot accept type record
HINT:  Try calling the function in the FROM clause using a column definition list.

SELECT * FROM json_to_record((test.data->>'children')::JSON);
ERROR:  missing FROM-clause entry for table "test"
LINE 1: SELECT * FROM json_to_record((test.data->>'children')::JSON)...


texts: Splitting a table into two tables randomly with 50-50 percent of records in impala

texts: I have a table having n number of records in impala. I need to find out how can I divide that table into two equal halves with 50 - 50 percent of records in each.


texts: How to remove the duplicate records from primary filegroup after partitioning?

texts:
Basically, our database is created based on primary filegroup alone.

Now, I have done partitioning for a large table which was approx 25 GB. The partitioning was done on secondary filegroups which has data files located at different drives for performance optimization. Now when I look into the database it looks like that the primary filegroup size have not been decreased.

FYI, I have partitioned an existing table based on index.

It looks like that the data has been copied records from my primary filegroup to secondary filegroups instead of moving the records.

Is there any fix for this or does the partition increases the database size ?

The table size looks to be doubled in size after partition and is that going to enhance performance occupying a lot of memory ?

Can I remove the duplicating records from primary filegroups after it has been moved to secondary filegroups ?


texts:
I am in the process of building a new server to run our SQL Server databases (we have many TB's of data). I am looking for some help on what I should request for hard drives. My thought was to have 5 logical drives:

Drive 1: System drive
Drive 2: Database drive for DB's with a lot of read/writes
Drive 3: Database drive for other DB's and TempDB
Drive 4: Log files
Drive 5: Backups and other storage

Would this be a good setup? What type of disk (SSD or other) and RAID should I use for each drive? Keep in mind I'm trying to get the most bang for the buck. I don't have an unlimited budget, and I want to spend a lot on RAM.


texts: not working alter database open resetlogs;

texts:
RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 11/28/2020 11:16:30
ORA-01666: control file is for a standby database

I got the backup from standby database that configured ASM also.
production database has RAC environment.
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
=============================================================================

However my database status is:
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      CCORA12C         PHYSICAL STANDBY MAXIMUM PERFORMANCE

And I  tried to rename my log file also:
SQL> ALTER DATABASE RENAME FILE '+DATA/CCORASTD/ONLINELOG/group_9.382.963067895  ' to '/u02/oradata/FRA/CCORA12C/onlinelog/group_9.382.96                   3067895';

ALTER DATABASE RENAME FILE '+DATA/CCORASTD/ONLINELOG/group_9.382.963067895  ' to '/u02/oradata/FRA/CCORA12C/onlinelog/group_9.382.9630678                   95'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-15124: ASM file name '+DATA/CCORASTD/ONLINELOG/group_9.382.963067895  '
contains an invalid alias name

I can't even drop logfile group or member:
SQL> ALTER DATABASE DROP LOGFILE MEMBER '+DATA/CCORASTD/ONLINELOG/group_9.382.963067895';

ALTER DATABASE DROP LOGFILE MEMBER '+DATA/CCORASTD/ONLINELOG/group_9.382.963067895'
*
ERROR at line 1:
ORA-00313: open failed for members of log group 9 of thread 1

ORA-00312: online log 9 thread 1:
'+RECO/CCORASTD/ONLINELOG/group_9.2596.963067897'

ORA-17503: ksfdopn:2 Failed to open file
+RECO/CCORASTD/ONLINELOG/group_9.2596.963067897

ORA-15001: diskgroup "RECO" does not exist or is not mounted
ORA-15064: communication failure with ASM instance
ORA-00312: online log 9 thread 1:
'+DATA/CCORASTD/ONLINELOG/group_9.382.963067895'

ORA-17503: ksfdopn:2 Failed to open file
+DATA/CCORASTD/ONLINELOG/group_9.382.963067895

ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15064: communication failure with ASM instance

--------------------------------------------------------------------
SQL> ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 9
*
ERROR at line 1:
ORA-00313: open failed for members of log group 9 of thread 1
ORA-00312: online log 9 thread 1:
'+RECO/CCORASTD/ONLINELOG/group_9.2596.963067897'

ORA-17503: ksfdopn:2 Failed to open file
+RECO/CCORASTD/ONLINELOG/group_9.2596.963067897

ORA-15001: diskgroup "RECO" does not exist or is not mounted
ORA-15064: communication failure with ASM instance
ORA-00312: online log 9 thread 1:
'+DATA/CCORASTD/ONLINELOG/group_9.382.963067895'

ORA-17503: ksfdopn:2 Failed to open file
+DATA/CCORASTD/ONLINELOG/group_9.382.963067895

ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15064: communication failure with ASM instance

any suggestions ??


texts: How can I make my database more concise?

texts:
I am writing an application for Android using SQLite (backed by ORMlite if it matters). So far I'm in the conceptual stage and working on the database design. The database is being designed to contain a collection of assets that are registered to one or more users. I have never worked on any data outside of SQLite and even then I have only made a few very small databases. As such I am not sure that my I handling this design correctly. As I expand on my design, it is beginning to look like a mess. My questions are:

Am I designing this database in too much of an ObjectOriented fashion? As a java programmer, this is just how it makes sense to design the database.
Is there any way to make the database look less like a mess?
Am I even on the correct track?

For reference here is an earlier more easy to read protoype of the database design. No typings have been assigned yet, sorry.

Here is a more updated visualisation of my problem.


texts: What's the cause for an AlwaysOn AG Secondary Replica to have a high Redo Queue Size and Estimated Recovery Time with a good Redo Rate?

texts:
SQL14 is the Primary server, SQL16 is the Secondary Replica, and they're setup using synchronous commit availability mode:

As of yesterday it appeared that data stopped synchronizing from the primary to the replica. This morning we Suspended the Availability Database and then Resumed it. That seemed to kick the synchronization again as I see new data coming through now but the Redo Queue Size and Estimated Recovery Time in the dashboard are still big and are continuing to grow.

What things can I check / do to fix this?

Additional Information:
Server Version: SQL Server 2016 Enterprise - SP1 (on both primary and secondary)

Also, we had some long running index re-organize / re-build jobs fail on the primary earlier in the morning. (That was about 4 hours ago but could that still be potentially a contributor to this issue now?)

I noticed the following server logs on the secondary (from oldest to newest):

Not sure if there's any clues in there?

DBCC OPENTRAN on the Secondary returns the following:

Extended events session to track waits on the Secondary:


texts: Can we copy data from 1 text file to 2 postgresql tables?

texts: I have a text file with some data and I have two similar tables in my postgresql database. Is it possible to copy a part of the data present in the file, say first 10 lines, to the first table and the remaining in the second table?


texts: Oracle Secure Backup (OSB) issue - "failed to read configuration data - Network is unreachable"

texts:
I am using Oracle 12.2.0.1 on Linux 6.8. I am using OSB to take a backup to tape.
After an unknown host name error in the backup log, I edited the /etc/hosts file and restarted the server.

After restarting, when I enter obtool in the DB server (which is a client) it says

  failed to read configuration data - Network is unreachable

Can anyone please throw me some light on this issue?


texts: Design consideration regarding state handling: how to store multiple, variable states for one entity

texts:
First I have to admit that I'm not a database professional neither are my colleagues. For a new project my colleagues and me came to a design question we couldn't really solve easily. And all the ideas had some disadvantages, so we could't figure out what's the best way to go.
We have a main entity "Transaction" which should be processed by "ProcessingRules". The processing rules can be configured by the users in the Web application (each rule has a different execution scheduling. One might be running every hour, whereas the others might run nightly).
Lets say Transaction gets 10'000 new records a day.
This would lead to a DB design where I need to keep the State "Processed YES/NO" for each "ProcessingRule" and "Transaction".
I thought the proper way is to have a relation table between the "ProcessingRoles" and the "Transaction". If no record present, the record has not been processed by this role yet.
Transaction [0..1] ------ [*] TransactionRuleProcessing [*] ------- [0..1] ProcessingRule
But when I think of the Query, this would lead into a WHERE NOT EXISTS (SELECT 1 FROM TransactionRuleProcessing...) query for the rule to identify new or unprocessed records.
If we have a large amount of rows in Transaction, I think this will affect performance because the NOT EXISTS will have to join the whole table to the state table. If I'm not mistaken, this might cause a performance issue.
On the other side, if we had only one state directly on the Transaction table, we could add an index and there would be no join between the large Transaction table and the state table.
Question:
Is it true that such a NOT EXISTS query would have to join the whole Transaction table with the TransactionRuleProcessing table to identify non existing (processed) rows? How could this affect performance of the database with a large Transaction table? What would be other recommendations to flag a record by a various amount of states?
Any ideas very much appreciated


texts: Will adding features pick up previously installed service pack updates?

texts: We have SQL Server 2008 R2 SP3 installed. Need to add a feature to the installation, Business Intelligence Development Studio. The question is do features installed after the application of a service pack pick up that service pack? If not does the service pack have to be installed again?


texts: Database 'msdb' cannot be opened. it has been marked suspect by recovery

texts:
I'm using SQL Server 2008. I got suddenly today an error while i was accessing a database from Visual Studio. The error is:

  Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. Error: 926

I don't know what to do to get out from this type of error.


texts: Managing failover for MySQL nodes using HA Proxy

texts:
We have an S1<-M1<->M2->S2 setup of MySQL replicated nodes. These are now to be brought to the back-end of an HA Proxy server to split read from writes. We also intend to achieve automatic fail-over with this. However, write requests to be routed to M2 only when M1 fails. In usual scenario, we will be good with not touching M2 at all. There seems to be no "balance" option in HA Proxy that switches to M2 only when M1 fails.
Please suggest how this can be achieved using HA Proxy.

Writing round-robin across M1 and M2 is a time consuming solution to be taken up at this point in time.


texts: How to reduce foreign key performance impact in PostgreSQL

texts:
Foreign keys add significant overhead...more than +600% in the example below (Docker PostgreSQL 11.4):

CREATE TABLE example_1 (a int PRIMARY KEY);
CREATE TABLE example_2 (a int NOT NULL REFERENCES example_1 (a));
CREATE TABLE example_3 (a int NOT NULL);

INSERT INTO example_1 SELECT generate_series(1, 1000);

-- with FK
INSERT INTO example_2 SELECT g FROM generate_series(1, 1000) g, generate_series(1, 10000);
Time: 91301.783 ms (01:31.302)

-- without FK
INSERT INTO example_3 SELECT g FROM generate_series(1, 1000) g, generate_series(1, 10000);
Time: 12395.756 ms (00:12.396)

Any ways to reduce this high FK overhead?


texts: SSIS Control Flow: Precedence constraints not working as expected

texts:
The control flow of my SSIS package is not behaving as expected.

What I want to achieve:

Test if CLR is enabled on SQL Server using

SELECT value
FROM   sys.configurations
WHERE  name = 'clr enabled'

If value == 0, attempt to enable CLR and display message Server is configured.
If value == 1, display message Server is configured.

If I setup my control flow to do only one or the other, it works.

This flow works as expected. However, I would like to, if value == 0, continue to the MessageBox after Enable CLR on Server.

I tried changing the control flow to this

Now, what happens is this:

value == 0 [CLR Enabled on Server] hands control to [Enable CLR on Server], which completes and exits.
value == 1 [CLR Enabled on Server] completes and exits.

[MessageBox- Server is Configured] is never reached.

Can anyone help me understand this, and/or point me to a good resource about SSIS Conditional Control Flow?


texts:
We want to programmatically detect errors in cron scheduled pg_dumps.

Apart from cheking whether or not the log file ends in "pg_dump: saving database definition":

What other tell-tale string can I grep for in order to programatically check a dump is ok.


texts: SQL Server database backup to store only the last 7 backups in a file

texts:
I want to create a scheduled task that will run (every night) a SQL script to fully backup my database X to a file. I want that file to store up to 7 full backups. The way I tried doing this is using the following code:

SELECT @SQL = '
BACKUP DATABASE [X]
TO  DISK = N''\file.bak''
WITH NOFORMAT, NOINIT, NAME = N''name'', RETAINDAYS = 7
'

But this doesn't overwrite/delete full backups contained on that file that are older than seven days.

Any ideas? Suggestions for alternatives?

Thanks


texts: How to copy a subset of SQL objects across databases within a transaction?

texts:
We already have a .dtsx package to copy a subset of tables (about 20) from one database to another (at least once in every month). It is using "Transfer SQL Server Objects Task". However this does not handle transactions See here How this task works is it first truncates all the tables and then inserts data. So if something goes wrong second DB may end up where it has no data at all!

My question is, Is there way that we can introduce transactions or is there a different way of doing this. I'd prefer to stick to .dtsx rather than manual scripts because of the maintenance. But I'd like to consider all the options I have.

Thanks for your help.


texts: Scheduling MySQL Workbench to execute and export on a daily basis

texts:
Here's my question.

I have a couple scripts (very simple stuff; select queries) that I run in MySQL Workbench which I can than export to CSVs. What I want to know is if it is possible to schedule MySQL Workbench to run the scripts and export to CSVs at a specific time everyday.

I am using Windows 7.


texts:
I have server in Kimsufi with 2gb RAM but my server is very slow , with simple hosting in ovh it responds quickly ...

With pingdom.com test i have for
 server: 10 sec
 and simple hosting: 2sec

is there a problem in my mysql configuration ? I dont need change query limit for 2gb (2000M) ?

my.cnf

innodb_buffer_pool_size=2M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

query_cache_limit       = 1M
query_cache_size        = 16M

innodb_buffer_pool_size=2M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M


texts: Real time info charts - Updating via trigger or via schedule

texts:
I have 100K records table that record activities of users. I want to show some charts base on that data value per time unit. So I've created a chart base table.

Chart table have 3 columns

Time unit (primary key) column (1,2,3,4...)
Absolute value per time unit - example: 1, 2, 7, 20 ...
Accumulated value (and growing) per time unit - example: 1, 3, 10 , 30 ...

Which is better?

Update Trigger (when status change) - ... I assume that CPU work light only on specific record on every change to the table.  Accumulative value will be pass to the next time unit by internal select.

OR

Aggregation Updating each time unit (by schedule) - ... I assume that CPU work hard on all records. Accumulative value will be calculated on all records till current time unit.

So which approach is make more sense on real time info charts?


texts: How to reset auto increment for the table with 1000 of records in mysql. I want it to start counting from 1 again.


texts: Upgrading to SQL Server 2012 from SQL Server 2008 R2

texts:
I am planning to upgrade my SQL Server 2008 R2 Standard Edition to SQL Server 2012 Standard Edition.

I had been through many blogs on advantages of 2012 but I didn't find any performance comparison of both the versions.

Are there any performance variations between the two versions?

What impact (if any) will 2008R2 and 2012 have on ability to service larger loads of data?


texts:
I'm having more and more errors with my MySQL (latest 5.1) replication, in a master-slaves (one master, two slaves). A couple of times in the last couple of days, I'm getting duplicate entry, like this:

Error 'Duplicate entry '4818122-74' for key 'PRIMARY'' on query.
Default database: 'xxxxx'.
Query: 'INSERT INTO `xxxxxx` (`xxxx`, `xxxxx`, `xxxx`, `xxxxx`, `xxxx`) VALUES ('4818122', '74', '566.29411764706', '34', '10')'

It happens 99% of the time on the same table. All databases are replicated.

What can cause this?


texts: Where do I find the meaning of TdsInputBufferError numbers?

texts:
We are having some sporadic connection errors on some of our SQL servers. I did some searching and found out about the Ring Buffer in SQL 2008 R2 and found some information, but I am not entirely sure how to figure it out.

Running the query

SELECT  CAST(record AS XML)
FROM    sys.dm_os_ring_buffers
WHERE   ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

Gives me some results, on of which is interesting

<Record id="4" type="RING_BUFFER_CONNECTIVITY" time="461419385">
  <ConnectivityTraceRecord>
    <RecordType>LoginTimers</RecordType>
    <Spid>0</Spid>
    <SniConnectionId>40A403B2-B860-4877-A1D3-1FA7024356B7</SniConnectionId>
    <SniConsumerError>17830</SniConsumerError>
    <SniProvider>4</SniProvider>
    <State>11</State>
    <RemoteHost>&lt;local machine&gt;</RemoteHost>
    <RemotePort>0</RemotePort>
    <LocalHost />
    <LocalPort>0</LocalPort>
    <RecordTime>5/19/2012 16:29:23.831</RecordTime>
    <TdsBuffersInformation>
      <TdsInputBufferError>109</TdsInputBufferError>
      <TdsOutputBufferError>0</TdsOutputBufferError>
      <TdsInputBufferBytes>0</TdsInputBufferBytes>
    </TdsBuffersInformation>
    <LoginTimers>
      <TotalLoginTimeInMilliseconds>18010</TotalLoginTimeInMilliseconds>
      <LoginTaskEnqueuedInMilliseconds>0</LoginTaskEnqueuedInMilliseconds>
      <NetworkWritesInMilliseconds>0</NetworkWritesInMilliseconds>
      <NetworkReadsInMilliseconds>18010</NetworkReadsInMilliseconds>
      <SslProcessingInMilliseconds>0</SslProcessingInMilliseconds>
      <SspiProcessingInMilliseconds>0</SspiProcessingInMilliseconds>
      <LoginTriggerAndResourceGovernorProcessingInMilliseconds>0</LoginTriggerAndResourceGovernorProcessingInMilliseconds>
    </LoginTimers>
  </ConnectivityTraceRecord>
  <Stack>...</Stack>
</Record>

So, my question is, where do I find the meaning for the TDS error numbers. In this case <TdsInputBufferError>109</TdsInputBufferError>


texts:
I have 3 tables: Employees, attendanceIn table, attendanceOut

And My query is

SELECT  employees.eno,employees.ename,employees.dept,attendanceIn.attIn AS attIn,
       attendanceOut.attOutAS attout
    FROM  employees
    LEFT JOIN  attendanceIn  ON employees.eno=attendanceIn.eno
    LEFT JOIN  attendanceOut  ON employees.eno=attendanceOut.eno
    WHERE  DATE(attendanceIn.puchtime) LIKE '2016-07-01%'
      AND  attendanceOut.puchtime LIKE '2016-07-01%'
    GROUP BY  eno
    ORDER BY  eno ASC

Is there any better way to write the query?


texts:
I have this query in MySQL:

SELECT
    p.idSentence,
    p.idPath,
    p.token,
    p.posTag,
    p.ner,
    p.isTV,
    r.relationType,
    t.tag
FROM path p
 LEFT OUTER JOIN relation r ON(p.idTokenN=r.idTokenN)
 JOIN contentDoc t ON p.idContDoc=t.idContDoc
 JOIN (
    SELECT idPath,
           MAX(isTV='true') AS hasTV,
           COUNT(*) AS pathLength
    FROM path
    GROUP BY idPath

  ) pf
 ON p.idPath=pf.idPath
    AND pf.hasTV =1
    AND pf.pathLength BETWEEN 2 AND 5
 JOIN (
     SELECT idSentence,COUNT(DISTINCT(idPath)) AS paths
     FROM path
     GROUP BY idSentence
    ) ps
 ON p.idSentence=ps.idSentence
    AND ps.paths BETWEEN 1 AND 3 ;

Because the data are so many, the query takes a long time to run, how can I optimize it to speed it up?


texts: Why am I getting nondeterministic results with the deterministic function STDEV()?

texts:
This is type of query I'm trying to run:

WITH CTE_Ordered AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY PartitionField ORDER BY DateField) AS PartitionRowId
  FROM SourceTable
),
CTE_Top1_PerPartition AS
(
  SELECT *
  FROM CTE_Ordered
  WHERE PartitionRowId = 1
),
CTE_Calculations AS
(
  SELECT AVG(NumberField1) AS NumberField1_Avg, StdDev.StdDev AS NumberField1_StdDev
  FROM CTE_Top1_PerPartition
  CROSS JOIN
  (
     SELECT STDEV(NumberField1) AS StdDev
     FROM CTE_Top1_PerPartition
  ) AS StdDev
  GROUP BY StdDev.StdDev
)

-- Final Select
SELECT *
FROM CTE_Calculations

Every time I run the final select, my NumberField1_StdDev value changes even though SourceTable is isolated and doesn't change.

I noticed if I select CTE_Top1_PerPartition into a temp table first and then run the rest of the query off of that temp table, then I get the same result for NumberField1_StdDev every time.

I would guess it has something to do with the order the results are returned in the CTE_Top1_PerPartition CTE, but I'm not sure why.


texts: SQL Server 2012 AvailabilityGroups - Preferred Backup Replica

texts:
I currently have a 2 node SQL Server 2012 HA Availability Group setup. Currently we have a number of SharePoint databases that exist in this availability group. I'm attempting to ensure our backups are properly configured to run on the secondary node. I have set the backup priority on node 1 to 50 and node 2 to 100. Then I run the below query to check and see which databases will be backed up on which node:

SELECT d.database_name,
  sys.fn_hadr_backup_is_preferred_replica (d.database_name) AS IsPreferredBackupReplicaNow
FROM sys.availability_databases_cluster d

Node 2 reports all databases have it set to the preferred backup replica. The problem is when I then run the same query on node 1. I see about 10 out of the 80+ databases also have node 1 set as the preferred node.

Node 2

database_name   IsPreferredBackupReplicaNow
WSS_Projects_Content    1
WSS_Teams_Content   1
WSS_Teams2_Content  1
WSS_Teams3_Content  1
WSS_Teams4_Content  1
WSS_Teams5_Content  1
WSS_Teams6_Content  1
WSS_Teams7_Content  1
WSS_Mysites_Content 1
WSS_Mysites10_Content   1
WSS_Mysites11_Content   1
WSS_Mysites12_Content   1
WSS_Mysites13_Content   1
WSS_Mysites14_Content   1
WSS_Mysites15_Content   1
WSS_Mysites2_Content    1
WSS_Mysites3_Content    1
WSS_Mysites4_Content    1
WSS_Mysites5_Content    1
WSS_Mysites6_Content    1
WSS_Mysites7_Content    1
WSS_Mysites8_Content    1
WSS_Mysites9_Content    1
WSS_Orgs_HR_Content 1
WSS_Orgs_its_Content    1
WSS_orgs_MA_Content 1
WSS_orgs_ND_Content 1

Node 1

database_name   IsPreferredBackupReplicaNow
WSS_Projects_Content    1
WSS_Teams_Content   1
WSS_Teams2_Content  1
WSS_Teams3_Content  1
WSS_Teams4_Content  1
WSS_Teams5_Content  1
WSS_Teams6_Content  1
WSS_Teams7_Content  1
WSS_Mysites_Content 1
WSS_Mysites10_Content   1
WSS_Mysites11_Content   0
WSS_Mysites12_Content   0
WSS_Mysites13_Content   0
WSS_Mysites14_Content   0
WSS_Mysites15_Content   0
WSS_Mysites2_Content    0
WSS_Mysites3_Content    0
WSS_Mysites4_Content    0
WSS_Mysites5_Content    0
WSS_Mysites6_Content    0
WSS_Mysites7_Content    0
WSS_Mysites8_Content    0
WSS_Mysites9_Content    0
WSS_Orgs_HR_Content 0
WSS_Orgs_its_Content    0
WSS_orgs_MA_Content 0
WSS_orgs_ND_Content 0

This brings about a number of questions I have and my need to understand what is happening. Why is there a discrepancy between the two nodes? If a backup is kicked off, which node will be used for backups? Will both nodes encounter backups? How can I ensure that only the second replica is the one performing the backups?

Our version is currently at 11.0.5058. Running the following statement on node1 shows secondary:

select name as AG_Name, automated_backup_preference_desc
from sys.availability_groups

All databases are synchronized. And all are in the same availability group.


texts: Postgres scalability - What is the impact of connection pooling?

texts:
We had a question over on Server Fault that raised an interesting question:
In light of the scalability improvements in Postgres 9.2 scalability improvements, is it better to use connection pooling mechanisms to avoid the overhead of making extra connections to the database, or is the connection overhead worth the improved read performance?

Relating it specifically to my environment: We have a web application that is database backed and read-centric, and we're currently running on Postgres 8.4.

Our re-implementation will be launching next year, along with an upgrade to 9.2, and gives each Apache worker process its own connection to the database (and thus its own Postgres backend which is preserved for the life of the Apache worker).
Based on what we've seen this seems to be a good balance between the overhead of connecting to the database and having more workers handling the read load, though we haven't yet done any substantial benchmarking of our own to confirm this.

Does that implementation seem like a reasonable one, and are there other options / connection pooling mechanisms which we should be considering in light of the recent scalability improvements?


texts: Access denied for user 'root'@'localhost' (using password: NO) on 'service mysql start'

texts:
Environment

$ uname -a
Linux aurora 4.4.0-59-generic #80-Ubuntu SMP Fri Jan 6 17:47:47 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

MySQL

$ mysqladmin --version
mysqladmin  Ver 8.42 Distrib 5.7.16, for Linux on x86_64

Clearly something is trying to log into my mysql server as root upon

service mysql start

but I don't know what it is.

The last line in /var/log/mysql/error.log related to starting the server is:

[Note] Access denied for user 'root'@'localhost' (using password: NO)

How do I figure the offending config file or server or application that is trying to log in as 'root'@'localhost' on startup? What other information can I provide to help to understand and resolve this issue?

Also, I am able to log in as 'root'@'localhost' if I do so explicitly. It's only the [Note] in the error log that is irritating me and that I want to debug.

In order to reduce the number of variables, I've also shutdown apache and deleted my crontab just in case that was part of the problem.

I am open to all suggestions.

Redirected from https://stackoverflow.com/questions/41601069/access-denied-for-user-rootlocalhost-using-password-no-on-service-mysql


texts: Symmetric difference operation in Transact-SQL?

texts:
I have always known about the UNION operator in SQL, but only recently discovered that there were other set operators, INTERSECT and EXCEPT. I haven't been able to find an operator that does the fourth big set operator, the symmetric difference (e.g. the opposite of INTERSECT.)

It looks like I can get the desired output by using something like

SELECT Field FROM A UNION SELECT Field FROM B
EXCEPT
SELECT Field FROM A INTERSECT SELECT Field FROM B

(assuming I got the precedence right), or by doing an anti-full-join:

SELECT A.Field, B.Field
FROM A
FULL JOIN B ON B.Id = A.Id
WHERE B.Id IS NULL OR A.Id IS NULL

But both of those look like rather intensive queries, especially compared to the other three basic set operations. Is there a symmetric difference operation in SQL and I just can't find it in the documentation? Or is there a "canonical" way to implement it in T-SQL?


texts: How to grant permissions to users based on table information?

texts:
Say I have a table:

+------------------------+
|       Employees        |
+------+--------+--------+
| Dept |  Name  | Salary |
+------+--------+--------+
|    1 | John   |  10000 |
|    1 | Mary   |  50000 |
|    2 | Alfred |  30000 |
+------+--------+--------+

Now I create two views:

create view dept1_memberview as select Dept, Name from Employees where Dept=1;
create view dept2_memberview as select Dept, Name from Employees where Dept=2;

What I want to do is give access to each employee in the table (their usernames match their entry under the 'Name' column) to the view that corresponds to the department they're in. I know I could just do:

grant select on db.dept1_memberview to John, Mary;
grant select on db.dept2_memberview to Alfred;

But mostly out of curious, and for larger databases with hundreds of employees, I would like to know how I'd do that.

Thanks in advance!


texts:
I have a Sample table:

Region    | OpenServices | DFC
Karaci    | 14           | 4
Lahore    | 13           | 3
Islamabad | 10           | 4

This is Second Table

Region    | OpenServices | DFC
Karaci    | 14           | 4
Lahore    | 10           | 3
Islamabad | 10           | 4

I want my Final table to look like

Region    | OpenServices | DFC
Karaci    | 14           | 4
Lahore    | 13 , 10      | 3
Islamabad | 10           | 4


texts:
I've been using pt-table-checksum on our staging server successfully, but when I run

./pt-table-checksum h=localhost,u=user --ask-pass --lock-wait-time=50 --databases prod

It just sits there. No output. Nothing in 'top'.

Any ideas where I should start looking? Thanks.


texts: How to pull high-level server information from Mysql

texts:
MySQL workbench shows the following under server stats:

I know it can grab the Connection Name and the Port from the data that I'm using to connect, but where does it grab the Host, socket, Version, Compiled For, and Running Since from? Is there a query that can grab all that information?


texts: How can i update table when counter reach a specific value? (thread safe)

texts:
I increase a field when somebody click the image. Also i want to update (decrease) customer budget when counter value reach to 1k. It is like that:

UPDATE click SET count = count + 1 WHERE customer_id = 1;
UPDATE budget JOIN click ON budget.customer_id = click.customer_id
 SET budget.total_budget = budget.total_budget - 10, click.count = 0
 WHERE budget.customer_id = 1 AND click.count = 1000;

But it should be thread safe, i don't want to decrease budget 2 times when 2 clicks happen same time. My question is how can do this with mysql?


texts:
I'm a database newbie trying to migrate lots of our log files into MySQL database.

I put 4 month of data, 200 million rows in one MySQL table, using a timestamp column as Index. This design works fine when query covers shorter time range like 1 day. However when I do 2 month query EXPLAIN gives plan of a full table scan. Unfortunately, this long time range query happens quiet often.

I am thinking is it worth to divide this large table into 4 smaller tables? Will it helps in performance?


texts: MongoDB uneven document sharding - and Migration Windows

texts:
I created a sharded mongodb cluster with 2 shards.
Then I created a sharded collection by range and inserted 10000 documents with random documents with random values for the sharded key using Java.
For some reason right after the insertion, the chunks are evenly distributed among the shards, however the number of documents are not.

This is what I see right after the insertion of the 10000 documents (in addition to existing 2 documents)

Shard shard0000 at 127.0.0.1:27017
 data : 2.65MiB docs : 4402 chunks : 7
 estimated data per chunk : 387KiB
 estimated docs per chunk : 628

Shard shard0001 at 127.0.0.1:27018
 data : 6.02MiB docs : 10002 chunks : 8
 estimated data per chunk : 770KiB
 estimated docs per chunk : 1250

Totals
 data : 8.67MiB docs : 14404 chunks : 15
 Shard shard0000 contains 30.57% data, 30.56% docs in cluster, avg obj size on shard : 631B
 Shard shard0001 contains 69.42% data, 69.43% docs in cluster, avg obj size on shard : 631B

First I don't understand why I see 14,404 documents instead of 10,000 that I inserted.

To my surprise, the next day, I come and run ```db.myCollection.getShardDistribution()``, and suddenly I see a completely different picture. The data suddenly became well distributed.

So I started experimenting and found out that if I run the following commands:

use config
db.settings.update(
   { _id: "balancer" },
   { $set: { activeWindow : { start : "11:06", stop : "11:07" } } },
   { upsert: true }
)

And wait for that time window to pass the data is suddenly making sense:

mongos> db.myCollection.getShardDistribution()

Shard shard0000 at 127.0.0.1:27017
 data : 2.65MiB docs : 4402 chunks : 7
 estimated data per chunk : 387KiB
 estimated docs per chunk : 628

Shard shard0001 at 127.0.0.1:27018
 data : 3.36MiB docs : 5600 chunks : 8
 estimated data per chunk : 431KiB
 estimated docs per chunk : 700

Totals
 data : 6.02MiB docs : 10002 chunks : 15
 Shard shard0000 contains 44.03% data, 44.01% docs in cluster, avg obj size on shard : 631B
 Shard shard0001 contains 55.96% data, 55.98% docs in cluster, avg obj size on shard : 630B

In my Java code that inserts 10000 documents, all I do is generate 10,000 Document objects and run an insertMany(documents) in the end.

  List<Document> documents = new ArrayList<>();

  for (int i = 0; i < 10000; i++) {
     Document document = new Document();
     document.append("name", generateName())
                    ...
     documents.add(document);
  }

  collection.insertMany(documents);

So what is going on here?

Note: I set the max chunk size to 1MB just to experiment with sharding
Thanks

Update:

A the key on which I'm sharding is a random string generated like this:

 private static String generateName() {
        StringBuilder name = new StringBuilder();

        name.append(RandomStringUtils.randomAlphabetic(1).toUpperCase());
        name.append(RandomStringUtils.randomAlphabetic(5, 10).toLowerCase());

        return name.toString();
    }

So for the large number of documents, the distribution should be fairly uniform.


texts: How to design DB for statistics service?

texts:
I'm going to create statistics on RoR. I have 3 models: User, Project and Channel. User owns Projects, whereas Projects have many Channels. Now I'm designing all these models using MySQL.

The goal of the service is to provide user the variation in time of different parameters of each Channel. E.g.: Channel Google Adwords has 1000 show/day, 100 clicks/day and 2 people which came from Google Adwords bought something that day. So I could have just stored all the information in columns, like: Channel "Google Adwords", shows: 1000, clicks: 100, buyings: 2. But I need to make distribution by days, please, give me an advice how to design such database? Maybe I should use another solution, maybe NoSQL?

Upd.

Input: User daily fills in data about ads from different sources (I call them channels, e.g.: Google Adwords): number of impressions and clicks (later data will be imported automatically usiing API).
Output: Various stats like graphs and CTR of different channels, all channels and so on.


texts:
I have a gear running a instance of mongoDB. I wish to connect to the mongo shell. How can I do it?

I have rhc and mongoDB installed on my computer.

I have RockMongo on the gear, but I cannot create a geospatial index from RockMongo.

I used rhc port-forward whic didn't give any error.

and then tried to connect using the following.

mongo -u<your mongodb user name> -p<the password for mongoDB>  --host <the ip number for mongoDB> --port <the port number> <app name>

for host I used the localhost address because I have port forward running. Now I can connect but every command i use results in "errmsg" : "unauthorised"


texts:
The invoice number is characters and numbers combined but I want to change these to numerical only in SQL.

I want like this CC/DDD/1, CC/DDD/2 .... n


texts:
How can MyISAM be "faster" than InnoDB if

MyISAM needs to do disk reads for the data?
InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?


texts: How does one create a data base for a survey/questionnaire?

texts: I need to create a database for a wordy questionnaire. At the end I want to be able to graph my information. Where can I do this?


texts:
I am trying to import raster data into PostGIS using raster2pgsql and as reference the PostGIS Cookbook.

First I created the sql file in the following command

raster2pgsql -d -I -C -M -F -t 100x100 -s 4326 tmax*.bil chp01.tmax_2012 >
tmax_2012.sql

When i try to run the sql file using this command line:

psql -d postgis_cookbook -U postgres -f tmax_2012.sql

I get the following error:

LINE 1: ÿþB1F8D1F8D1F8D1F8D1F8D1F8D1F8D1F8D1F8D1F81F8D1F8D1F8D1F8D1F...

This also the same when trying only one raster file. I could load the sql file into pgadmin GUI and it works. But i want to do it on the command line


texts: Exporting SQL Server 2008 R2 Activity Monitor Log

texts:
In SQL Server 2008 R2, admin users are allowed to view the Activity Monitor and under "Recent Expensive Queries", there is a list of SQL statements being ran on the server.

Is there a way to export each and every one of the SQL statements displayed without using SQL Server profiler?

I was given the responsibility of monitoring a database with many redundant tables and I have no idea which tables are actually in use.

I want to remove those dummy or unused tables and retain the productive tables.

Any suggestions on how to accomplish this?


texts: Running .sql files in postgres (structured programs)

texts:
In oracle I could create .sql files and run them with a structure (like a stored procedure) with

begin

  declare

end;

I did some tests, because I need to run scripts to fix problems, not is necessary record it as a stored procedure/function on server, so like in Oracle, I want to run it once and check the result.

Not is possible run .sql files on postgres with CURSOR, declare, variables, etc.. , only directly on server with stored procedures/functions ?


texts: UPDATE a column in one table according to a column in another

texts:
In Postgres, I have two tables that have become de-normalized. We're in the process of migrating to a normalized form, but in the meantime the tables have become out of sync. I'd like to copy the values for the native_name column from the native_name_aliases table into the dpoint table. Here is my attempt:

update dpoint
set native_name = (select
                      nna.native_name
                    from
                        native_name_aliases as nna
                    LEFT JOIN
                      dpoint as d
                    ON
                      d.dpoint_id = nna.dpoint_id)
where building_id = 42;

I get an error message:

  more than one row returned by a subquery used as an expression

My native_name_aliases table has fields id, native_name, dpoint_id, building_id.
And the dpoint table has dpoint_id, building_id, native_name and a bunch of other irrelevant fields.

Where am I going wrong?


texts: Checkopenport - DB2 / shell script sample

texts:
at DB2 startup we will check, whether the db2 port is in use.
In the client DB2 setup we have only one instance / server.
the port is always 50020 - for all servers.
our idea -
netstat -tunlp | grep ":50200"
and checking the return code
this works - but other ideas are welcome


texts: Transferting large SQL Databases to Azure (>300MB)

texts:
I want to transfer a local SQL Database to Microsoft Azure.
There are files stored in the db, so the db is about 300MB in size.

From SQL Server Management Studio I tried the following:

  Tasks->Generate Scripts

I was able to create the .sql script but couldn't load it.

I also tried the

  Tasks->Deploy Database to SQL Azure...

but after about 30 mins, it crashed.

Any ideas?


texts: Multi SQL Server Web licence on Single Machine

texts:
I have a cloud server on 1and1.com and that has 8 Socket/Core. I have aslo installed the SQL Server Web Edition provided by 1and1.com. At the the time of installing the options shows to me you need 2 licence (as Web edition is limited to 4 socket). They also telling, you have 2 licence and 2 licence distributed among  8 core and billed me for two licences. but it's look like our sql server is using only 4cpu.

When i run

  select scheduler_id, cpu_id, status, is_online
  from sys.dm_os_schedulers
  where status = 'VISIBLE ONLINE'

It is showing only 4 cpu is online. and rest four are offline when i run

  select scheduler_id, cpu_id, status, is_online
  from sys.dm_os_schedulers
  where status = 'VISIBLE OFFLINE'

Can anyone help me to understand with this situation.


texts:
I am having issues starting my SQL services. Currently it is failing the start due to:

  FileMgr::StartLogFiles: Operating system error 2(The system cannot
  find the file specified.) occurred while creating or opening file
  'E:\Program Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf'. Diagnose and
  correct the operating system error, and retry the operation.

The issue herein lies in the fact that this .ldf file is not on the E drive. There is no E: drive. The file is located on the F: drive.

I cannot find anywhere to tell SQL to change this. I can not drop/move/detach/etc the databases, because I can't even get the service to start, in order to connect.

This is happening on 3 different instances of SQL, after volume moves. Any ideas?

SQL 2008


texts: Oracle database doesn't go to upgrade mode

texts:
I am trying to upgrade/migrate from 10g to 11g. When I start the database in upgrade mode with:

startup upgrade

select status from v$instance

STATUS
--------
MOUNTED

It doesn't go to upgrade mode.


texts:
I have two SQL Server instances running on Windows Server, SQL Server cluster with 2 nodes with shared storage (Active, Active type).

The SQL Server version running is SQL Server 2012 Enterprise edition.

Can we implement AlwaysOn on this? Is it feasible then what are the complexities and steps?


texts: Why is there a difference between these transaction DMVs in SQL Server 2008 R2?

texts:
When I execute the two queries below,

SELECT
    session_id, transaction_id
    FROM sys.dm_tran_session_transactions;

AND

SELECT
    session_id, request_id, at.transaction_id
    FROM sys.dm_tran_active_transactions at
        JOIN sys.dm_exec_requests r
            ON r.transaction_id = at.transaction_id;

I've read the BOL for both 1 and 2 but don't see any clear explanation as to why the difference would occur.

I get different results. The former query returns no results, but the latter returns active transactions with session and transaction ids. The request_id is 0 which, I think, means that it's the only request made by the session. Could someone help me understand why there is a difference between the two concepts I've queried above?

EDIT

I just reran the queries and now I get a result for the first DMV which has a session_id that is not actually contained in the second result set.


texts: SQL Server 2000 : List of tables ordered from by number of referenced tables

texts:
Is there any way in SQL Server 2000 to get a list of tables ordered by dependency? I need it for insert order in empty data base.

I can't turn off constraints, so I'm stuck with this solution.

Thanks


texts: How to make MySQL table name case insensitive in Ubuntu?

texts:
I am using Ubuntu 13.10 and MySQL 5.6 and I know database name and table name are case sensitive in Ubuntu (and some other *nix environments) by default.

Now, I want to make MySQL work as case insensitive in Ubuntu.

Is it possible? If yes, how can I do it?


texts: IN Clause - performance difference between listing values and query

texts:
I changed this query

SELECT ...
FROM linkedServer.DB.Schema.Table1 t1
LEFT JOIN linkedServer.DB.Schema.Table2 t2 ON t1.ORDER_ID = t2.ORDER_ID
WHERE t1.BRANCH_ID NOT IN (
        '009991', '009992', '009993', '009994', '009995', '009996', '009999', '900001',
        '900002', '900003', '900004', '900005', '900006', '900007', '900008', '999991',
        '999992', '999993', '999994', '999995'
        )
GROUP BY ...

into this

SELECT ...
FROM linkedServer.DB.Schema.Table1 t1
LEFT JOIN linkedServer.DB.Schema.Table2 t2 ON t1.ORDER_ID = t2.ORDER_ID
WHERE t1.BRANCH_ID NOT IN (
        SELECT b.BRANCH_ID
        FROM TB_BRANCH b --25 rows in total
        WHERE b.START_DT = '99999999' --the result of this sub-query is fewer than list above.
        )
GROUP BY ...

then performance dropped.

Why is this huge change of Execution plan happening? How can I avoid performance drop while using a sub-query in IN clause?


texts: What happens when changing column type DATETIME to TIMESTAMP in MySQL?

texts:
I want to convert a column data type from DATETIME to TIMESTAMP in a table with more than one million of lines.

Is it possible? Is it slow? Will I lose the old information or MySQL can convert this automatically? Can I just do an alter table or may I do something different?


texts: Can Postgres synchronous replication stall a cluster if secondary/standby crashes?

texts: In a setup with Postgres synchronous replication, let's say one of the secondary/standby machines crashes, will that lock/halt the master? That is, since the Postgres master always waits for confirmation from the secondary/standby before it commits the write transaction, then will the master simply stop functioning if the secondary/standby is inaccessible? How does Postgres deal with this case?


texts: how to make sure pt-sync-table data is the latest?

texts:
the first step is to checksum difference and record into percona.checksum table

pt-table-checksum h=xx,P=xx,u=xx,p=xx method=dsn=h=xx,D=xx,t=xx --databases=kobe

the second step is to print the repair SQL into a file

pt-table-sync --print --sync-to-master h=xx,P=xx,u=xx,p=xx >repair.sql

and I need to take this repairsql file to developers,show them how many records are difference, difference detail, and let them make sure。Ater that
I execute the repair sql

so my question is what I execute this old repair SQL, the new data is inserting ,and the new difference is creating, maybe this old repair SQL will cover the latest data !

can anybody help me ? thank you !


texts:
When I am running a form in Forms 11g

http://rahulsingh-pc:8888/forms/frmservlet

I'm getting an error:

Failure of server APACHE bridge:
No backend server available for connection: timed out after 10 seconds or idempotent set to OFF.

Products installed:

Web Logic 10.3.5
forms&reports&portals 11.1.2.0

Can anybody help me fix this problem?