texts: Query 100x slower in SQL Server 2014, Row Count Spool row estimate the culprit?

texts:
I have a query that runs in 800 milliseconds in SQL Server 2012 and takes about 170 seconds in SQL Server 2014. I think that I've narrowed this down to a poor cardinality estimate for the Row Count Spool operator. I've read a bit about spool operators (e.g., here and here), but am still having trouble understanding a few things:

Why does this query need a Row Count Spool operator? I don't think it's necessary for correctness, so what specific optimization is it trying to provide?
Why does SQL Server estimate that the join to the Row Count Spool operator removes all rows?
Is this a bug in SQL Server 2014? If so, I'll file in Connect. But I'd like a deeper understanding first.

Note: I can re-write the query as a LEFT JOIN or add indexes to the tables in order to achieve acceptable performance in both SQL Server 2012 and SQL Server 2014. So this question is more about understanding this specific query and plan in depth and less about how to phrase the query differently.

The slow query

See this Pastebin for a full test script. Here is the specific test query I'm looking at:

-- Prune any existing customers from the set of potential new customers
-- This query is much slower than expected in SQL Server 2014
SELECT *
FROM #potentialNewCustomers -- 10K rows
WHERE cust_nbr NOT IN (
    SELECT cust_nbr
    FROM #existingCustomers -- 1MM rows
)

SQL Server 2014: The estimated query plan

SQL Server believes that the Left Anti Semi Join to the Row Count Spool will filter the 10,000 rows down to 1 row. For this reason, it selects a LOOP JOIN for the subsequent join to #existingCustomers.

SQL Server 2014: The actual query plan

As expected (by everyone but SQL Server!), the Row Count Spool did not remove any rows. So we are looping 10,000 times when SQL Server expected to loop just once.

SQL Server 2012: The estimated query plan

When using SQL Server 2012 (or OPTION (QUERYTRACEON 9481) in SQL Server 2014), the Row Count Spool does not reduce the estimated # of rows and a hash join is chosen, resulting in a far better plan.

The LEFT JOIN re-write

For reference, here is a way that I may re-write the query in order to achieve good performance in all SQL Server 2012, 2014, and 2016. However, I'm still interested in the specific behavior of the query above and whether it is a bug in the new SQL Server 2014 Cardinality Estimator.

-- Re-writing with LEFT JOIN yields much better performance in 2012/2014/2016
SELECT n.*
FROM #potentialNewCustomers n
LEFT JOIN (SELECT 1 AS test, cust_nbr FROM #existingCustomers) c
    ON c.cust_nbr = n.cust_nbr
WHERE c.test IS NULL


texts:
I want to delete a row from a table if that table exists.

Below is the delete statement

DELETE
FROM UM_CLAIM_BEHAVIOR
WHERE UM_TENANT_ID = ?

Can I extend this query to check the existence of the table or is there any other alternative to check existence of table before executing delete query?

According to comments it is not possible to give a platform-independent solution for this.


texts: Replication testing is a Materialized view Refresh equivalent of continious update?

texts:
In my database I have the following a materialized view named materialized_view_test and the data are being feeded from 3 tables:

select
 *
from
 a_table join b_table on b_table.a_id=a_table.id
 join c_table on b_table.c_id=c_table.id

As you can see the b_table is a pivot table for a_table and c_table. Also my database for testing purpoces has a master-slave replication with the slave to be on a hot stanby replication mode.

I try to stress my database in order to investigate which configuration on the following parameters is the optimal:

max_standby_streaming_delay
max_standby_streaming_delay
hot_standby_feedback

So I continiously run the following queries on my database over an infinite loop (for convenience lets name the test as test1):

Set 1:

DELETE FROM b_table where a_id=12 and c_id=33
REFRESH MATERIALIZED VIEW materialized_view_test;
VACUUM (VERBOSE) materialized_view_test

Set 2:

INSERT INTO b_table(a_id,b_id) VALUES (12,33);
REFRESH MATERIALIZED VIEW materialized_view_test;
VACUUM (VERBOSE) materialized_view_test

And I toggle between 2 sets into my database by checking a counter whether is odd or even, using a custom script. Afterwads, I read from the materialized view in a purpocely non-optimal written query in order to cause a load:

select * FROM (
  SELECT * FROM (
   SELECT *, pg_sleep(5) FROM b_table join b_table as b_table12 on btable.b_id=b_table12.b_id
   ) as dummy
) as dummy2

But in my database I rearely do any deletes. So I though if I was continiously toggling a boolean flag over an infinite loop in one of my tables (for convenience lets name the test as test2):

UPDATE a_table set some_flag = NOT some_flag where a_id=12;
VACUUM (VERBOSE) materialized_view_test;

And then continiously sellecting over it in a infinite loop:

select * FROM (
  SELECT * FROM (
   SELECT *, pg_sleep(5) FROM a_table join a_table as b_table12 on btable.a_id=b_table12.a_id
   ) as dummy
) as dummy2

So does in my situation the test1 can have same results and effects as running into test2 or I need both tests in order to test my replication schema.


texts: How can I override the default values for a parameter in a report?

texts:
I have a report with a parameter that usually gets its default values by running a query that takes another parameter as it's input. This works fine when the report is run interactively.

However, I have a requirement to call this report from code where all the filters are supplied by the code. The code I have for the other parameters works fine:

   string report = "Analysis/Report.aspx?ReportPath=/MyReport";
   string fullReportPath = string.Format("{0}&LocationId={1}&BatchNumber={2}&SearchReference={3}", report, locationId, batchNumber, reference);
   RunReport(fullReportPath);

There is a drop down where the both the available values and default values are retrieved from the database via a query which has the locationId as a parameter. This is so all the values are selected by default.

What do I need to change in the report and/or the code so it works as it does now and I can supply the selected values for this drop down as a parameter?

I've found this blog post which uses the concept of internal or hidden control parameters to use when setting the values of other parameters, but the example given just sets the other parameter to a default value or leaves it blank. I need for the parameter to run the current query.


texts:
When i test select * from myFederatedTable (for test there is 50k rows) it take 0.3732 seconds
now I have two federated tables with data < 20000 in both tables, and have query like :

SELECT i.id_news, i.id_news_type, i.id_agency, i.add_date, i.update_date, j.title, k.image_name_latin, m.folder_path
FROM tbl i
INNER JOIN tbl_languages j on i.id_news = j.id_news and j.lang = 'en'
INNER JOIN federated_Table1 k on i.id_image_groups = k.id_image_groups
INNER JOIN federated_table2 m on k.id_folder_groups = m.id_folder_groups
ORDER BY i.update_date DESC
LIMIT 14

It takes 16 seconds

Without joins to federated tables

SELECT i.id_news, i.id_news_type, i.id_agency, i.add_date, i.update_date, j.title
    FROM tbl i
    INNER JOIN tbl_languages j on i.id_news = j.id_news and j.lang = 'en'
    ORDER BY i.update_date DESC
    LIMIT 14

0.0496 seconds.
I have few questions

1.Is there any way to optimize it ?
2.when i select from federated table (really i select from main database?) or when i join to federated table I really join to main database?
3.Task is copy data from one database tables to another database tables. Is there best way to replicate this 2 tables for quick
selection ?


texts: Subqueries' aliases same as main queries' aliases

texts:
I have an SQL query whose aliases are the same as some of its subquery's aliases.

For example:

select *
from ROOM r
where ...
         (
              select *
              from ROAD r
              where ...
         )

This works fine, as the subquery's alias seems to hide the main one's.

Will it work that way in all cases?
Will I ever get undefined results?
If it's OK to do that, how can I make a reference to the main query's r?


texts: Migrating SQL Server 2000 to 2008/14 - Stored procedures compatibility?

texts: I am planning migration from old SQL server 2000 db to 2014 (through 2008 extra step). I Wonder if stored procedures will work with no tweaks or arrangements are required


texts: MV on remote/master table fails to resume syncing (after master db host was temporarily unreachable)

texts:
I have a fast-refresh materialized view in a target db (18c) that syncs from a remote table/master db (19c):
--master db (19c)
create materialized view log on master.workorder with primary key;

--target db (18c)
create materialized view target.wo_mv
build immediate
refresh fast
start with sysdate
next sysdate + (15/(60*60*24))
as (
select
    workorderid
    --other columns
from
    master.workorder@my_dblink
);

My organization has ongoing issues where the master database's host becomes intermittently "unreachable". This happens daily and lasts for approximately 15 minutes. It's a serious issue that is being investigated and is beyond my control.

My DBA tells me that we get ORA-12514 & ORA-3150 errors in the target db when the master db is unreachable.
ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

ORA-03150: end-of-file on communication channel for database link

Problem:
When the master db becomes unreachable, of course, the MV in the target db fails to sync. That makes sense.
However, what doesn't make sense to me is: the MV in the target db doesn't resume syncing once the master db comes back online.

Records fail to sync from the master MV logs to the target MV. The records just sit in the master MV log table and nothing happens.

Question:
As a novice, I would have expected the MV in the target db to automatically resume once the master db came back online. Everything else that uses the dblink between the two dbs works fine. Just not the MV sync.
Is there a reason why the MV in the target db wouldn't automatically resume when the master db comes back online?


texts: MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine

texts:
I'm loading a 100GB file via LOAD DATA INFILE. I've had good success with MyISAM, a few hours and done.

I'm trying it now using InnoDB. The load starts fast at over 10MB/sec (watching the table file growth, file_per_table is turned on).

But after about 5GB of data it slows down to the 2-4MB/sec range, as I get over 20GB it was down around 2MB/sec.

InnoDB buffer pools size is 8G.  And I've done the following prior to running the LOAD DATA INFILE command:

SET @@session.sql_log_bin=0;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
alter table item_load disable keys;
//Run LOAD DATA INFILE....

I can't see the reason why it's starting off well and slowing down over time.

Also, using the same settings, I ran the same LOAD DATA INFILE command with the table using InnoDB and MyISAM and a 5GB test dataset, MyISAM was 20x faster:

InnoDB:

mysql> LOAD DATA CONCURRENT LOCAL INFILE '/tmp/item' REPLACE INTO TABLE item_load;
Query OK, 2630886 rows affected, 6 warnings (21 min 25.38 sec)
Records: 2630886  Deleted: 0  Skipped: 0  Warnings: 6

MyISAM:

mysql> LOAD DATA CONCURRENT LOCAL INFILE '/tmp/item' REPLACE INTO TABLE item_load;
Query OK, 2630886 rows affected, 6 warnings (1 min 2.52 sec)
Records: 2630886  Deleted: 0  Skipped: 0  Warnings: 6

Anything else I should consider trying? The MyISAM engine is able to keep up the load rate much better.

Additional details:

I've tried loading the files individually, no difference.
Incidentally, I have 150 files of 500MB each, within each file the keys are sorted.
After getting 40GB in overnight, 12h later, the load rate was down to 0.5MB/sec, meaning the operation is, practically speaking, impossible.
I haven't found any other answers to similar questions on other forums, it's seeming to me that InnoDB doesn't support loading large amounts of data into tables over a few GB in size.


texts: Mongo: how to covert a string to decimal?

texts: I have written millions of documents with $CURRENT_CASH_BALANCE as strings instead of decimals.  How can I convert them all to decimals?


texts: All manual settings get wiped out when saved in My.cnf, why?

texts:
When I open my.cnf to enter manual setting like:

innodb_buffer_pool_size = 50M
innodb_log_file_size = 15M
bulk_insert_buffer_size=25M
log-slow-queries = mysql-inja-slow.log

All of my settings get vanished when I hit Save! My server has mysql  5.1.


texts: Is it possible to mix mongod version in a sharded cluster

texts:
I'm playing around with sharded cluster and I'm thought I saw somewhere that different mongo version could live within the same cluster.

For example :

mongos : 4.0
configRs : 4.0
shardRs01 : 3.4
shardRs02 : 4.0

It would have saved me a lot of time because I would have migrated my shardRs01 to 4.0 (and debian 9) by simply moving the chunks to the rest of the cluster and deleting it once done.

But after thinking about it, it seemed strange that it could be possible, due to the compatibility issue between mongo versions.

And my tests are supporting this conclusion :

I cannot add a 3.4 shard into a 4.0 cluster
Reversely I cannot add a 4.0 shard into a 3.6 cluster

So I wanted to make sure that it was the case that It is not possible to add a shard that does not use the same mongo version as the cluster.


texts: Order by for 2 row result causes 800% performance hit

texts:
The following query, run against a table of about 6 mil rows takes 25 seconds. When I remove the final order by, it runs in 3 seconds. There are no indexes on the table (it's an intermediate SSIS ETL target that is later pulled into a DW.)

It isn't caching. I've run it multiple times in any order with consistent results.

The query itself checks for gaps in sequence numbers. SSIS will use it to see if it needs to re-fetch anything.

;with edges as
(
    select
        ROW_NUMBER() over (partition by s1.SiteIDNumber order by s1.SequenceNumber) as rn,
        s1.SiteIDNumber,
        s1.SequenceNumber
    from TestPlay s1
    left join TestPlay s2 on s2.SiteIDNumber = s1.SiteIDNumber and s2.SequenceNumber = s1.SequenceNumber + 1
    left join TestPlay s3 on s3.SiteIDNumber = s1.SiteIDNumber and s3.SequenceNumber = s1.SequenceNumber - 1
    where s2.SiteIDNumber is null
    or s3.SiteIDNumber is null
),
gaps as
(
    select
        e.rn,
        e.SiteIDNumber,
        e.SequenceNumber  + 1 as StartSeq,
        e2.SequenceNumber - 1 as EndSeq
    from edges e
    join edges e2 on e2.SiteIDNumber = e.SiteIDNumber and e2.rn = e.rn+1
    where e.rn = (e.rn / 2) * 2
)
select * from gaps order by rn

Here is the only part of the Execution Plan that seems to be different:

With ORDER BY (25 sec):

No ORDER BY (3 sec):


texts: Warehouse storage system (FIFO by BBD and date)

texts:
I have inventory table.

CREATE TABLE inventory
(
InventoryID int(11) unsigned PRIMARY KEY AUTO_INCREMENT,
InventoryDate   datetime,
Quantity    decimal(15,4),
BBD date NULL,
ItemID  int(11) unsigned NULL,
MoneyValuePerUnit   decimal(20,4) NULL);

and rows inside

INSERT INTO inventory (InventoryDate, Quantity, BBD, ItemID, MoneyValuePerUnit) VALUES
('2019-12-01 16:23:39', 100, null, 1, 1.23),
('2019-12-02 16:27:11', 200, null, 1, 1.55),
('2019-12-03 16:31:58', 100, null, 1, 1.23),
('2019-12-10 16:23:39', -100, null, 1, 1.23),
('2019-12-11 16:23:39', -137, null, 1, 1.55);

It is about warehouse managment system. Some product (ItemID=1) is taken to warehouse 1,2,3 dec (with different prices)
and some quantities left warehouse (10 and 11 dec) with corresponding price.

Warehouse is working FIFO (or, if we have BBD (best before date) then the worst BBD first)

So, my problem is, when I'm creating outgoing order in my system, the corresponding quantity of goods must left the warehouse = I need to insert one+ rows to table inventory with negative Quantity (like 2 last rows in this table).

But, I need to calculate what BBD and price will left warehouse.
In this example, I have no BBD's at all (just for example I have product without BBD), but I have different purchase price (1.23 and 1.55 per item)

So, when I added first row, I took 100 quantities of item per 1.23/item
Second row: +200 quantities but different price 1.55
Third row: +100 quantities but price is also 1.23

Then I received first sales order for 237 items.
And I need to "reduce" warehouse sequentially, starting from first item in stock.
In this example I see, that I have 100 items with price 1.23, so I insert row N 4, to deduct this amount by this price
next I have to deduct 137 items, and I have 200 items by price 1.55, so I insert row n 5 to deduct 137 items by 1.55

Now I have in warehouse 100 items by 1.23, and 63 items by 1.55. But order is very important. Because items per 1.55 (row 2) came first than items by 1.23(row 3), and row 1 is "empty".

And finally, I received second sales order for 100 items.
So I have to somehow calculate, what I need to reduce firstly 63 items by price 1.55, and only then 37 items by price 1.23

Question is: How can I calculate it? I have that products left warehouse obey the following rules: worst BBD first. If BBD is the same, then first come left first (by InventoryDate)

SQLFiddle is here:
http://sqlfiddle.com/#!9/99cf48/4


texts: Repeating rows of date intervals based on another table in Oracle

texts:
I have a table of maintenance plans with date intervals between each required maintenance period.

Fiddle: http://sqlfiddle.com/#!4/af742

create table maint_sched (maint_id number, start_date date, day_interval number);
insert into maint_sched values (1, date '2015-01-01', 90);
insert into maint_sched values (2, date '2015-04-01', 200);
insert into maint_sched values (3, date '2015-07-01', 30);

I need to write a query which generates rows for all maintenance schedules up to a finite point in the future (e.g. 12 months).

MAINT_ID   DATE_DUE
--------  ---------
1          1-Jan-15
1          1-Apr-15
2          1-Apr-15
1         30-Jun-15
3          1-Jul-15
3         31-Jul-15
3         30-Aug-15
1         28-Sep-15
3         29-Sep-15
2         18-Oct-15
3         29-Oct-15
3         28-Nov-15
1         27-Dec-15
3         28-Dec-15

I've been able to get as far as generating fixed intervals with CONNECT BY tricks, but it starts doing something recursive when I try joining it to a table other than dual.

-- This generates a list of every five days for 36 months
SELECT (sysdate + (5 * level)) AS duedate
FROM dual
CONNECT BY (sysdate + (5 * level)) < add_months(sysdate, 36)

I have a feeling this is the right approach, but I could use some help making it work!


texts: I want to find out in SQL SERVER 2012, who is creating SQL Logins and on which date

texts: I was trying to find out in sql server 2012, who created login and which date. Can any body help on this thanks,


texts: VARCHAR2 should perhaps be DT_WSTR when working with SSIS against a Oracle Database?

texts:
When using SSIS with Oracle database, it seems to always assume that a VARCHAR2 maps to a DT_STR.  However, if my Oracle encoding is set to AL32UTF8, I should be able to store any unicode character in a VARCHAR2, right?  Is there any way to force SSIS to map an Oracle VARCHAR2 to a DT_WSTR? Should there be?

I looked up a few other questions on this, but nothing seems to address it.

I have a data source that has many columns as DT_WSTR, and I want each to go into a VARCHAR2 column without having to convert every column.


texts: Can I log psql time and statements to a text file (without the data)?

texts:
I am currently comparing performance of PostgreSQL with several other SQL systems. I am aware of the \timing option to turn on timing queries. However, I would very much like to automate the process of copying the statements executed and the query speed below it. I imagine there is a simple way to log this?

Let's say I run:

CREATE TABLE t1 AS
SELECT itemID, prodCategory
FROM products
WHERE prodCategory = "footwear"

I want to automatically save into a text file:

CREATE TABLE t1 AS
SELECT itemID, prodCategory
FROM products
WHERE prodCategory = "footwear"

SELECT 7790
Time: 10.884 ms

If OS Specifications are needed, I am using MacOS.


texts:
I am trying to perform a deep copy on RS. See http://docs.aws.amazon.com/redshift/latest/dg/performing-a-deep-copy.html

I created the new/temporary table using the SAME DDL with compression settings as the original table.

I have 3 nodes, each node is using 21% disk space.

When I run the copy:

insert into events_tmp (select * from events);

I run out of disk space.
Does the deep copy not compress the data?

Thanks!


texts: Force everyone to use snapshot isolation

texts:
I have a scenario in which I don't want any readers to block writers. I don't want users using table hints or isolation levels to issue shared locks, which might block writers.

Is there a way for SQL Server to force everyone to use snapshot isolation or remove the explicit hints they added?

I have read Can I force a user to use WITH NOLOCK?

It's ok if user just uses RCSI or SI. Thus no S lock. However, user might issue ad-hoc queries with some table hints (e.g. REPEATABLEREAD) or change isolation level, which can block write. For example, if one user does:

SELECT * FROM X WITH (REPEATABLEREAD)

...if table X is big, likely the table will have S lock and writes to that table will be blocked, which means data loss to me in a real-time scenario.

For AlwaysOn Readable Secondary Replicas:

  All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. Also, all locking hints are ignored. This eliminates reader/writer contention.

I want to do similar things, that is to limit the isolation level somehow. Is that possible?


texts: SQL How to copy text from one table (from one column) to another table (to one column), preferably with IF the target column is empty

texts:
I have two tables in a database and I wish to copy text from one column of the table to another column in another table.

Lets say I have two tables called 'articles' and 'info'. I have to copy articles.article column text into info.description for the same articles, which can be identified by a so called 'Codename' and not ID. Preferably IF the column info.description is empty. I intend to use WHERE category = 4 to limit myself to only some articles, but that shouldn't be a problem and if the IF statement is correct even unnecessary.

Tables layout (Not whole, but enough for here):

Articles (ID, Codename (VARCHAR), Article (VARCHAR))
Info (ID2, Codename (VARCHAR), Description (VARCHAR))

So basically, I would like to give articles with no description a basic description with their name and some data, that is written beside the name.


texts: 'packet too large' error with packet smaller than max_allowed_packet

texts:
Some time ago we started storing serialized data in our MySQL database and immediately ran into the well known 'packet too large' error. We increased max_allowed_packet to 64M and all was well.

This morning, I suddenly received a too large packet 26814732 error, even though

mysql> show variables like '%packet%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.00 sec)

How is this possible? I found this related post, but we are not dealing with mysqldump, we haven't changed any other database configuration settings and we've already been storing BLOBs larger than this one. What could've changed to lead to this situation?


texts:
I have got some problem to implement Merge Replication in SQL server 2008 R2 which send me the error response at the time of Synchronization using PULL from Publisher. I have tried to solve the problem by Stopping Merge Agent from JOB in sql server, Restart SQL Server Agent services, from Sql Server Agent JOB history i have again tried to restart specific Merge JOB agent but i have got still same problem which Show in below error :

After that when i forcefully tried to start above synchronization process then another error message shown which is below :

Can you please help me to resolve this issue in SQL server.


texts: Multiple many-to-many database relations

texts:
SO told me to ask here, so this is just copy-pasted from over there

We're making a database of anime. Every anime can be listed as having been made by an arbitrary number of studios, and every studio can have made an arbitrary number of anime.

The setup we're using to store the data is like this:

class Anime(Base):
    __tablename__ = 'anime'
    uid = Column(Integer, Sequence('anime_id_seq'), primary_key=True)
    studios = relationship("StudioEntry", backref="anime")

class StudioEntry(Base):
    __tablename__ = 'studio_entries'
    uid = Column(Integer, Sequence('studio_entry_id_seq'), primary_key=True)
    anime_id = Column(Integer, ForeignKey('anime.uid'), nullable=False)
    studio_id = Column(Integer, ForeignKey('studios.uid'), nullable=False)

class Studio(Base):
    __tablename__ = 'studios'
    uid = Column(Integer, Sequence('studio_id_seq'), primary_key=True)
    name = Column(String)

Where each StudioEntry matches a Studio to an Anime.

This by itself is fine, however there's many kinds of similar data we have to store (think licensors, publishers, genres). Ideally, we'd like to replace the {Studio,Licensor,...}Entry classes (and tables) with one "MetadataEntry" class. What would be the best way of going about this, and is this even advisable to begin with?

Answers to questions in comments:

@mustaccio It just seems redundant to have so many tables for what is essentially one thing. I can't really give a concrete reason why I think it's bad, just that it feels like it's not the right thing to do.


texts: How can I pass column to function in sql?

texts:
I'm calculating median as:

DECLARE @TEMP TABLE
(
    ID INT
)

Select
(
        (
            Select Top 1 ID
            From
            (
                Select  Top 50 Percent ID
                From    @Temp
                Where   ID Is NOT NULL
                Order By ID
            ) As A
            Order By ID DESC
        ) +
        (
            Select Top 1 ID
            From
            (
                Select  Top 50 Percent ID
                From    @Temp
                Where   ID Is NOT NULL
                Order By ID DESC
            ) As A
            Order By ID Asc
        )
) / 2

Above query I want to use. But, in my case there are so many columns for those I want to calculate MEDIAN. But I think it would be bad to repeat above block of code for each column.
So, I'm trying to define separate function which would accept column values, process and return median.
Should I have to define table-value-funtion for that or there is another optimized way to do so??

This question is related to the following questions:

How to write function in sql which accept table as input and return result back as table?
Getting error "must declare the scalar variable" in SQL function even though it is declared
why unable to execute table-valued function?
https://dba.stackexchange.com/questions/127328/how-to-use-udf-function-in-insert-into-select-from-clause
Calculating multiple medians


texts: How to get the first two octets in an IP address Oracle SQL

texts:
E.g., IP = '10.11.12.13'
I want to extract '10.11.' or everything except what comes after the 2nd '.'


texts: Trigger to automatically add values to a table in Oracle

texts:
I am new to Oracle.

I am looking for a trigger which helps to automatically insert values into a table when data is entered into another table in the same database.

I have tried a lot and didn't succeed. Can anyone explain how this is done?


texts: Separate databases, schemas, or another option?

texts:
I am using Postgres currently for my database and I am looking for the most logical and efficient solution for the case below:

I have a database set up right now which contains all of the productivity data for every single project in my company. Everything is standard, all columns are conformed, everything is automated (the original data comes from many different sources and formats). I have a whole section of dimension lookup tables and numerous fact tables which are being used for reporting.

I have been asked to add some other information which is much more specific to certain projects and I am trying to think of the best way to go about it. These new tables have nothing to do with the main database and there the type of data being stored will vary wildly per project.

So the overall goal is to have the required, standard, global metrics come from the existing database. Then start to include all of the project-specific data/metrics into an overall data warehouse so there would be a view of total performance for all contractual obligations per individual, date, time period, etc.

Creating tables in my existing database will become too messy (there are over 1,000 projects which each might have different tables)
Creating a separate database for each one, or clustering them logically would be fine but would I still be able to do joins between databases? I would not want to replicate the data from my main database. The productivity data would be used for all projects still.
Is this a case where creating a schema per project (or cluster) would make sense? All projects would relate back to my main database.

The current situation is that each project works in silos. They each have people downloading redundant data and storing it in Excel (probably on their computer which means if they leave or it crashes then the data is lost). There is no single version of truth because things are in Excel or PowerPoint. The goal is to pull everything together and automate some of these tasks out of existence.

Sample of relations (very simplified, we are dealing with hundreds of potential metrics)

Existing data has tables like attendance:

employee_id | date | start_shift | end_shift | duration

Or productivity:

employee_id | date | transaction_count | transaction_time

And a lot of lookup tables:

employee_id | supervisor_name | project_id | project_name | project_category | effective_date | expiration_date | current

The new data could come in a variety of formats and will require a lot of ETL, but at the end of the day we should have the date and employee ID at the very least.

employee_id | date | survey_count | survey_score

So the idea would be to issue reports tying this data together at the lowest granularity (probably daily for most) so end-users could see productivity data and the survey score data in one view.


texts: Can I apply production statistics to an empty local database to test PostgreSQL planner hypotheses?

texts:
I'm toying with the idea of exporting a database's statistics from production and applying them to a test database (same structure, small dataset). The goal is to test software for changes that introduce expensive query plans in a non-production environment before the change goes to production.

I don't see any prior art on this. How might I go about applying statistics from one database instance to another instance, and lock those statistics down to perform this sort of test?


texts: Get count of intersected rows on the same table

texts:
I have the following table:

ID  user_id          skill_id
1   8                1
2   8                3
3   2                3
4   5                3
5   2                1
6   5                2
7   5                4
8   5                5
9   8                5

I need to get the count of users who have both skills 1 and 3 (or more), a real life example would be to get the count of users who know php,mysql,and javascript, but not those that only know php and javascript.

When I do SELECT COUNT(USER_ID) WHERE skill_id IN(1,3) I get a count of 3, which means it's giving me the users with any of them, I need the users who have all?

As a side note I think I could do this intersecting different queries but that would mean a separated query for each tag and there can be endless combinations of skills, something tells me there is a simpler way, speed is really important here.

Thanks.


texts: performance issue on using ip2location for group of ips

texts:
In admin panel I want to show counting users by country. I have in database these tables

CREATE TABLE `cities` (
  `id` int(11) UNSIGNED NOT NULL,
  `country_id` int(11) UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `country` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `ips` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `ip` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ip_city` (
  `id` int(10) UNSIGNED NOT NULL,
  `ip_from` int(10) UNSIGNED DEFAULT NULL,
  `ip_to` int(10) UNSIGNED DEFAULT NULL,
  `city_id` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `cities`
  ADD PRIMARY KEY (`id`),
  ADD KEY `country_fk` (`country_id`);

ALTER TABLE `country`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `ips`
  ADD PRIMARY KEY (`id`),
  ADD KEY `ips_user_id_foreign` (`user_id`);

ALTER TABLE `ip_city`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `city_index` (`id`),
  ADD KEY `idx_ip_from` (`ip_from`),
  ADD KEY `idx_ip_to` (`ip_to`),
  ADD KEY `idx_ip_from_to` (`ip_from`,`ip_to`),
  ADD KEY `city_index_fk` (`city_id`);

ALTER TABLE `cities`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `country`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `ips`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `ip_city`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `cities`
  ADD CONSTRAINT `country_fk` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ips`
  ADD CONSTRAINT `ips_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ip_city`
  ADD CONSTRAINT `city_index_fk` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

I write this query:

select
country.name,
count(*)

from (
    /*get last ips */
SELECT
    INET_ATON(m1.ip) AS ip
FROM
    ips m1
LEFT JOIN ips m2 ON
    (
        m1.user_id = m2.user_id AND m1.id < m2.id
    )
WHERE
    m2.id IS NULL
    ) a
/*end of getting users ips (around 3500) */
inner JOIN ip_city

on ip between `ip_from` and `ip_to`

inner join cities on ip_city.city_id = cities.id
inner JOIN country on  cities.country_id = country.id

group by country.id

but it very slow.
Problem is  in :

inner JOIN ip_city

on ip between `ip_from` and `ip_to`

for every ip it consuming around 1 second.
I found this query

SELECT
    *
FROM
    (
    SELECT
        *
    FROM
        ip_city
    WHERE
        ip_to >= INET_ATON('8.8.8.8')
    LIMIT 1
) AS tmp
WHERE
    ip_from <= INET_ATON('8.8.8.8')

It work very fast but it work only for one ip.
How to combine these 2 querys or how to optimize first query ?


texts: MySQL trigger: if record exists just update

texts:
There is a MySQL database with the following table reviews(reviewer, product, score, title, comment, creation_date, last_modified).
It is needed a trigger before insertion to check if the user has already reviewed the same product. If so, just update the fields score, title, comment and last_modified. This mean no insertion.
Otherwise no operation is needed from the trigger and the review is inserted as usually.

N.B. primary key is bold, foreign key is italic

I tried something myself but I am confident enough to say it does not work:

DELIMITER /
CREATE TRIGGER update_review BEFORE INSERT ON reviews
FOR EACH ROW
BEGIN
IF ((SELECT reviews.reviewer, reviews.product FROM reviews WHERE reviews.reviewer=new.reviewer AND reviews.product=new.product)) THEN
SET old.score=new.score AND old.title=new.title AND old.comment=new.comment AND old.last_modified=new.last_modified;
END IF;
END /
DELIMITER ;


texts: Confusion normalizing data to 3NF and transitive dependencies

texts:
I am currently in a Database class and have some confusion with normalizing to 3NF. I have one specific issue that was not addressed in class but appears in the book problems.

For example I have

R (ABCDEFGH)

AB-->D
B-->C
B-->E
A-->H
H-->G

I have two issues with the problem. First, when decomposing the transitive dependencies of B, should they be split into two tables of BC and BE or a single table BCE.

Second is how do I decompose H-->G. When I remove the partial dependency of A-->H into a separate table AH, the dependency H-->G no longer exists because I already removed H to put it into a separate table.

This is my solution to breaking this down.

R (ABCDEFGH)
R1 (ABCEFGH)  R1(ABD)
R2 (ABEFGH) R2(BC)
R3 (ABDFGH) R3(BE)
R4 (ABDFG) R4(AH)

I have no idea whether I am loosing the H-->G FD by breaking it down like this. However following the steps I had to break apart all the partial FDs before breaking down the transitive one.

Any help would be appreciated on my two issues.


texts: MySQL Why does mysqldump break/just stop? Improve my mysqldump command

texts:
I use the following command to backup my MySQL (with mysqldumpl) two times a day:
/usr/local/mysql/bin/mysqldumpl --lock-tables=false --default-character-set=utf8 -h'host' -u'user' -p'pass' -f -v db266991x2616700 | gzip > /Volumes/Backups/backup.sql.gz

I just found out, that the mysqldump is often incomplete. The file is written, but the file sizes vary. Sometimes the backup file is 20 - 30 % smaller than other backup files.
All files end with -- Dump completed on ..., but they are incomplete and just stop in the middle of creating the insert command for a very large table:
LOCK TABLES `log_mail` WRITE;
/*!40000 ALTER TABLE `log_mail` DISABLE KEYS */;
INSERT INTO `log_mail` VALUES (................................STOPS SOMEWHERE HERE

What could be the reason for that? Could be a reason, that the database is in use and I don't lock the tables?
How can I prevent this? Is there a way to check for errors and repeat the backup in case of an error? It is important that I have a reliable way to backup the database several times a day without checking every backup manually.
It is an InnoDB, so I just found out that might be a better command:
mysqldump --single-transaction --skip-lock-tables some_database > some_database.sql
Is this right? What would be the best/right way?
Before my question was closed on stackoverflow one comment said: "I suggest you redirect stderr to a file and also check the return status code" How would I do that?


texts: Drillthrough action on semi-additive measure group

texts:
I have created a semi-additive (Last Non-empty) measure in a cube that I am developing.  For that measure group I have created a drillthrough action.  When I use that drillthrough action it is returning not just one row for each item as I would expect, but one row for each item for each date, as though it is not recognizing that the measure is semi-additive.

The same issue is mentioned in a couple of Microsoft Connect articles from 2009 here and here, but there is no comment on how to resolve the issue, and I've found very little else on this problem.

I'm using SQL Server 2012 Enterprise.

Is there a solution or a work-around for this problem?


texts: What datatypes can cause "String or binary data would be truncated"?

texts: What kind of datatypes can cause the "String or binary data would be truncated" error? Varchars and other strings are obvious, but what about the "binary data"? Can this include integers or datetimes? Or can this error only be caused by datatypes where you have to specify a length? I could not find any relevant documentation and Google has failed me as well.


texts: Msg 39011, SQL Server was unable to communicate with the LaunchPad service

texts:
We have just installed SQL Server Enterprise 2016 CTP 3.3 on a server that already had SQL 2012 Standard installed.  We installed SQL2016 as a Named Instance and have been trying to get the R integration working, but we are having problems running the external scripts from management studio.

We walked through the Install the R Packages described here: https://msdn.microsoft.com/en-us/library/mt590809.aspx,
And we have manually run the Post-Installation R Configuration steps listed here: https://msdn.microsoft.com/en-us/library/mt590536.aspx

At this point, we are able to connect to SQL from RStudio and manipulate data locally or on the server.  However, we are not able to specify R script in Management Studio.  When we try to run an R script in TSQL, we get the error message:

  Msg 39011, Level 16, State 1, Line 1
  SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.

We have tried several of the sample R scripts including the following:

execute sp_execute_external_script
@language = N'R'
, @script = N' mytextvariable <- c("hello", " ", "world");
                 OutputDataSet <- as.data.frame(mytextvariable);'
, @input_data_1 = N' SELECT 1 as Temp1'
WITH RESULT SETS (([col] char(20) NOT NULL));

execute sp_execute_external_script
@language = N'R'
, @script = N' OutputDataSet <- InputDataSet;'
, @input_data_1 = N' SELECT *  FROM MyData;'
WITH RESULT SETS (([NewColName] int NOT NULL));

execute sp_execute_external_script
@language = N'R'
, @script = N' SQLOut <- SQLIn;'
, @input_data_1 = N' SELECT 12 as Col;'
, @input_data_1_name  = N'SQLIn'
, @output_data_1_name =  N'SQLOut'
WITH RESULT SETS (([NewColName] int NOT NULL));

All of them result in the same error messages about not being able to communicate with the LaunchPad service.

Here are the configuration options we have verified seem to be configured correctly:

We can see that “external scripts enabled” has a value of 1 when running “exec sp_configure”
We can see the Windows User Group “SQLRUserGroupSQL2016” has been created in “Local Users and Groups”
We can see the 20 Windows Users created for R processing in “Local Users and Groups”
We can see that the Database Role “db_rrerole“ was created correctly on master db.
We can see the Extended Stored Procedures “xp_ScaleR_ …” have been correctly created on master db.
We have verified that the SQL Server Service for our Instance is running along with the SQL Agent Service and SQL LaunchPad Service.

They are all logged in as the same Windows Domain User Account, and that user has been added to the Administrators Group for the server.

We have tried restarting the above services many times as well as rebooting the server.
We have tried connecting to Management Studio both using Windows Auth with a Domain Account that is in the Database Role “sysadmins”, and as a local SQL User Account that is in the Database Role “sysadmins”
We manually added those users to the “db_owner” and “db_rrerole” roles on the master db (just to be sure)

Don’t know what else to check…


texts: How to create SQL Server Agent Queued jobs?

texts:
There is a perfmon counter "Queued jobs" in perfmon object "SQLAgent:Jobs".

How to create a job queue that will reflect in this perfmon counter?

Can I get this queued job info from somewhere else like DMV?


texts: Mysql query to fetch records which are not matched form both tables


texts: Slony initial subscription is causing out-of-memory kernel panics. Why?

texts:
Context:

We run Slony 2.0 with Postgres 8.4 on two CentOS 6 servers--one master, and one slave. Our database is about 30GB in size, which isn't unusual, but we do have a couple of tables that are more than 5GB each.

Recently, we needed to re-build our Slony cluster. I turned off Slony, restored identical database snapshots on the master and the slave, set up my slony.conf and slon_tools.conf, started the slons, ran slonik_init_cluster | slonik, then slonik_create_set 1 | slonik (we only have one replication set), and finally slonik_subscribe_set 1 2 | slonik. Everything looked good, and I was able to watch subscription progress in the logs.

Then the server stopped responding. I rebooted it, and saw "Kernel panic - not syncing: Out of memory and no killable processes" after it had killed everything it could.

What I've tried:

First I blew away the database completely, re ran initdb, and then restored the identical snapshots again. Same kernel panic. Then I blew it away, uninstalled Postgres and Slony, and reinstalled them. I double-checked all of our memory-based settings in postgresql.conf, and they are all at stock/recommended levels (i.e. shared_buffers is at 1/4 of RAM etc etc). I ran a VACUUM ANALYZE FULL on the database before initializing the Slony cluster. Same result every time: kernel panic, out of memory.

There's no chance of random/manual config changes having caused this: all of our Postgres and Slony configuration is managed by Puppet, and hasn't changed for months.

Question:

Why is this happening?

Our database has grown fairly linearly over the past few months (at the beginning of the year it was about 23GB, now it's 30), and every other time I have had to re-initialize the Slony cluster on these same servers, it has worked fine.


texts: Sql join with three tables and multiple rows

texts:
I have three tables like this:

    Table Runners:

  This table saves the runner info and the length of miles

ID|Name|BMP  |EMP  |Vendor
1 |Alex|12.25|22.25|Heinz
2 |Abby|0.00 |3.55 |Uni
3 |Marx|5.55 |10.55|Jam

  Table RaceDetails:   This table saves the direction(most races have up
  and down in which case the range switches)

ID|Dir|BMP  |EMP
1 |U  |12.26|12.33
1 |U  |12.33|12.47
1 |U  |21.12|21.99
1 |U  |22.00|22.44
1 |U  |22.15|22.36
2 |U  |0.00 |2.55
2 |U  |2.55 |4.56
2 |D  |4.56 |2.55
2 |D  |2.55 |0.00
3 |U  |5.55 |9.55
3 |U  |9.55 |11.69
3 |D  |11.69|9.55
3 |D  |9.55 |-0.12

  Table RaceSense:

ID|BMP  |EMP  |SENSOR
1 |12.25|20.33|1
1 |20.33|22.25|3
2 |0.00 |3.55 |2
3 |5.55 |9.55 |2
3 |9.55 |10.55|5

I am doing a join between them like this:

Select    distinct *
from      Runners a
left join RaceDetails b
on        a.ID=b.ID
left join RaceSense c
on        a.ID=c.ID

  This is giving me 22 rows because it duplicates data from Racedetails
  because of multiple rows for same ID in RaceSense table

I also tried this:

Select    distinct *
from      Runners a
left join RaceDetails b
on        a.ID=b.ID
left join RaceSense c
on        a.ID=c.ID
and      (b.BMP+b.EMP)/2>=c.BMP
and      (b.BMP+b.EMP)/2<=c.EMP

  This gives me 8 rows not 13 because some of the average fall outside
  the actual range from the RaceSense Table

I want 13 rows with no duplication, Like this:

ID|Name  |BMP  |EMP  |VENDOR|SENSOR
1 |Alex  |12.26|12.33|Heinz |1
1 |Alex  |12.33|12.47|Heinz |1
1 |Alex  |21.12|21.99|Heinz |3
1 |Alex  |22.00|22.44|Heinz |3
1 |Alex  |22.15|22.36|Heinz |3
2 |Abby  |0.00 |2.55 |Uni   |2
2 |Abby  |2.55 |4.56 |Uni   |2
2 |Abby  |4.56 |2.55 |Uni   |2
2 |Abby  |2.55 |0.00 |Uni   |2
3 |Marx  |5.55 |9.55 |Jam   |2
3 |Marx  |9.55 |11.69|Jam   |5
3 |Marx  |11.69|9.55 |Jam   |5
3 |Marx  |9.55 |-0.12|Jam   |2

The way I want to do it is:

1.if the BMP and EMP in Details table are within The Sense table BMP and EMP then that is good.
2.If either BMP or EMP in Details is outside either the BMP or EMP in Sense table then take the average of BMP and EMP in Details and ensure it is within the range of Sense table.
3.If the average value is also out of range then I want to expand the Sense table BMP and EMP by 0.1
4.Otherwise give me a null

Any ideas?


texts: help in correcting the syntax error of the below create table with as with multiple select

texts:
Please help to correct the syntax of the below. I am from a math background trying to use Oracle. The command helps me permute the columns and put them in one table.

create table newsample  AS
(
select(
(select h1.E1,h1.E2,h1.E3,h1.E4 from sample1 h1),
(select h1.E1,h1.E2,h1.E4,h1.E3 from sample1 h1),
(select h1.E1,h1.E3,h1.E2,h1.E4 from sample1 h1),
(select h1.E1,h1.E3,h1.E4,h1.E2 from sample1 h1),
(select h1.E1,h1.E4,h1.E2,h1.E3 from sample1 h1),
(select h1.E1,h1.E4,h1.E3,h1.E2 from sample1 h1),
(select h1.E2,h1.E1,h1.E3,h1.E4 from sample1 h1),
(select h1.E2,h1.E1,h1.E4,h1.E3 from sample1 h1),
(select h1.E2,h1.E3,h1.E1,h1.E4 from sample1 h1),
(select h1.E2,h1.E3,h1.E4,h1.E1 from sample1 h1),
(select h1.E2,h1.E4,h1.E1,h1.E3 from sample1 h1),
(select h1.E2,h1.E4,h1.E3,h1.E1 from sample1 h1),
(select h1.E3,h1.E1,h1.E2,h1.E4 from sample1 h1),
(select h1.E3,h1.E1,h1.E4,h1.E2 from sample1 h1),
(select h1.E3,h1.E2,h1.E1,h1.E4 from sample1 h1),
(select h1.E3,h1.E2,h1.E4,h1.E1 from sample1 h1),
(select h1.E3,h1.E4,h1.E1,h1.E2 from sample1 h1),
(select h1.E3,h1.E4,h1.E2,h1.E1 from sample1 h1),
(select h1.E4,h1.E1,h1.E2,h1.E3 from sample1 h1),
(select h1.E4,h1.E1,h1.E3,h1.E2 from sample h1),
(select h1.E4,h1.E2,h1.E1,h1.E3 from sample1 h1),
(select h1.E4,h1.E2,h1.E3,h1.E1 from sample1 h1),
(select h1.E4,h1.E3,h1.E1,h1.E2 from sample1 h1),
(select h1.E4,h1.E3,h1.E2,h1.E1 from sample1 h1)))

  Error at Command Line:4 Column:49
  Error report:
  SQL Error: ORA-00907: missing right parenthesis
  00907. 00000 -  "missing right parenthesis"
  *Cause:
  *Action:

CREATE TABLE "SYSTEM"."SAMPLE1"
       (    "E1" VARCHAR2(20 BYTE),
      "E2" VARCHAR2(20 BYTE),
      "E3" VARCHAR2(20 BYTE),
      "E4" VARCHAR2(20 BYTE)
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
     NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "SYSTEM" ;


texts: Is there a way to make an OOo form for PostgreSQL updateable view?

texts:
I have a PostgreSQL database, with data from some 8 tables (not counting enums implemented as a table referenced by foreign key) summarized in a view. I made an INSTEAD OF trigger, so the view is in fact completely updateable. I connected the database to OpenOffice Base through jdbc so that I could make forms for it in OpenOffice. There is an option to connect a form to the view as though it was a table, but since the view has no primary key (it can't in PostgreSQL), the view is read-only.

Is there any way to make the form active for the view? Or do I have to decompose the form into one primary and seven sub-forms, so that I could use it to insert data? Or is there any other solution? Or is it not possible to make such a complex form in OOo Base at all?


texts:
we are using percona mysql 5.7 version.
MySQL not releasing the memory after operation complete.
we are very much grateful if anyone can guide us to resolve issue.
Thanks


texts: How to transfer postgres databases from one server to other

texts:
VPS server has old Debian 6 Squeeze with Postgres 9.1
It has 24 databases.
Every night backup copies are created using pg_dump to /root/backups directory for every database.
This directory has 24 .backup files with total size 37 GB.

I installed new VPS server with Debian 10 and Postgres 12.

How to transfer those databases to new server ?

Both server have ssh and root user, postgres port 5432  open,  100 MB internet connection and fixed IP addresses. In night they are not used by users, can stopped during move.

Should I download .backup files and use pg_restore or use pipe to restore whole cluster.


texts:
Environment
Windows Professional 10
MongoDB 3.6.4 2008R2Plus SSL (64 bit)  (before computer crash)
MongoDB 4.0.2 2008R2Plus SSL (64 bit)  (after computer crash)

I have been developing locally, and turned on computer today to blue screen of death.

I had no option but to reset Windows 10 and keep personal files.

I therefore have this directory:

C: > data

which contains two directories, db and log.

Below is contents of directory db:

Below is contents of directory log:

I have downloaded and installed:

MongoDB 4.0.2 2008R2Plus SSL (64 bit)

and I accepted the default installation directories:

Question

How can I restore the databases and collections from the contents of my original mongoDB data directory?

Note - I am on the same computer, after a Windows 10 reset, and new install of MongoDB.

Related Questions

There are similar questions below, but they don't seem to provide an authoritative and accepted answer:

How to restore .wt backup file to local MongoDB?

How to copy wt files from another mongo instance?

https://stackoverflow.com/q/37557319

https://stackoverflow.com/q/32551549


texts:
When I use dbca command as Oracle 11g user I have the following error message:

BASH: DBCA:  command not found

and when I checked the bash profile I found the following:

    # .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
    . ~/.bashrc
fi

# User specific environment and startup programs

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=oracledev-srv.etqint.com; export ORACLE_HOSTNAME
#ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
#ORACLE_BASE=/oracle11gr2/u01/app/oracle; export ORACLE_BASE
#ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
#ORACLE_SID=DB11G; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

PATH=$PATH:$HOME/bin
unset USERNAME
cd $ORACLE_BASE
export PATH

Server information: RedHat Linux 6.3, Oracle installed on the server 11g R2 and 12c.

What I did for investigation:

check the bash file and found out they are correct
navigate to bin file and hit dbca but still the same error message
edit the bash file manually in-case I missed something
reboot the server

And still the same error message.


texts: Cursor to insert .pdf files into a table

texts:
I need to insert all .pdf files from a folder, in a table, according to its number.

I could get a list of files ( I have all .pdfs inside a folder ):

exec master.sys.Xp_dirtree 'The_Folder_where_PDFS_are',0,1

And I could insert it inside a table:

CREATE TABLE PDFTest(NumExample integer,image_data VARBINARY(MAX));

DECLARE @NumExample integer
SET @NumExample = 2456789

INSERT INTO PDFTest(NumExample ,image_data)
SELECT @NumExample ,image_data
FROM OPENROWSET(BULK N'C:\DBA\3937052016_Requisição.pdf',SINGLE_BLOB) AS ImageSource(image_data);

Select * from PDFTest

But as you can see, files are named after a number ( 3937052016 ). This number is the ID of another table, so, if the PDF file starts with 123, I need to insert it into the ID 123. I just would like a start point. I'm trying to create a cursor, looping throug all files, and using left() or substring () function to get the number, inserting it into a variable, and comparing it , like insert this pdf into table where left/substring(pdf_name)=id.

I need something like a string like this :

Insert into tablex ( pdffield )
values ( 123_PdfFIle.pdf ( obviously using openrowset )
 where left/substring(123_pdf to get the pdf's code )) = id`

I'm on the right patch? Is there a way to do this with less efforts?


texts: Why would a Clustered Index Seek return a higher "Actual Number of Rows" than there are rows in the underlying table?

texts:
I'm troubleshooting an issue related to SQL Server (Azure SQL Database technically) occasionally choosing a bad execution plan, presumably due to skewed stats. sp_updatestats fixes it every time, until a few hours or days later when a bad plan gets cached again.

Looking at the "bad" plan, I noticed something that strikes me as odd: there is a Clustered Index Seek on a table that currently has about 1.7 million rows. The "Estimated Number of Rows" for this operation is about 1200, which is definitely in line with the average row count I would expect from that operation in this case, but the "Actual Number of Rows" is in excess of 60 million! Following the fat line from this leaf node, various downstream operations such as joins and sorts are being performed on all 60 million, causing excessive slowness, spills to tempdb, and other badness.

I must be misunderstanding what a Clustered Index Seek actually does, because I wouldn't think it's possible for it to "output" more rows than are in the underlying table. What could cause this? And better yet, any pointers on how to fix it?

[Bonus points for including something like "sp_updatestats fixes it every time but can't figure out how to fix it permanently? Go read this article." This has been a general problem for us on a few different fronts lately.]


texts: how to solve Invalid usage of the option NEXT in the FETCH statement

texts:
I am using jtds 1.2.2 driver with SQL Server 2008. Below is the query I tried to execute

SELECT * from AM_API  FETCH NEXT 10 ROWS ONLY

Anyway after executing above query I got below error

Caused by: java.sql.SQLException: Invalid usage of the option NEXT in the FETCH statement.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:777)
    at org.jaggeryjs.hostobjects.db.DatabaseHostObject.executeQuery(DatabaseHostObject.java:510)

Any idea how to solve this issue?

UPDATE

I have read this that saying FETCH is working on SQL Server 2012 onwards only. Then how to limit result in SQL server 2008. The query going to execute is not under my control. I just postfixed FETCH NEXT 10 ROWS ONLY to the query to limit the result. So is there a way to limit the result by adding a limit query as a postfix to the query.


texts:
I suddenly experienced a query on my production site returning no results but also producing no errors, it was just returning zero rows.  Come to find out a subquery was producing the below error:

Error Code: 1690. BIGINT UNSIGNED value is out of range in '(s.subscriber_count - (s.subscribed_count - s.unsubscribed_count))'

My data set looks like:

I came across https://stackoverflow.com/a/11780905/197606 and haven't actually tried it because I want to understand why this is producing this error.  One comment mentions a negative expression, but there's no scenario in my situation where unsubscribed_count is less than subscribed_count.

Can anyone help me understand why I'm seeing this error?


texts: How to download Oracle Database 11g release 2 for Mac OS

texts: I have been trying to download Oracle Database 11g release 2 for mac os but in the given link by oracle I can only see Linux x86  and Linux x86-64. Is this ok to use this Linux file for installing oracle database in mac


texts: How to set up mysql docker container with existing ib* files?

texts:
Related to a previous issue, I'm trying to recover some data from existing ib* files from a crashed server. The database version is 5.1.69, so quite old. One of the responses was to install MySQL 5.5, because it should still be able to import 5.1, but even that is too old on current systems, and one just runs into ever deeper compatibility issues.
“NBK” in the same issue suggested in a comment to use a docker file with an older version of MySQL. I decided to try this approach. I was able to install docker and pull the vsamov/mysql-5.1.73 image, but I'm stuck now at how to get the ib* files into the docker container.
I think I need the image to run so that it has a container ID, but if it's running, then the ib* files are locked, so I'm not sure how to proceed. If anyone has experience in this, or can provide a reference, it would be much appreciated.


texts: Can a database for teams, activites, markers and outputs be structured without data redundancy?

texts:
There are many TEAMS that engage in ACTIVITIES in a many-to-many relationship with TEAM_ACTIVITIES as a joining table. Each ACTIVITY has a set of MARKERS that are used to track each team's progress in the activity Each MARKER belongs to a single ACTIVITY.

Teams choose which activities they are going to be engaged in, then, on a monthly basis, for each activity they are engaged in they report progress against the markers for that activity. This progress is stored as OUTPUTS.

Each OUTPUT belongs to one TEAM and one MARKER and includes a month and a value.

My problem is that I feel there is some redundancy in this structure, namely that the activities in which each team is engaged is modelled by the TEAM_ACTIVITIES table, but also is in the OUTPUTS table, since that joins TEAMS to MARKERS which belong to ACTIVITIES.

That means to keep integrity I'll have to make sure that OUTPUTS only include entries for team-activity pairs that exist in the TEAM_ACTIVITIES table.

The other way to structure this would be to make OUTPUTS belong to TEAM_ACTIVITIES instead of TEAMS but then the redundancy has just shifted since the activity for an output could come through the TEAM_ACTIVITIES table or through the MARKERS table,

Is there any good way to structure this without redundancy, so that I don't have to do extra checking to ensure integrity?


texts:
On SQL Server 2005 log report, everyday I receive the following message:

I/O is frozen on database databasename.

Is it serious ?
How to resolve it ?


texts: SQL Server - what isolation level for non-blocking select statements?

texts:
I have a long running transaction (called, say, T1) that performs some deletes, updates and inserts on a table in SQL Server 2008 R2. At the same time, another process periodically runs select statements from this table.

Under default isolation settings (READ COMMITTED I think?), T1 blocks any select statements from running until the transaction commits or is rolled back.

What I'd like to see is for the select statements to function on consistent data even while the transaction is underway. I believe SNAPSHOT isolation can help, but am not sure if I'm going in the right direction. Would this be the best isolation level for this application?

Secondly, I don't have any control over the process that is calling the select statements, but I do have control over the .NET application that calls T1. Would any isolation level changes be required on both the select statements and T1, or would it be sufficient to mark  just T1 as having a different isolation level?


texts: Alter Table operations blocks replication in MySQL

texts:
We are using Amazon Web Service's MySQL 5.7.26 with 4 read-only replicas. After some Alter Table operations we have found that the replication stops working. The symtoms are great replica lag that is only solved after rebooting the databases.

The problema has occurred serveral times. Sometimes even with very small tables (1000 records). This is problematic as it reduces our window to do changes that ordinarly would be done during working hours.

P.S: the engine is MySQL Community


texts: Comparing the contents of 2 .nsm (naming standards) files

texts:
I have 2 naming standards files from ERwin (.nsm extension), and I want to compare them to see if they contain the same rules. Is there some sort of automated way to check this?

If there isn't a way to compare the two within ERwin itself, is there a way to convert the 2 files into a more 'standard' file type (i.e. an Excel file), and then compare the files that way? I'm coming from a programming perspective (mostly Java), and from what I've seen it wouldn't be that hard to write a program that compares 2 Excel files.


texts:
I was trying to Configure Log shipping through SSMS (SQL server Management Studio) in between Failover Cluster Instance (Principal Server) and Standalone (Secondary Server), Once I click OK button to finish the Log Shipping configuration, Backup on Primary completed successfully but for Restore to secondary it throws error. I am using existing Drives with required Permissions granted.

Error message:

It means I need to Create a separate folder and assign permissions?


texts:
We have a stored procedure sp_foo that contains dynamic sql and at the end of sp_foo the dynamic sql is executed using EXECUTE (@query)

While analyzing a trace profile i noticed that the Eventsquence (ES), the start and end time do not match my expectation.

Row   EC   ES     Start_Time    End_Time    D_ms  SQL
C     45   1293   44.240        45.603      1363  Select ...
D     45   1294   43.737        45.603      1866  execute (@query)
E     12   1296   43.730        45.600      1871  execute sp_foo @id=34

Questions

Why has E the highest EventSequence (ES=1296), is started first and finishes first?
Why has C the latest start_time, but the lowest EventSequence (ES=1293)

I used this query

Select EventClass as EC, EventSequence as ES
    , Duration/1000 as duration_ms
    , convert(time, StartTime) as [Start_Time]
    , convert(time, EndTime) as [End_Time]
    ....
From TraceImport
WHERE EventClass in (10, 12, 45)

Update to comment on first answer

Gareth points out that my Eventsequence might be running from high to low.
I believe that this is not the case yet

my Eventsequence values are ascending (later start_time == higher ES value)
My ES numbers are in the 2.73E7 range; According to SQL Server Planet Bigint Max Value the maximum value for an Bigint in SQL Server is 8 bytes big from: -9223372036854775808 through 9223372036854775807 If i am not mistaken this would mean that my ES values had to be in the range of 9.2E17 or 9.2E18

Therefore i assume that bigint was not reached yet. We are using 2008R2 Version 10.50.4339.0


texts: What sort of profile would you recommend for an Oracle user that is manifested through a RESTful API; particularly where there will be a lot of asynchronous requests? So we're talking about a potentially large number of simultaneous connections, with a requirement for low latency, but returning relatively short snippets of data.


texts: Error log flooding with aborted connections (Aurora 2.03 - MySQL 5.7.12)

texts:
Here's some contextual information: I have a multi-tenant, WordPress MU installation running on Elastic Beanstalk, scaled up to 2 instances minimum. These are connected to the RDS database and are otherwise functioning correctly. Each WordPress install (tenant) has a database on a shared RDS cluster.

This doesn't happen in Aurora MySQL 5.6, which may be a clue, I just haven't been able to find an answer yet. A couple days ago I began upgrading to Aurora MySQL 5.7. I created a new cluster from scratch, and am importing individual databases for a gradual rollout in case there was any problems.

The RDS cluster has 1 primary instance and 1 read replica, and the error is occurring on both the primary and replica.

Here is a sample of the log:

2018-10-17T11:12:55.418509Z 34783 [Note] Aborted connection 34783 to db: 'unconnected' user: 'unauthenticated' host: '172.31.8.238' (Got an error reading communication packets)
2018-10-17T11:12:59.939953Z 34785 [Note] Aborted connection 34785 to db: 'unconnected' user: 'unauthenticated' host: '172.31.21.224' (Got an error reading communication packets)
2018-10-17T11:13:10.715438Z 34791 [Note] Aborted connection 34791 to db: 'unconnected' user: 'unauthenticated' host: '172.31.8.238' (Got an error reading communication packets)

The IPs are the EC2 instances so the connections aren't malicious as far as I can tell. I have the security group setup to only allow connections from the Elastic Beanstalk application.

I find db: 'unconnected' user: 'unauthenticated' to be pretty odd though.

The databases seem to be working fine but I'd like to resolve this before moving the rest of our clients over.

Please let me know if there's any other information I can provide, and thank you in advance for spending the time.


texts: MSSQL Mirror Configuration Troubleshooting: Witness server forcibly disconnecting every 100 seconds

texts:
I recently set up a three servers running MSSQL Server 2016 in a high safety synchronous mirroring configuration.  Right now, everything is working normally.  Any changes made in the principal are replicated on the mirror consistently.

However, both the Principal and the Mirror server constantly have the error:

"Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://WITNESS_SERVER:5022'."

The Witness server also has the exact same error toward both the Principal and the Mirror.  There are no errors between the Principal and Mirror, and there are no other accompanying errors (Handshake rejected, etc...).  Interestingly, these disconnects occur repeatedly exactly 100 seconds apart without cease.  They do not happen at the same time for the Mirror and Principal, and currently are offset by about 12 seconds.  Has anyone run into a similar issue or know exactly what sort of issue can lead to 100 second forced disconnects?


texts: TempInsertStateItemShort procedure on ASPState is taking more than 1 second to execute

texts:
I'm working on a .NET website who runs in load balance within more than one server and because of that I need to have my session on SQL Server.

In the last 2 weeks I've notice that the procedure TempInsertStateItemShort is taking more than 1 second to run.

Basically this procedure inserts into tempdb.dbo.ASPStateTempSessions

 CREATE PROCEDURE dbo.TempInsertStateItemShort
            @id         tSessionId,
            @itemShort  tSessionItemShort,
            @timeout    int
        AS
            DECLARE @now AS datetime
            DECLARE @nowLocal AS datetime

            SET @now = GETUTCDATE()
            SET @nowLocal = GETDATE()

            INSERT [tempdb].dbo.ASPStateTempSessions
                (SessionId,
                 SessionItemShort,
                 Timeout,
                 Expires,
                 Locked,
                 LockDate,
                 LockDateLocal,
                 LockCookie)
            VALUES
                (@id,
                 @itemShort,
                 @timeout,
                 DATEADD(n, @timeout, @now),
                 0,
                 @now,
                 @nowLocal,
                 1)
            RETURN 0

tempdb.dbo.ASPStateTempSessions table has 800k rows and the PK index is about 98% fragmented.

Does the fragmentation be the reason for insert take more than 1 second?
Does anyone had experience that?
How can I improve performance on this table?

Thanks in advance!!!!


texts: Ops Manager Install in Windows and connect to mongod instance started in linux

texts:
Can we install ops manager in windows and connect to mongodb instance started in Linux operating system? Does ops manager installation on windows require separate installation of mongoDB instance on windows also?
Can we install ops manager on linux and access it from windows using URL?Please guide.


texts: Two-way encryption in PostgreSQL version 9.1 database

texts: What is the process of two-way encryption of data in PostgreSQL database version 9.1 using built-in functions?


texts:
I'm planning to implement this schema in Mongodb, I have been doing some readings about schema design, and the notion was whenever you structure your data like a relational database you must be doing something wrong.

My questions:

what should I do when collection size gets larger than 16MB limit?
app_log in server_log collections gets might in some cases grow larger than 16MB depending how busy the server is.

I'm aware of the cap feature that I could use, but the requirement is store all logs for 90 days.
Do you see any potential issues with my design?
Is it a good practice to have the application check for collection size and create new collection by day / hour ..etc to accommodate log size growth?

Thanks


texts: T-SQL bulk insert fails on specific word "DENY" in string-data

texts:
I have CSV files structured as string ; string ; string
First string is quoted with "". The other 2 strings are not.
I'm bulk inserting them into a table using nchar(x) for each of the 3 columns with a sufficiently large value for x.

BULK INSERT [table] FROM 'import.csv' WITH (FIELDTERMINATOR = ';')

This has been working for a couple of years.
Since a few days the import is giving issues on some rows. After investigation each of the failing rows gets a conversion/type mismatch error on the 3rd column.
The thing that has changed about the data is that in the middle of this 3rd string the text (DENY) is now present in each of the failing rows. This was previously never the case.

 Msg 4864, Level 16, State 1, Line 1
 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1019, column 3 (Rights).

Any ideas why the presence of the substring (DENY) causes this to happen ?
(I know it is a SQL reserved word, but why is it parsed in this position ?)
How to work around it ? I still need those rows to be imported, including that text.

Sample content of column 3:

(I)(OI)(CI)(F)
(I)(OI)(CI)(DENY)(WDAC,WO,S,DC)
(I)(OI)(CI)(F)

The 2nd line gives the error. The other 2 import fine.
(And in case you are wondering: Yes, it is ICACLS output that I am parsing.)


texts: How can I resolve timeout errors in SSIS around midnight every night?

texts:
I find a pair of these in my Application Event Log a little after midnight every night:

  The SSIS Execution Process could not write to the IS catalog:
  MyServerName:SSISDB Error details: Unable to add new messages to the
  table of operation messages.:Timeout expired. The timeout period
  elapsed prior to completion of the operation or the server is not
  responding.; at
  Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ISServerHelper.AppendEventMessage(SqlCommand
  cmd, Int64 operationId, Int32 message_type, DateTimeOffset
  message_time, Int16 message_source, String message_text, Nullable1
  extendedInfoId, String packageName, String packageLocationType, String
  packagePathFull, String eventName, String messageSource, String
  messageSrcId, String subComponentName, String packagePath, String
  executionPath, UInt32 threadId, Nullable1 messageCode, Int64&
  eventMessageId) at
  Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.BasicEventInfo.WriteInternal(SqlCommand
  sqlCommand) at
  Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.Loggable.Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ILoggable.Write(SqlConnection
  conn)

Searching the web for various parts of the error message failed to turn up anything useful.

There is an Agent job that runs SSISDB.internal.cleanup_server_retention_window at midnight every night, and Confio Ignite shows me that it blocks for about 6 minutes every time it runs. It blocks a couple of executions from SSIS ISServerExec of SSISDB.internal.append_event_message with LCK_M_IX waits.

I'm not sure which procedure is timing out, or if the timeout is from something unrelated. Is this a known issue (I'm running SQL Server 2012 SP1 Standard Edition (no CU installed)? Is there a good workaround?


texts: MYSQL 8.0 server not found in Workbench directory in Windows 10?

texts:
I have upgraded my workbench to 8.0CE and also removed the previous 5.7 MYSQL server as well as Workbench 6.3.

This is the directory:

C:\Program Files\MySQL\MySQL Workbench 8.0 CE

Also when I typed the below, I get error message

C:>cd Program Files\MySQL\MySQL Server 8.0.12.0\bin
The system cannot find the path specified.

Now, there used to be a server in the same directory where MYSQL Workbench reside but I just can't find the server file there.

Another strange thing I just discovered is that even though I have upgraded to version 8, the PorgramData is still containing MYSQLServer5.7 !  It is supposed to be MYSQLServer 8.0.12.0 right ?

Please let me know if there is any steps I have missed.

Tks.


texts: 60 minutes to shut down sql server service?

texts:
We recently merged with a new company and they have a server that has 265 DBs on it (6.6tb of mdfs). Shortly after we merged the other DBA said it takes 60 minutes to shut down the services which I thought was incredibly high. Last night they stopped and started the services and sure enough it took 60 minutes. I looked at the log this morning and found the following error messages in the sql log file while it was in the process of trying to stop the service.

I know SQL takes a longer time to start back up if you have a lot of VLFs, but it only took 1 minute from the time it started up until the Recovery Complete message appeared in the log. Any idea if 60 mins is normal? Or if not, what can I do to investigate speeding this up. Thanks!

 Error: 17142, Severity: 14, State: 0.
 SQL Server service has been paused. No new connections will be allowed. To resume the
 service, use SQL Computer Manager or the Services application in Control Panel.

 Error: 18456, Severity: 14, State: 13.
 Login failed for user ''. Reason: SQL Server service is paused.
 No new connections can be accepted at this time. [CLIENT: xx.xxx.xx.xx]


texts:
I am going through the CREATE SYMMETRIC KEY (Transact-SQL) page.

In there it says as a Caution

  When a symmetric key is encrypted with a password instead of the
  public key of the database master key, the TRIPLE DES encryption
  algorithm is used. Because of this, keys that are created with a
  strong encryption algorithm, such as AES, are themselves secured by a
  weaker algorithm.

I do not understand the first part in bold and have a couple of questions.

From my testing I do not see an option to encrypt a symmetric key
with Database Master key.
Isn't the Database master key a Symmetric key.  What do they mean by
    public key of th database master key?


texts: How to insert values into a table from a select query in PostgreSQL?

texts:
I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int).

Now I want to insert a row into items_ver from items. Is there any short SQL-syntax for doing this?

I have tried with:

INSERT INTO items_ver VALUES (SELECT * FROM items WHERE item_id = 2);

but I get a syntax error:

ERROR:  syntax error at or near "select"
LINE 1: INSERT INTO items_ver VALUES (SELECT * FROM items WHERE item...

I now tried:

INSERT INTO items_ver SELECT * FROM items WHERE item_id = 2;

It worked better but I got an error:

ERROR:  column "item_group" is of type integer but expression is of type
character varying
LINE 1: INSERT INTO items_ver SELECT * FROM items WHERE item_id = 2;

This may be because the columns are defined in a different order in the tables. Does the column order matter? I hoped that PostgreSQL match the column names.


texts: What is the .sqlproj.user file, and how is it created?

texts:
I want to move a .sln file to a directory above where it is currently located (because right now it's in a project directory, which isn't semantically correct). The project is called 'pName', and within the project there is a file pName.sqlproj.user.

When i move the .sln file to the parent directory that same .user file is created. Why? is it due to which project is set as a startup?


texts: SQL Server 2008 R2 services not coming online

texts:
I am working for a critical account and yesterday I got a weird issue after applying cumulative update package 14 on top of SQL Server 2008 R2 Standard edition.

Before applying CU-14 package on the server, I stopped the DB Engine services from Configuration Manager. I started the patching and this finished successfully without any issue and I rebooted the machine too.

The thing is that Services were not coming online even if it was in automatic mode. I started the services and tried to connect and I got an error message:

  Server is in script update mode

So I waited for some time because it is in update in progress. After that when I connected to that instance I was getting

  network related instance specific error

And interestingly SQL Server services was stopped automatically, again I was trying to start the services and the same scenario is happening repeatedly and I even uninstalled the CU-14 package from the server as well.

As a workaround I included "-T902" flag at the end of the parameters from config manager and started the services successfully and I am able to connect. But running the service by using the flag is not a good workaround.

Still I am experiencing the issue and please suggest: What are the steps we have to perform to resolve this issue?

Thanks for understanding!


texts: select specified number of unique IDs where second column is unique

texts:
Look at the following example starting from the top row (id=9) and work your way down, selecting a limit of 4 rows that have sec's that we have not yet seen. We "select" id=9 because we don't yet have sec=1. We continue to work our way down like this, but when we get to id=7 we skip it because we already have sec=5 (from row with id=8). We continue in the same manner, and we finally stop at id=3 because we have accumulated 4 rows (our desired limit).

 id | sec
----+-----
  9 |   1  <- 1
  8 |   5  <- 2
  7 |   5  # skip, already have sec=5
  6 |   4  <- 3
  5 |   1  # skip, already have sec=1
  4 |   1  # skip, already have sec=1
  3 |   3  <- 4
  2 |   2
  1 |   1

Of course the SQL algorithm can (will!) be different than I described.

Desired result:

 id
----
  9
  8
  6
  3
(4 rows)

If I wanted to increase the limit to 5 rows, then the row with id=2 would be included in the results. However, if I increased the limit to 6 rows, the row with id=1 would not be added because sec=1 has already been seen.

Note: Though it shouldn't matter, I am on PostgreSQL 9.3.1.

In case you want to quickly build the table to test this out:

CREATE TABLE my_table (id serial primary key, sec integer DEFAULT 0 NOT NULL);
INSERT INTO my_table (sec) VALUES
  (1)
, (2)
, (3)
, (1)
, (1)
, (4)
, (5)
, (5)
, (1);
CREATE INDEX index_my_table_on_sec ON my_table (sec);


texts: Adding a column and new foreign key constraint in one line

texts:
Let's say we have a table called Salesman with the folling properties

Create Table SalesMan(
  EmplyeeId int Primary Key,
  Name varchar(20) Not Null,
  LastName Varchar(20) Not Null,
  Email Varchar(40) Unique);

and a simple table definition for "Order"

Create Table Orders(
  Number Varchar(20),
  Quantity Int,
  DealerCode Varchar(20),
  ModelName Varchar(20),
  ModelVersion Varchar(20));

At this point I forgot to add a One to Many relation between Order and Salesman so that a SalesMan can have many orders and Orders can have only one Salesman

Alter Table Orders Add sm_fk int;
Alter Table Orders Add Constraint Sales_fk Foreign Key (sm_fk) References SalesMan(EmplyeeId)

Now my question is: could I have simplified the query by omitting the second alter table query and compress it to

Alter Table Orders add sm_fk int References SalesMan(EmplyeeId)

Would this be the same as the second Alter Table query?

Thank you for your time!


texts: MongoDB --- Failed global initialization: Failed to open "/var/log/mongodb/mongod-config.log"

texts:
I'm trying to setup the config servers for mongodb sharding. I created a specific config file that is set to log to /var/log/mongodb/mongod-config.log.

When I run mongod --config <path-to-config-file>, I get this error:

`F CONTROL  [main] Failed global initialization: FileNotOpen: Failed to open
"/var/log/mongodb/mongod-config.log"`

When I run the same command but with sudo, it runs but there's no output.

But then when I try starting the mongo shell at the port (27019) specified by my config file, I get this:

MongoDB shell version v3.6.4
connecting to: mongodb://127.0.0.1:27019/test
2018-05-01T03:13:13.399+0000 W NETWORK  [thread1] Failed to connect to
127.0.0.1:27019, in(checking socket for error after poll), reason: Connection
refused
2018-05-01T03:13:13.400+0000 E QUERY    [thread1] Error: couldn't connect to
server 127.0.0.1:27019, connection attempt failed :
connect@src/mongo/shell/mongo.js:251:13
@(connect):1:6
exception: connect failed

I'm just following the MongoDB sharding documentation

I've seen some of the previous posts on here and one was the same problem. No solution there, but I want to try again and see if anybody has a solution to this.

Not sure if I missed something or what the issue is...


texts: Can't connect to a PG database from MicroOLAP, but can connect from SSH?

texts:
I'm able to connect to my PGSQL database from SSHv2.

But when I try to do the same thing on MicroOLAP Database Designer, I get this message:

(Note: From my college PC, I can connect. But I can't connect from home.)

My configuration:

Here's me on arka.foi.hr via SSHv2 (accessible from home):


texts: How to Manage CPU Usage in Oracle Database 11g using Instance Caging?

texts: I am doing a little test of the cpu utilization case, got to know that i can use Instance Caging for the same to improve my resource utilization. Can somebody let me know how to do this? i m using oracle 11g.


texts:
What could be causing (and how to fix) my tempdb files (8 equal size) to have [avg_write_stall_ms] of 7300ms and the next  highest data file is 47ms.

System is Hyper-V fail-over cluster, SQL 2012 Standard, 60GB RAM, 20 vCPU, EqualLogic PS6100 SAN

Here's my results from the Glen Berry tools

I've requested that the tempdb data files be moved to their own drive letter, but am not sure that will help because it would be the same SAN.


texts: dropping a subscription in the subscriber first, how to fix it?

texts:
I was looking for dropping a subscription, however, I made the mistake of dropping it in the subscribe, without dropping it at the publisher first, ignoring the warning from the picture below.

  TITLE: Microsoft SQL Server Management Studio

  This action will remove the selected subscription information, but not
  the previously replicated data, from database 'MY_DATABASE_Sub'.
  Removing the subscription information should be done only if the
  subscription has been deleted at the Publisher, MY_PUBLISHER, or if the
  subscription is otherwise defunct. Removing an active subscription
  will result in errors at the Publisher.

  Are you sure you want to remove the subscription information?

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.0.17119.0&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CleanUpPushSubscriberConfirmation&LinkId=20476

  ------------------------------ BUTTONS:

  &Yes &No

that is causing me the error below (in yellow), basically the subscription is showing up in the replication monitor, but it should not be there at all.

Question is:
How can I fix this error? Basically get rid of the ghost subscription showing up in the replication monitor and causing it become red flagged?


texts:
I'm developing an application with many, dinamycally-assembled, complicated SQL queries. I'd like to keep the query plans in the procedure cache for as long as it is possible. To prevent discarding the query plans from the procedure cache (because of memory pressure), I'd like to allocate enough memory to the procedure cache. According to this only 10% of the "visible target" memory in the 4Gb-64GB range is used for the procedure cache; I'd like this rate to be higher.

Is there any way to have some control on the size of the procedure cache? SQL Server 2008 or 2012; maybe some Enterprise Edition features?

Also, does this "10% of the visible target memory" rule also apply, if I explicitly set the min. memory for the SQL Server?

(I am aware of the KEEP PLAN / KEEPFIXED PLAN hints; I'm also aware of the advatages of preparing the statements over ad-hoc queries, so please don't recommend these possibilities. I'm specifically curious about controlling the procedure cache size.)


texts:
I am developing a desktop application and it will include clients, bills, banks, incomes&outgoings etc,

I am using mysql and delphi,

My question is do i have to cycle (create new tables and transfer needed data from old tables to new tables for new year) every year because i think database will have data for a year and it will adventually get slow, is there a way/is it possible to continue without cycle but without database get slow?

I want to do this because i want user be able to find/see client cards and bills of old years too

Thanks


texts:
I am getting an Error on Slave as:

  could not execute Write_rows event on table mydatabasename.atable; Duplicate entry '174465' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000004, end_log_pos 60121977

As I am directly getting updates from master so why this happens.

How can we handle this in an effective manner so that there is no data loss on the slave.I do not want to set up the whole replication again for maintaining the data integrity.

Thanks..!


texts: I need to design an ERD with UML notation, can anyone recommend a tool that does this.


texts: Database ER diagram review. Is this a good database design ? [HELP]

texts:
This database For school management system.
Please review and help me with your ideas.


texts: Choice of authentication approach for financial app on PostgreSQL

texts:
First some background.

The LedgerSMB project is an open source financial accounting software project that runs on PostgreSQL.  We implement a very large amount of business logic in user defined functions, which act as the main mapping tool between program object methods and database behavior.  Currently we use database users as authentication users, partly by choice (this allows centralized security logic, so that other tools can be written and re-use permissions given to users), and partly by necessity (after we forked from SQL-Ledger, there weren't many options for retrofitting security onto that codebase).

This buys us access to a reasonable number of single signon options that PostgreSQL has access to, from LDAP to Kerberos 5.  We can even use PAM where passwords are concerned.  It also allows us to re-use permissions when integrating with other applications, or allowing other client interfaces.  For a financial accounting application this seems like a net win.

There are obvious costs involved.  For the web application, we are very limited to the types of http auth that can be supported.  DIGEST for example is entirely out.  BASIC works, and we could implement KRB5 easily enough (I plan on having this supported and working out of the box for 1.4).  Very strong authentication measures cannot be properly managed on this directly although we could probably shim them in if necessary (for example BASIC + client-side SSL cert with a cn matching the user name and a specific root ca).

At the same time we've run into a fair amount of criticism mostly from the development crowd and more occasionally from dba's who tell me that the application should be the security barrier, not the database.  My view is still that a smaller security perimeter is generally better, that the re-use of business logic and security logic go together, and that it strikes me as dangerous to re-use business logic without re-using security logic on the same level of the program.

Am I missing any major tradeoffs here?  Are there gotchas that I am not considering?


texts: Estimate number of rows in query results

texts:
Given the relation:

donate(donor, type, amount, candidate, party, donate-date)

N(donate) 21,000 - total rows
V(party) = 30 - number of different parties
V(type) = 50 - number of different donation types
V(candidate) = 200 - number of different candidates

How to estimate how many rows would the following query yield?

SELECT candidate
FROM donate
WHERE party="sky" AND type "shoe"

How can I use those details to infer the average number of results?

I can tell that the upper limit is 200 as that is the number of total candidates.
I can tell that there are on average 21000/200 = 105 donations to every candidate.
There are on average 21000/30 = 700 donations to every party.
Not really sure how to proceed.


texts: SQL Server 2008 R2, error creating file for FileStream

texts:
I'm trying to create a file to add to a filestream file group and I get the error:

  Msg 5120, Level 16, State 106, Line 1
  Unable to open the physical file "d:\data\filestreamstore.mdf". Operating System Error -1073741790: "0xc0000022(failed to retrieve text for this error. Reason: 15105)".

I am running the query connection as administrator and I have full privileges on the drive and folder in question.

In the ERROR log I see:

  Error: 17207, Severity 16, State:1.
  STREAMFCB::Startup: Operating system error 0xc0000022(failed to retrieve text for this error. Reason: 15105) occurred while createing or opening file 'd:\data\filestreamstore.mdf'. Diagnose and correct the operating system error, and retry the operation.
  Error: 17204, Severity 16, State 1.
  FCB::Startup () failed: Could not open file d:\data\filestreamstore.mdf for file number 65537.  OS errof: 0xc0000022(failed to retrieve text for this error. Reason: 15105)

These identical errors are found in the Application log, with no more details.

Has anyone seen this before?


texts: SQL Xml returning empty root tag on empty record set

texts:
I am working on xml explicit to generate user defined nodes in xml output using Sql Server 2005 Express edition. The below code works fine, just that i couldn't manage to generate a empty  node when no record set exists in the Users table.

CODE:

select 1 as tag, null as parent,

FirstName as [User!1!FirstName!Element],

LastName as [User!1!LastName!Element]

FROM Users

FOR XML EXPLICIT

OUTPUT:

<Users>
<FirstName>Ammy</FirstName>
<LastName>Dammy</LastName>
</Users>
<Users>
<FirstName>Lammy</FirstName>
<LastName>Lolly</LastName>
</Users>

Expected Output on Empty table:

<Users></Users>

Note: I don't like the xsnil approach, all other approaches to solving the problem is welcome


texts: How to completely "reset" an Oracle instance?

texts:
I'd like to drop all entities from an Oracle instance. I want to delete all tables, users, indices and so on, just like as if it would be a freshly created instance. The result should look, or at least closely approximate from the viewpoint of the clients, as if I had completely deleted and then reinstalled the instance with the same settings.

Doing this with a complete wipe/reinstall is slow, and it is not easily automatizable. I am looking for better options, but it is a possible solution, if there isn't.

Dropping the schemas is not enough, because some entities still remain in place (for example, users).

Is there an easy way to drop everything, to reach the initial clean and empty state, but more quickly?


texts:
I'm having a problem in creating spfile from pfile.I think I don't have permission to write on Oracle Home. I'm using Windows 8

SQL> startup nomount pfile='initlittle2.ora.txt';
ORACLE instance started.

Total System Global Area  644468736 bytes
Fixed Size                  1376520 bytes
Variable Size             192941816 bytes
Database Buffers          444596224 bytes
Redo Buffers                5554176 bytes
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file
'A:\ORACLEBASE\PRODUCT\11.2.0\OLTP2\DATABASE\INITLITTLE2.ORA'

Any workaround for my problem?


texts: Dynamic pivot table filtering and performance


texts: Trying to run a shell script from dbms_scheduler . Throws error no matter what the script is?

texts:
I am trying to run a shell script from a dbms_scheduler but am getting a bunch of errors :-

the job defn :-

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
     job_name           => 'test_prog',
     job_type           => 'EXECUTABLE',
     job_action         => '/export/home/shell_script.sh',
     enabled            => TRUE
  );
END;

and shell_script.sh is :-

echo 'hello' >> a.txt;

and I get these errors :-

EXEC DBMS_SCHEDULER.run_job('test_prog');
BEGIN DBMS_SCHEDULER.run_job('test_prog'); END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [],
[], [], [], []
ORA-06512: at "SYS.DBMS_ISCHED", line 154
ORA-06512: at "SYS.DBMS_SCHEDULER", line 450
ORA-06512: at line 1


texts: Upgrading Debian version of PXC production cluster

texts:
I have a 3 node PXC 5.7 cluster behind a ProxySQL instance. The cluster is still running on Debian 8.

I have setup recently a testing PXC 5.7 cluster with a copy of the database on Debian 10 without issues, but now I would like to upgrade the production cluster as well.

What would be the recommended way of upgrading the PXC cluster with a minimum of downtime?
I was thinking of setting up 3 new nodes on Debian 10 and adding them to the production cluster but not adding them to the ProxySQL. Once they have all the data distributed I could downgrade the Debian 8 nodes and let the Debian 10 nodes take over in ProxySQL. After that I could remove the Debian 8 nodes from the cluster.

Another option would be to do an inplace Debian version upgrade, but that's always scary as F.


texts: Error run pgAdmin4 Fatal Error: The pgAdmin 4 server could not be contacted

texts:
I had installed PostgreSQL 10 and everythings perfect after i install but now i have this issue.

Fatal Error:
The pgAdmin 4 server could not be contacted

anyone know what could be the problem?