texts: Best way to compare strings using pre-built ngrams?

texts:
I'm struggling with an n-gram problem and have been hunting the Web for examples to help me out and have noticed n-grams being discussed on this forum. I'm working on a system where we've got scraped titles and are trying to reduce the number of unrecognised texts. There are roughly 60,000 known good titles and about 6,000,000 unmatched titles. 1:100 is not a very impressive ratio. Some of the bad titles aren't in the list of canonical titles and can't ever be matched, but others are failing to be matched because of punctuation/positional/editing/spelling inconsistencies and errors. Typical dirty data.

To avoid confusion I'll clarify what I mean by n-gram as the term is used for two different things. I'm talking n-grams where you break a string into a series of n-length substrings. These are sometimes called "q-grams", for some reason. I'm not talking about something like Google's corpus of word combination "n-grams". So, with a length of 3 for n, the string "hello" breaks down into:

__h
_he
hel
ell
lo_
o__

(The padding before and after isn't always used but, based on my data set, I think that it will be helpful.)

There is a plug-in for MySQL that parses texts into 2-character blocks, but I'm very likely to need lengths of 3 and possibly other lengths. Testing will show what's most effective.

Despite being a programmer for a long time, I'm poor enough at SQL that I haven't sorted out how to build the n-grams or how to rank similarities based on the n-grams. If it comes to it, I can build the n-grams in another language without any trouble and load them all into MySQL. That leaves me with needing to figure out how to construct the SELECT to compare strings.

Do you happen to have or know of an example that you might be able to refer me to?

For background, the rest of the team has been trying to solve this using some code I provided with a variety of algoritms, phonetic and statistical:

Soundex and metaphone:
Not really appropriate for this data (too many languages) and not great on a good day anyway.
Levenshtein distance, longest common subsequence, and Jaro-Winkler:
Much higher quality, but too expensive to run on-the-fly for a data set of this size.

Thanks in advance for any help or suggestions.


texts: Mariadb is giving warning for this query and table

texts:
Please create the following table,

CREATE TABLE test3 (

  meta_key varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  meta_value varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO test3 (meta_key, meta_value) VALUES
('utm_source', 'facebook'),
('lead_date', '20180915'),
('utm_source', 'facebook'),
('lead_date', '20180905');

then run the following query

select meta_value
from (select meta_value
      from test3
      where meta_key='lead_date') as q0
where date(meta_value)<CURDATE()   ;

you will get following warning while running them (use adminer to see them)

Level   Code    Message
Warning 1292    Incorrect datetime value: 'facebook'
Warning 1292    Incorrect datetime value: 'facebook'

Ideally, this should not have happened. Can someone explain what's wrong here?

Update
This query is actually part of a bigger set of queries. It has been reduced to this level to highlight the warnings.
The issue is not with CURDATE. if you write date(meta_value) is NOT NULL it will give warnings.
In fact, it will give warnings with all date related functions in where clause.

It does not seem that it could be an optimization issue.
Try the following queries with a join. At this point, the optimizer cannot optimize since I am comparing with another table.
It will still give a warning.

drop table if exists test3;
CREATE TABLE test3 (

  meta_key varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  meta_value varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO test3 (meta_key, meta_value) VALUES
('utm_source', 'facebook'),
('lead_date', '20180915'),
('utm_source', 'facebook'),
('lead_date', '20180905');

select meta_value
from (select meta_value
      from test3
      where meta_key='lead_date') as q0 , (select date('20200101') as
compare) as d
where date(meta_value)<compare  ;

Now try another query:

select meta_value
from (select meta_value
      from test3
      where meta_key='lead_date' limit 1000000) as q0
where date(meta_value)<CURDATE()  ;

This will not give an error.


texts: Optimising BCP performance for BLOB data

texts:
I'm the process of planning the live migration of a 2TB database to partitioned tables. The system is broadly speaking a document store, the majority of the space being allocated to LOBs of between 50kb and 500kb, with a small percentage in the 500kb to 1MB range. Part of the migration will involve BCPing data from the old to new database.

BCP is the preferred approach as the current/historic divide in the data permits extracting the older data in stages (during quieter periods) in advance of a final switch, minimising impact on the live system. The volume of data and availability of storage precludes an in-situ rebuild to a partition scheme.

I'm suspect there maybe some performance gains to be had by experimenting with KILOBYTES_PER_BATCH rather than ROWS_PER_BATCH, due to the BLOB content. It's suggested in the BCP documentation that SQL can optimise the operations based on this value.

What I can't find is any guidance on the nature of these optimisations or where to start my testing. In the abscence of suggestions I'll try short runs at 4/8/16/32/64mb boundaries to start.

Probably some gains to be from changing the packet size (BCP -a parameter, rather than server level setting) but I'm inclined to bump this to the maximum 65535 unless anyone has a more formulaic approach.


texts:
If I have a cluster and I pause the SQL Server Service on the active server with the cluster interpret this as a failure and attempt to fail-over?

I tried this on a failover cluster test on my laptop (Windows 2008 Enterprise eval running SQL Server 2008 R2 SP1), open SQL server configuration manager and stop SQL server service on the (active) node wait for couple of minutes but somehow SQL server db service did not failed over?


texts: Postgres insert query max length or number of VALUES parameters

texts:
I'm trying to find a way to speedup population of my db.

I'd like to use COPY as suggested here, but I have only access to my db, not to the entire server. All other tips worked good for me, but unfortunately I need another speedup now.

So now I'm using just an INSERT ... VALUES without autocommit or any constraints.
Maybe I'm totally wrong, but I noticed that when I'm trying to execute lesser number of queries with more values in VALUES, population goes faster.

So I'd like to know what is the query length limit (or VALUES parameters limit).


texts: How to improve SELECT performance on a big ORACLE view?

texts:
I cannot use a materialized view for the base table data changed frequently. There are about 0.6 million rows in the_big_view. The query I need to do is: SELECT * FROM the_big_view WHERE student_number='*****', which returns arbitrary rows of data.

If I add an "ORDER BY student_number" clause in the "CREATE VIEW the_big_view AS ...", does that do good?


texts: Upserting data with existing many-to-many connections

texts:
This is a strategy question - but one I've been grappling with all week and could really use some wisdom-as-a-service.
Context
I am trying to piece together a clean, actionable PostgreSQL database with sanitized records from a ton of messy, noisy raw source tables.
Say we have contacts, companies, stores. Many contacts can work at a company. Multiple companies can be involved in a store (owner, maintenance, etc.) There are many-to-many (M:N) junctions between contacts_companies and companies_stores.
Table relationships:
contacts >-< contacts_companies >-< companies >-< companies_stores >-< stores

unique on: contacts:email, companies:name, stores:formatted_address

The multiple data sources have all sorts of overlaps between the above tables. Some have just store info, some have some contact and store info, etc. As I go through each data source, data will be cleansed and then upserted to the appropriate table.
Problem
The upsert operation is the problem. Inserting with an ON CONFLICT is fine for a specific table, but what about the junctions between?
To illustrate the problem, say we have a data source that has the following records:
{owner: "Alice A.", email: "A@Alice_stores.net", store_address:"1 main st"}

This record has

a contact {name: Alice A, email: A@Alice_stores.net},
a company {name: Alice Stores}
a store {address: 1 main st}

The database might already have a store for 1 main st, but not a contact/company for Alice.
If we individually upsert the contact, company and store, we won't know what proper IDs to place in the junction tables:

If INSERT, use new id for FK in junction.
If Update, use existing id for fk in junction.

Question
What is the best strategy for upserting multiple tables from various sources when there are M:N relationships across the board?
One strategy that occurred to me is to query all tables/junctions, hold them in memory and perform all conditionals/updates in memory and then have a more forceful upsert. But this can quickly get out of hand...


texts:
Firstly I would like to apologies if this is not the correct place to ask this question but I know how good community spirit is around here from my time in Stack Overflow.
I am very new to the world of databases but I am looking to create an application that will use a database in the background to hold data.The application is going to be an action plan creator and will hold a lot of data but I have no idea how to design my database. Therefore I am looking for some help.
I will be using MySQL for the database and Java for the application.
Design:
I tried to type out how I would like the design to be but ended up confusing myself so guessed that I would have no luck explaining on here. Therefore, here is an image of what I will be trying to achieve for each action plan:

I hope this helps but to try and explain further, each action plan would have one of these (I am aware that I would have to run some kind of expression to get this) and each time the action number would start from 1. Although I have only added 3 updates in the example above, each action number would have to be able to expand to theoretically unlimited amount.
Reading the tutorials I have spotted so far, I know that this requires some kind of relational database. However, I am not able to get my head around how to set this all up.
I know this is a BIG ask but I would be very grateful for any advise anyone has to offer and I am eager to learn more.


texts: How to apply a condition on "count over partition"?

texts:
I'm using Oracle 11g.
Assuming multiple consecutive entries (sorted by datetime) with the same place_id represent a single visit.

With this table:
datetime_id | user_id | place_id

and I want to get
user_id | first_place_visited_id | second_place_visited_id | third_place_visited_id.

I tried this:

select * row_number() over ( partition by user_id,place_id
                                   order by datetime_id desc ) rn
          from data_table

Before I pivot the result to get one raw for each user, I face a problem.
Let's assume that some user has this kind of data

datetime_id    | user_id | place_id
   1           | 1       | 1
   2           | 1       | 1
   3           | 1       | 2
   4           | 1       | 2
   5           | 1       | 1
   6           | 1       | 2
   7           | 1       | 2
   8           | 1       | 3

The analytical function here will partition the data into windows as following:

   date_time_id| user_id | place_id   rn

1st window:

   1           | 1       | 1          1
   2           | 1       | 1          2
   5           | 1       | 1          3

2nd window:

   3           | 1       | 2          1
   4           | 1       | 2          2
   6           | 1       | 2          3
   7           | 1       | 2          4

3rd window:

   8           | 1       | 3          1

How can I apply a condition such as Having in group by clause, such that
I can force datetime_id to have consecutive values inside each window, i.e I want something like this:

   date_time_id| user_id | place_id   rn

1st window:

   1           | 1       | 1          1
   2           | 1       | 1          2

2nd window:

   3           | 1       | 2          1
   4           | 1       | 2          2

3rd window:

   5           | 1       | 1          1

4th window:

   6           | 1       | 2          1
   7           | 1       | 2          2

5th window:

   8           | 1       | 3          1


texts: Why OUTER APPLY forces scanning the entire table?

texts:
I have a query which does not performs well and optimizer scans all 108864 items from table ExternFile uselessly when seeking only 30 rows would be fine. I have simplified the problem to following select from view.

The upper layer query looks like:

SELECT
realtyId,
ServerPath,
OfferState,
CategoryId,
ParentCategoryId
FROM FSTest
WHERE OfferState <= 3 AND (CategoryId = 2 OR ParentCategoryId = 2)
ORDER BY Ranking DESC
OFFSET 2970 ROWS FETCH NEXT 30 ROWS ONLY

The underlaying view this is following:

CREATE View [dbo].[FSTest] AS
SELECT
realty.Id as realtyId,
realty.OfferState,
realty.Ranking,
Category.Id as CategoryId,
Category.ParentCategoryId,
ExternFileOuterApply.ServerPath as ServerPath,
ExternFileOuterApply.Height as ImageHeight,
ExternFileOuterApply.Width as ImageWidth
FROM realty
JOIN Category ON realty.CategoryId = Category.Id
OUTER APPLY (SELECT TOP (1) ServerPath, Height,Width FROM ExternFile WHERE ForeignId =  realty.Id AND IsMain = 1 AND ForeignTable = 5) AS ExternFileOuterApply

The problem is the OUTER APPLY. I would expect that the query skips all 2970 rows and execute the OUTER APPLY only for 30 rows which are contained in the result. But the OUTER APPLY is scanning entire table ExternFile (108864 rows). Why?

Is it somehow connected with temporary index in the index spool (see image bellow)? What is wrong with the query?


texts: Cannot drop column : needed in a foreign key constraint

texts:
I have a table with two foreign key constraints as below:

mysql> SHOW CREATE TABLE `user`;

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `region_id` int(11) unsigned DEFAULT NULL,
  `town_id` int(11) unsigned DEFAULT NULL,
  `fullname` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `username` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `FK_G38T6P7EKUXYWH1` (`region_id`),
  KEY `FK_J8VWK0ZN7FD2QX4` (`town_id`),
  CONSTRAINT `FK_G38T6P7EKUXYWH1` FOREIGN KEY (`region_id`) REFERENCES `region` (`id`) ON UPDATE NO ACTION,
  CONSTRAINT `FK_J8VWK0ZN7FD2QX4` FOREIGN KEY (`town_id`) REFERENCES `town` (`id`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I can't drop a foreign key column although I disable FOREIGN_KEY_CHECKS.

mysql> ALTER TABLE `user` DROP COLUMN `region_id`;
1553 - Cannot drop index 'FK_G38T6P7EKUXYWH1': needed in a foreign key constraint

mysql> SHOW VARIABLES LIKE 'FOREIGN_KEY%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set

mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected

mysql> SHOW VARIABLES LIKE 'FOREIGN_KEY%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set

mysql> ALTER TABLE `user` DROP COLUMN `region_id`;
1828 - Cannot drop column 'region_id': needed in a foreign key constraint 'FK_G38T6P7EKUXYWH1'


texts: Contained User on View - Select permission denied on object 'table1', database 'Database1', schema 'test'

texts:
As an example, I have a database called 'Database1' on Azure SQL Server 12.0.2000.8
I needed to setup an account that could only access a view in the database (eg. View1).
This was achieved by the following:
CREATE USER [testuser] WITH PASSWORD 'P@ssword1!'
GO
GRANT SELECT ON [dbo].[View1] to [testuser]
GO

This worked perfectly. I was able to connect to the Database and only see the view and not the related tables.
My colleagues then utilized this method to create additional user accounts with read access to other views.
Recently the user accounts have been unable to access the views with the following error msg.

"Select permission denied on object 'table1', database 'Database1', schema 'test'"

I have checked that there is no deny permission against the accounts.
If I grant select on the specified table (table1), the issue is resolved, however this enables the user account to view the table contents which I would like to avoid.
Any assistance in troubleshooting this would be appreciated.


texts: how to import an existing database into SQL server 2012?

texts:
I am brand new to SQL, SQL Server 2012 and SQL Databases. I have an example database already created for me that I want to import into my SQL Server.

I notice that there is a Books_log with it too. What is it? (I appreciate your answer but not my main question tho).

I've tried importing it by using SQL Server Management Studio. I created a new database, then I right-clicked it and selected Tasks, then Import Data, then the Import and Export Wizard Opened, then I gave it my best guess and selected Flat File Source as my Data Source, then I clicked on Browse and selected the Books.mdf database. Then at this point I feel like there is something wrong because when I moved to the Columns or Preview tab this is what it looks like:

Anyways, I clicked on Next. I chose the .Net Framwork Data Provider for SqlServer as my Destination. Then clicked on Next and this is what I got:

What am I doing wrong?


texts: How to Export Table sql to Excel file in sql server 2014?

texts:
I want to export tbl_category to excel file with code. I can export with the wizard but I want to create procedures to create excel file daily.
tbl_category : 2 column contain : [id] is int and [category] is nvarchar(max)

code:

INSERT INTO OPENROWSET

('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\template.xlsx;HDR=YES;IMEX=1','SELECT * FROM [Sheet1$]')
SELECT [id]
      ,[Category]
  FROM [dbo].[TBL_Category]

windows 7 32 bit and 64bit.
sql server2014.
ms office 2010.

error:

  Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

1


texts: Postgresql BDR problem compiling pluging

texts:
I'm trying to build deb packages for postgresql BDR plugin, but I'm getting the following error:

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Werror=format-security -I/usr/include/mit-krb5 -fPIC -pie -DLINUX_OOM_SCORE_ADJ=0 -fno-omit-frame-pointer -fpic -I/usr/include/postgresql -I. -I./ -I/usr/include/postgresql/9.4/server -I/usr/include/postgresql/internal -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/tcl8.6  -c -o pg_resetxlog.o pg_resetxlog.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Werror=format-security -I/usr/include/mit-krb5 -fPIC -pie -DLINUX_OOM_SCORE_ADJ=0 -fno-omit-frame-pointer -fpic pg_resetxlog.o -L/usr/lib/x86_64-linux-gnu -Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now -Wl,--as-needed -L/usr/lib/mit-krb5 -L/usr/lib/x86_64-linux-gnu/mit-krb5  -Wl,--as-needed  -L/usr/lib/x86_64-linux-gnu -lpgcommon -lpgport -L/usr/lib/x86_64-linux-gnu -lpq -lpgcommon -lpgport -lselinux -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm  -o bdr_resetxlog
pg_resetxlog.o: In function `memcpy':
/usr/include/x86_64-linux-gnu/bits/string3.h:51: undefined reference to `pg_crc32_table'
pg_resetxlog.o: In function `RewriteControlFile':
/var/lib/postgresql/src/bdr-plugin/pg_resetxlog.c:955: undefined reference to `pg_crc32_table'
pg_resetxlog.o: In function `PrintControlValues':
/var/lib/postgresql/src/bdr-plugin/pg_resetxlog.c:749: undefined reference to `pg_crc32_table'
collect2: error: ld returned 1 exit status
make: *** [bdr_resetxlog] Error 1

To build this deb package I'm using the following commands:

cd ~/src/
git clone -b bdr-plugin/next git://git.postgresql.org/git/2ndquadrant_bdr.git bdr-plugin
git clone -b debian/bdr https://github.com/2ndQuadrant/bdr-packaging bdr-packaging
cp -rfv bdr-packaging/debian bdr-plugin/debian
cd bdr-plugin
dpkg-buildpackage -b -rfakeroot -us -uc

About the dependencies, all the postgresql-BDR server are installed, and all postgresql looks ok:

# dpkg -l | grep postgresq | grep dev
ii  postgresql-bdr-server-dev-9.4       9.4.5-2trusty                    amd64        development files for PostgreSQL-BDR 9.4 server-side programming
ii  postgresql-server-dev-9.1           9.1.21-1.pgdg14.04+1             amd64        development files for PostgreSQL 9.1 server-side programming
ii  postgresql-server-dev-9.2           9.2.16-1.pgdg14.04+1             amd64        development files for PostgreSQL 9.2 server-side programming
ii  postgresql-server-dev-9.3           9.3.12-1.pgdg14.04+1             amd64        development files for PostgreSQL 9.3 server-side programming
ii  postgresql-server-dev-9.5           9.5.2-1.pgdg14.04+1              amd64        development files for PostgreSQL 9.5 server-side programming
ii  postgresql-server-dev-all           173.pgdg14.04+1                  all          extension build tool for multiple PostgreSQL versions

Any idea why I'm getting this error?

Regards


texts: Do i need to rebuild the indexes on the table after changing column length on SQL table?

texts: I have increased the length of a column using alter table statement, there are 3 non clustered and 1 clustered index on the table.  Now do i need to rebuild the indexes on the table ?


texts: SQL Server 2008 error when changing the number of cores

texts:
I have a virtual machine (virtualbox) with Windows Server 2003 and SQL Server 2008, running with 4 cores. I wanted to use 6 cores, but when I changed the machine configuration, for some reason SQL Server stopped working. I couldn't start the service - it was giving me an unhandled exception without any extra data.

Does anyone know a fix for that? I can live with 4 cores, but I really want to upgrade to 6.

(I know is this setting, because as soon i went back to 4 cores SQL Server started to work again)

I think there has to be a way to upgrade the machine without reinstalling.


texts:
Test scenario:
I am only one user on database. I reset sys.dm_os_wait_stats. I run a SELECT query (table scan).

Result:
Execution time = 20 minutes, but sys.dm_os_wait_stats -> PAGEIOLATCH_SH -> Wait_time_ms = 40 minutes (2 580 252)

Question:
How is it possible that in 20 minutes: wait_time_ms > execution time ?


texts: Nulling other Date parameters in SSRS on selection of one

texts:
I have set up multiple dates as input parameters and in SQL query, I've set up the WHERE clause like:

WHERE datecol1 >= @date1 OR (@date1 IS NULL)

So that handles dates as optional parameters. I have three date parameters. I want to make the report such that if the user selects one date, other two should be nullified, like the null should become checked.

Also, right now, I have this setting in default values part of the date parameter that i want to be dependent on the other date parameter.

What am I doing wrong here? Because this still does not work.


texts: Use BIGINT and twice the storage space or INTEGER but with extra operations?

texts:
Suppose I've a table named agency with some columns:
  internal_id(integer, unique)
, external_id(bigint, unique)
, name, location, created_at, ...

internal_id and external_id are each one unique and candidates for being as the primary key column.
There are some other tables (says A, B, C, D, E) that reference to this table. Suppose each of these tables may contains millions or billions of rows.
Normally I have the external_id when I need to filter the tables A, B, C, D, E data.
Which of the following scenarios are the best way to go, considering performance and storage space:

Use internal_id as primary key in agency, and as foreign key in other tables. Because this field takes 4 bytes of storage space, we can save billion of bytes. However as I normally have the external_id, I have to do an extra JOIN for each query as a penalty:

    SELECT A.* FROM A
         INNER JOIN agency ON A.internal_id=agency.internal_id
    WHERE agency.external_id=5;

Use internal_id as primary key in agency, and as foreign key in other tables. But to get rid of an extra JOIN, in my application I could first map external_id to internal_id with a simple query (SELECT internal_id FROM agency WHERE external_id=5), and then use the fetched internal_id for another simple query:

    SELECT * FROM A
    WHERE internal_id=59; -- 59 is the fetched internal_id from the other query

Does it have better performance than JOIN considering an extra round trip between app and database?

forgetting internal_id and use external_id as the primary key and foreign key, with the penalty of 4 more extra bytes per record in each other tables (A, B, C, D, E) and cost of billions of more storage space or potentially even slower database operations (because of bigger database files):

    SELECT * FROM A
    WHERE external_id=5

Update:

agency table may contains 10s of thousands or at most a few millions of rows.
internal_id and external_id will not change over time, but other non-identity columns may rarely change.
There are about 5 to 7 related tables (A, B, C, D, E, ...) that a few of them may get too large over time, say a few million rows per day (billions of rows over a year)


texts:
What is the difference when using or not using "With Replace" when restoring database. I know that With Replace, I would get identical database as backup file even log file would be replaced/overwritten and current content / transactions would be broken.

But, what is actual difference; I read somewhere that some configuration for database like recovery model is stored in Master database; With Replace - it can be overwritten! What else? I cannot find much on what is the use of With Replace? Why it is used a lot, most of the developer restore databases using "With Replace".

I want to clear my concept of restore database.

Thanks


texts:
user

id   |  name
1    |  A
2    |  B

article

id   | title | descritption
1    | wx    | xyz
2    | yz    | abc

article_rating

article_id(article->id)  |  given_by(user->id)  |  given_rating
   1                              2                  4
   2                              1                  3

saved_article

article_id(article->id)  |  saved_by(user->id)
   1                              2

I'm changing some buttons and icons depending on whether a user has rated/ saved an article or not. I'm using two select queries for that:

rated or not

SELECT given_rating as rating
FROM article_rating
WHERE  given_by=? AND article_id=?

saved or not

SELECT saved_by as saved
FROM saved_article
WHERE  saved_by=? AND article_id=?

So my question is how can i combine both into one mysql query efficiently??


texts:
My server is writing about 50% of temp tables to disk, even when max_heap_table_size and tmp_table_size are ridiculously high (500M). Yesterday I ran a script all day that monitored the tmp folder and the total space never exceeded 100M.

My theory is big columns such as long text are forcing tmp tables to disk. I have no control over the queries or this part of the database design.

If I pointed tmpdir to a 500M tmpfs file system I could speed up these queries. I can only think of 2 reason why not to do this.

It takes away from memory that could be allocated to innodb buffer pool
Infrequent operation such as table copy, optimise, alter etc can use huge amounts of tmp space

Regarding reason 1, I'm thinking the performance gains would trump and gains by allocating more memory to innodb buffer pool, but I need to test.

Regarding reason 2, if I pointed tmpdir to a tmpfs file system and innodb_tmpdir to a large disk based system, will that avoid filling up tmpfs when doing table copy, optimise, alter etc?


texts: fastest way to generate permutations of a table with n columns and put in a new table added one more question to same how to remove duplicate rows

texts:
As i am from mathematics background and require to do some huge permutations to start in my work kind help.

If i have a table by name sample1 with n columns what is fastest way to get all the permutations of the rows of sample1 into a new table. n>=12 say.

Please provide kind help with a link where i can get help or any code or query.

An example

say the 6 columns of a row contain

E1       E2      E3        E4        E5        E6

'[0,1]' '[1,2]'  '[2,3]'   '[3.4]'   '[4,5]'   '[9,10]'

I need to include its permutations

like

E1       E2      E3        E4        E5        E6

'[1,2]' '[0,1]'   '[2,3]'   '[3.4]'   '[4,5]'  '[9,10]'

 '[0,1]'   '[2,3]' '[3.4]' '[4,5]'  '[9,10]' '[1,2]'

and other permutations of row 1 above if there are n columns there will be n! permutations i need them also to be generated quickly and put into a new table.
U can the headers as column name each is a varchar.

Like for the above table if there are two rows with same elements in each of its columns how to remove duplicates kind help with any query


texts: sqloledb vs sqlncli - Performance difference or just feature set?

texts:
I've been running through some Classic ASP code recently and was wondering if there was a performance difference between connecting using:

Provider=SQLOLEDB;server=NTSERVERNAME\SQLEXPRESS;database=DBNAME;uid=USER;pwd=PASSWORD;

Or

Provider=SQLNCLI;server=NTSERVERNAME\SQLEXPRESS;database=DBNAME;uid=USER;pwd=PASSWORD;

I seem to recall that there is a performance impact with some provider options (mostly from the days of SQL Server 2000 and earlier), but I've not been able to find if that is the case with these two providers.

The only thing I've been able to find are articles explaining that the latter will let me use some of the new features in SQL Server 2005 (XML data type, etc.) and some other feature support issues - but nothing about performance.


texts: How to define a parameter that will be used as an IN argument inside a function?

texts:
I want to make a function that receives two parameters. The first one represents the salaries from a group of employees, and second one, the codes from a group of departments. Both, P_IN_SALARIES and P_IN_DEPARTMENTS_CODES parameters, will be used as
arguments in an IN function of a query, just as demonstrated in the code below:
CREATE OR REPLACE FUNCTION public.get_employees_id(P_IN_SALARIES WHICH_TYPE_COMES_HERE, P_IN_DEPARTMENTS_CODES WHICH_TYPE_COMES_HERE)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
declare
    v_employees_ids text;
begin

    select STRING_AGG(employee.id || '', ',') into v_employees_ids
    from employee
    inner join departament on department.id = employee.departament_id
    where employee.salary in (P_IN_SALARIES)
    and department.code in (P_IN_DEPARTMENTS_CODES);

    RETURN v_employees_ids;
END;
$function$

What is the type of a IN parameter in a SQL statement?
Is there a generic one that I might use in order to allow a kind of portability on an occasional database exchange? (e.g. to Oracle)


texts:
I have a SQL Server Agent job stuck in "In Progress". It's a FULL backup that usually takes 30 minutes, but it's taking 7 hours and counting. I've restarted the Agent and then the whole OS and it still says "In Progress".

Is there any way to stop that?

If I run select * from sys.dm_exec_requests, I get 2 rows in "running" status, one says "SELECT", the other one "DBCC TABLE CHECK". And indeed the first step of the job is to run a Database Integrity check using Ola Hallengren's scripts.

Version:

  Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64)
  Jul  5 2017 23:41:17   Copyright (c) Microsoft Corporation  Standard
  Edition (64-bit) on Windows Server 2016 Datacenter 6.3  (Build
  14393: ) (Hypervisor)


texts: BULK INSERT continue on PRIMARY KEY error

texts:
I get a CSV file with a several million records every few days. I need to insert them into our database.  What I do is quite straightforward:
BULK INSERT dbo.activity
FROM 'C:\tmp\activity.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '0x0a',
    BATCHSIZE = 1000,
    MAXERRORS = 2
)
GO

This works well.  However, it may be the case that I get some duplicated records. When I run the import I, as expected, get the error:
Violation of PRIMARY KEY constraint 'PK__id__790FF7A****'. Cannot insert duplicate key in object 'dbo.activity'. The duplicate key value is (1234)

This gets quite cumbersome when instead of one duplicated record I have hundreds.  Is there any way to tell BULK INSERT to ignore (or log) those errors and carry on with the good records? I am thinking about something like:
BEGIN TRY:
  BULK INSERT dbo.activity
  FROM 'C:\tmp\activity.csv'
  WITH (options....)
END TRY
BEGIN CATCH:
  SELECT
        ERROR_NUMBER() AS ErrorNumber
       ,ERROR_MESSAGE() AS ErrorMessage;
   -- I know this line wrong.  It is just what I would like to do
  CONTINUE;
END CATCH

Thanks!


texts: How to retrieve top 3 highest and 3 lowest values in SQL?

texts:
I created a table as shown below,
CREATE TABLE SALES
(
       SALE_ID          INTEGER,
       PRODUCT_ID       INTEGER,
       YEAR             INTEGER,
       QUANTITY         INTEGER,
       PRICE                INTEGER
);

Now i want query which retrieves three highest and three lowest price products in the sales table. I am new in SQL please help. I tried a lot of queries but nothing happen. below is my query.
SELECT TOP 3 PRICE FROM SALES
Union
SELECT PRICE FROM SALES order by PRICE;


texts: Is it possible for a user that is not the job owner to edit the job or add new steps?

texts:
My development lead is ok to revoke the sysadmin privileges he currently has, if there is a way he can still edit/execute/enable-disable/execute application jobs.

So far, I see that with the SQLAgentOperatorRole - he can execute/stop/start jobs, view properties, enable/disable jobs and schedules, review operators, proxies, alerts and delete job history. He can create new jobs BUT he cannot edit jobs or add new steps to the job that he is not the owner of.

As far as I know, A job can be edited only by its owner or members of the sysadmin role.

I am not very clear with the proxy setup. Is there some way to handle this?

Please advise. Thank you,

Debbie


texts: Is this schema usable for multi-tenant clients?How to remove multi-cascade paths?

texts:
I am  newbie to database design. I want to build multi-tenant application that will have the following schema for its data:

School(*schoolid*, schoolname, schooltype);

Department(*deptid*,deptname,schoolid);  //schoolid is foreign key

Staff(*staffid*,staffname,contactno,age,courseid, schoolid);  //schoolid,courseid are foreign keys

Course(*courseid*,coursename,coursetype,schoolid);  //schoolid is foreign key

DSC(deptid,staffid,courseid,six,nine); //six,nine are months indicating duration
of the course to which courseid referes.All ids are foreign keys

SD(staffid,deptid,ten,two,three); //ten,two and three are times at which
particular staffid is free.All ids are foreign keys

The fields within asterisks(*) are primary fields. I want to have
schoolid in School, Department, Staff, Course tables for security concerns and can't remove it from there. What can be more optimized structure for multi-tenacy in this regard? When I use SQL Server 2012 to build this schema, it *gives multi-cascade cycles error. *.
Any suggestions?


texts: EventLog: Server TCP provider failed to listen on [ 'any'  1433]. Tcp port is already in use

texts:
This isn't a duplicate; my scenario is different from others I've found:

MSSQLSERVER service does *not* start, port 1433 in use
https://blogs.msdn.microsoft.com/sql_pfe_blog/2016/10/05/tcp-port-is-already-in-use/
https://support.plesk.com/hc/en-us/articles/213409409

Differences:

Neither cls & netstat -oanb -p tcp nor TCPView reveal anything listening on port 1433
According to the EventLog message, this is occurring on IPV6
The problem doesn't manifest for two named instances running on the same machine—only the default instance

To my knowledge, I'm not using IPV6 in any way, shape or form. I certainly haven't configured it.

The service will start when I make any of these configurations:

Disable TCP/IP in SQL Configuration Manager
Enable TCP/IP but turn off Listen All in TCP/IP Properties
When Listen All is off, disable all IPs on the IP Addresses tab

If I do either of these, the service fails to start:

Turn on Listen All
Enable any IP address

This has got me pulling my hair out.

What's gone wrong here?


texts: Variable numbers of parameters in a SqlClr UDF or Stored Proc

texts:
Lets say I have the following signature for a CLR Stored Proc or UDF:

[Public]
public static void RaisError(
    string message,
    short severity,
    short state,
    params object[] args)

The generated DDL is

CREATE PROCEDURE [dbo].[RaisError]
    @message [nvarchar](4000),
    @severity [smallint],
    @state [smallint],
    @args /* Error: Unsupported type. */
AS EXTERNAL NAME [SqlSaturdayClr].[StoredProcedures].[RaisError];

I can manually write DDL to reference the stored procedure with any number of variables, defaulting them all to null. However, is there a T-SQL way to say "this stored procedure has a variable number of arguments" in the same way as sp_executesql does?

From this codeproject article it seems that extended stored procs intrinsically have a varariable number of variables and you have to enforce variable length in the code. Is my only option to use an extended stored procedure?


texts: select fieldvalues and column-description of a table

texts:
is it possible to retreive the value of a column AND the descirption of each column? I want to see the description behind wach value.

Like:

SELECT
    a.*,
    (SELECT
        d.description
    FROM
        information_schema.COLUMNS AS c
        LEFT JOIN pg_description as d ON
        d.objsubid = c.ordinal_position
    WHERE
        TABLE_NAME = <a.tablename> AND column_name = <colname>) as description
FROM table as a
WHERE true;

The table should by look like this:

id: serial 'no description';
col1: int2 'Column One';
col2: int2 'Column Two';

Content:

1, 10, 11
2, 20, 21
3, 30, 31

The result should look like this:

1, 10 (column one), 11 (column two)
2, 20 (column one), 21 (column two)
3, 30 (column one), 31 (column two)

Best regards

Christian


texts: How to query for records based on most recent version

texts:
I have the following table structure

ID, ORDERNUMBER, DATE, STATUS
1, 1, 1-1-01, proposal
2, 1, 1-2-01, proposal
3, 1, 1-3-01, active
4, 2, 1-1-02, proposal
5, 2, 1-2-02, cancelled
6, 3, 1-1-03, proposal
7, 3, 1-2-03, proposal
8, 4, 1-1-04, proposal
9, 4, 1-2-04, active
10, 4, 1-3-04, active

Here is the SQL to recreate the table.

CREATE TABLE [dbo].[tableX](
    [ID] [int] NOT NULL,
    [Ordernumber] [int] NULL,
    [Date] [date] NULL,
    [Status] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [tableX]
    ([ID]
    ,[Ordernumber]
    ,[Date]
    ,[Status])
VALUES
    (1, 1, '1-1-01', 'proposal'),
    (2, 1, '1-2-01', 'proposal'),
    (3, 1, '1-3-01', 'active'),
    (4, 2, '1-1-02', 'proposal'),
    (5, 2, '1-2-02', 'cancelled'),
    (6, 3, '1-1-03', 'proposal'),
    (7, 3, '1-2-03', 'proposal'),
    (8, 4, '1-1-04', 'proposal'),
    (9, 4, '1-2-04', 'active'),
    (10, 4, '1-3-04', 'active')

I need to query for all records that have a most recent state of active, in the case of the example it is ordernumber 1 and 4.
Input parameter: Status = active
Result:

ID, ORDERNUMBER, DATE, STATUS
1, 1, 1-1-01, proposal
2, 1, 1-2-01, proposal
3, 1, 1-3-01, active
8, 4, 1-1-04, proposal
9, 4, 1-2-04, active
10, 4, 1-3-04, active

Further I need to split this into history and current statuses so for state active and current the Id's 3 and 10. For state active and history the Id's 1, 2, 8 and 9.
Input parameters: Status = Active, Date = Most current
Result:

ID, ORDERNUMBER, DATE, STATUS
3, 1, 1-3-01, active
10, 4, 1-3-04, active

Input parameters: Status = Active, Date = Not most current
Result:

ID, ORDERNUMBER, DATE, STATUS
1, 1, 1-1-01, proposal
2, 1, 1-2-01, proposal
8, 4, 1-1-04, proposal
9, 4, 1-2-04, active

I've tried a lot of possibilties, but I cannot find a query that works. Can someone help me out or point me in the right direction?

I am working in a SQL Server environment.


texts: Calculating Price Based Off Price Break Table

texts:
I have an orders table and a price break table.  If an itemcode exists in the price break table then the price is reduced based on the quantity.  I am not sure of what the syntax would be for a conditional like this, so I am going to seek assistance.  This is sample DDL and a query that I started on with a case statement but not sure how to finish it off

 DECLARE @Parent TABLE
(
  itemname VARCHAR(100),
  itemprice FLOAT,
  itemqty INT
)

 DECLARE @Exceptions TABLE
(
  itemname VARCHAR(100),
  qtyordered VARCHAR(10),
  itemrpice FLOAT
)

 INSERT INTO @Parent ( itemname, itemprice, itemqty )
            VALUES  (  'rr11',   0.00 ,     10 ),
                    (  'rr11',   0.00 ,     5 ),
                    (  'rr11',   0.00 ,     22 ),
                    (  'mc22',   15.00,     5 )

 INSERT INTO @Exceptions ( itemname, qtyordered, itemrpice )
            VALUES      (  'rr11',  '1-5' ,     15.50 ),
                        (  'rr11',  '6-10',     11.00 ),
                        (  'rr11',  '>11' ,     17.00 )
Select
itemname
,case when itemprice IN (Select itemrpice from @Exceptions) Then
--compare the quantities to gather the pricing info
itemqty
FROM @Parent


texts: Query to filter a table based on a comparison applied to the same table?

texts:
Case & Problem:

Consider a corporate building with some rooms and departments that need authorization to enter.
Employees have to scan their badges to be authorized.
If the employee is inside (the badge was already scanned), the system warns her/him accordingly and does not open the door.
And the supervisor should be able to see and/or report all employees who are in a particular room at any given time.

Checking whether the employee is inside or not was somewhat trivial with the help of a couple of StackOverflow/StackExchange answers.

My problem is at the reporting stage.
The question is how can I filter the table to show those who are currently inside the given point, in a performant way.

Current Situation & What I've Done So Far:

My backend code is looping over all the records and check if they're inside, one by one.
Yet, I suspect that this is not ideal (I might be wrong, though) performance-wise and/or for modularity/responsibility concerns; in the future, I'll need to allow other client applications to connect to the database without having them write their own code/logic to check or report the inside status.

Sample Table:

+-----+----------+---------------+-----------+-----------------+
|  id | code     | point         | action    | timestamp       |
+-----+----------+---------------+-----------+-----------------+
|   1 | 11111111 | Department A  | pass_in   | 1561369024008   |
|   2 | 11111111 | Department A  | pass_out  | 1561369024007   |
|   3 | 11111111 | Department A  | pass_in   | 1561369024006   |
|   5 | 22222222 | Department B  | pass_in   | 1561369024005   |
|   6 | 22222222 | Department A  | pass_out  | 1561369024004   |
|   7 | 22222222 | Department A  | pass_in   | 1561369024003   |
+-----+----------+---------------+-----------+-----------------+

Sample Code:

var point = 'Department A';
var recordsInPoint = helper
                        .fetchAllRecords()
                        .filter( record => helper.isInside( record, point ) );

Query to check if inside:

SELECT
    ( count_compare.count_in > count_compare.count_out ) AS is_inside
FROM
(
    SELECT
        (
        SELECT
            COUNT(`id`)
        FROM
            scanner_log
        WHERE
            `code`='11111111' AND
            `point`='Department A' AND
            `action`='pass_in'
        )  AS count_in,
        (
        SELECT
            COUNT(`id`)
        FROM
            scanner_log
        WHERE
            `code`='11111111' AND
            `point`='Department A' AND
            `action`='pass_out'
        ) AS count_out
) AS `count_compare`;

Questions:
• How can I move the reporting logic from backend to database?
• Would it be really faster and less resource-hungry if handled by db server?

Ideas & Options:

• Create a stored procedure and carry the backend logic to db as is by looping over all records.
I don't see much benefit on this, to be honest. Same loop method, just performed by db server. Also, there's a major drawback as I don't have just one table for logs, there are separate tables for each building or company. And I can't pass the table name as a parameter, as far as I know mySQL / MariaDB doesn't support this. I have to use PREPARE QUERY and EXECUTE QUERY commands which sounds a little bit overkill to me.

• Create another nested temporary table and join them somehow to filter but I just couldn't get my head around it, really, nothing.

Thanks for all your help and insights.


texts: How to Get one Single statement of two table value in SQL

texts:
I wanted to retrieve two table values in one single statement such as,
(Example Bank statements..)
Table 1: Invoice
SELECT ClientNo, InvoiceDate, InvoiceNo, TotalDue
FROM Invoice_TBL
WHERE ClientNo = 1
ORDER BY InvoiceDate ASC

Table 2: Payment
SELECT ClientNo, PaymentDate, TotalAmountPaid
FROM Payment_TBL
WHERE ClientNo = 1
ORDER BY PaymentDate ASC

The Question is how can i merge two table values and retrieve data from client wise date ascending order
I want the result like bellow screenshot

i'm new to SQL database. Thank you


texts: Select distance between cities in two directions

texts:
I have a distance table and a cities table.
I have joined them in a way where i now have: cityA_id,city_A,cityB_id,cityB, distance in km
i need to get the distance betweens cityA and CITY B and
then get the distance between cityB and CityA in the same query result.
(meaning if Albany to Auburn is 36 Km i want to also check Auburn to Albany distance (where cityA will be Auburn and cityB Albany)
sample data:

how can i write this affectingly?
what i have so far:
with A as (
SELECT  [CITY_1],
[CITY_NAME]
,[CITY_2]

,[DIS_IN_KM]

FROM [DB].[dbo].[DISTANCE] d
join cities c
on d.CITY_1 = c.CITY_NO
),
first_direction as
(
select A.[CITY_1] as cityA_id,A.[CITY_NAME] as [CityA],A.[CITY_2] as CityB_id,  c.CITY_NAME as CITYB, A.[DIS_IN_KM] as [distance in km],
c.CITY_NAME as CITYB2, A.[CITY_NAME] as CITYA2--, B.DIS_IN_KM as [distance in km opposite]

from A join CITIES c on A.CITY_2 = c.CITY_NO

)
select * from first_direction

tables schema:
CREATE TABLE [dbo].[DISTANCE](
    [CITY_1] [char](6) NOT NULL,
    [CITY_2] [char](6) NOT NULL,
    [DRIVE_TIME] [decimal](9, 2) NOT NULL,
    [DIS_IN_KM] [smallint] NOT NULL,
    [HARD_DIST] [smallint] NOT NULL,
    [BUS_FARE] [decimal](9, 3) NOT NULL,
    [CODE1] [smallint] NOT NULL,
    [CODE2] [smallint] NOT NULL,
    [CODE3] [smallint] NOT NULL,
    [DATE] [datetime] NOT NULL,
    [TEMP1] [decimal](9, 3) NOT NULL,
    [TEMP2] [decimal](9, 3) NOT NULL,
    [TEMP3] [decimal](9, 3) NOT NULL,
 CONSTRAINT [PK_DISTANCE2] PRIMARY KEY NONCLUSTERED
(
    [CITY_1] ASC,
    [CITY_2] ASC
)

CREATE TABLE [dbo].[CITIES](
    [CITY_NO] [char](6) NOT NULL,
    [CITY_NAME] [char](40) NOT NULL,
    [BASE_CODE] [tinyint] NOT NULL,
    [ZONE_CODE] [tinyint] NOT NULL,
    [NAME_2] [char](40) NOT NULL,
 CONSTRAINT [PK_CITIES_NUMBER] PRIMARY KEY NONCLUSTERED
(
    [CITY_NO] ASC
)

Thanks in advance!


texts:
I'm looking so long, and cannot find anything right concerning - ho to sum the values from JSON column.

My values in the column looks like this:

[{"file":"FILE.doc",
  "POSITION":"3245",
  "NUMBER":"2345",
  "PRICE":"123",
  "PRICE2":"3456",
  "NUMBER":"2019-01-25",
  "date":"2019\/01\/15",
  "added_by":"admin"
 },{
  "file":"FILE.doc",
  "POSITION":"345",
  "numer_faktury":"456",
  "PRICE":"125",
  "PRICE2":"567",
  "DATE":"2019-01-11",
  "DATE":"2019\/01\/15",
  "added_by":"admin"
}]

Table = table
column = prices

My select to extract the values looks as follow:

SELECT JSON_EXTRACT(prices,
    '$[*].PRICE') as quality FROM table
    WHERE employee_id = 10060

And I've got this:

|  QUALITY   |
["123", "125"]

Is there any possible to sum the values and get:

|   QUALITY     |
|   ["248"]     |


texts:
I commonly make this query on my database
SELECT * FROM Example_tab WHERE Column_a + Column_b < 10;.

Is it possible to create indexes based on Column_a + Column_b < 10 to speed up this lookup. I see it is possible to do it for oracle based db over here (by doing CREATE INDEX Idx ON Example_tab(Column_a + Column_b);).But I can't find it anywhere for postgresql or mysql.

Is it even possible to do it on databases other than oracle ? If yes, is it documented somewhere, what are they called (basically, what should I google to find them) ?

Thanks in advance for any help.


texts: Does activating delayed durability affect executing statements?

texts:
I'm in the middle of a huge REORGANIZE. In order to speed it up I set DELAYED_DURABILITY = FORCED.

Will this affect the running statement? I watched IO and CPU numbers on the server and could not make out a difference. I'm also not sure if there would be any difference at all so I can't tell.


texts:
I am using SQL Server 2008 R2 and I want to store Japanese characters in one of the columns of my table.

Say I want to store Japanese names, how can I achieve that? Is there an easy way?


texts: Is it possible to login to an Oracle schema using a certificate?

texts:
The question is based on having to deal with lots of automated processing (testing, mostly). As it stands, all the schemas used have the same well known password, which is stored in lots of configuration files, which is bad from a security perspective. Using encrypted passwords does not really cut it.
So the question is: Does Oracle DB allow the use of client certificates for authentication?


texts: Intermittent SQL Delegated Authentication Errors

texts:
Here is an odd issue that is causing me to lose all my hair.
Every few days or so our delegated authentication from one SQL server to another fails with the error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. The SQL server logs show: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: 10.1.x.x] Error: 18456, Severity: 14, State: 5."

Our environment consists of 6 Domain controllers ranging from Windows 2012 to Windows 2019, The SQL servers are Windows Server 2016 running SQL Server 2017.
After a few hours the issue goes away by itself, unless the Kerberos tickets are purged on either one of the affected SQL servers which resolves the issue in about 15 minutes.
At first we thought it was an issue with a particular SQL server so we replaced it, however over the last year it has affected several servers with several different delegated connections.
To date we have tried updating SQL server, deleting and recreating SPNs, upgrading the bulk of our DCs to Windows 2019, updating the domain functionality level, and multiple Microsoft tickets. We have a suspicion that leaving SSMS sessions to the SQL Servers overnight triggers the Kerberos delegated connections to fail more often but it may be superstition at this point.
Any ideas? It works 80% of the time which has made it especially tricky to hunt down. The domain controllers appear to be syncing normally, but I'm not sure what to look for in the case that the DCs are the problem.
Oddly, we are able to connect to the individual servers via protected AD accounts during the outage and the log ins are listed as Kerberos, it's just the AD delegated permissions that stop working.
We do the DC maintenance over the weekends and the issue seems to pop up at any time of any day.


texts: SQL Database Server Recovery and connections

texts:
We have a server 2008 R2 with SQL 2008 instance.  The underlying hardware RAID is failing and we need to migrate the databases to a stable piece of hardware with the same hostname on the network quickly.  If we remove the existing server off the network to allow the new hardware to be joined to the domain with the same hostname will that allow us to reattach/restore the databases from the former server without having to change client based connection strings/ODBC?  If this fails, will restarting the old server and rejoing it to the domain mean that the databases/aplications still connect ok?

I ask this because I do not fully understand how the clients connect to the instance...does it just use DNS and connect with the server hostname and instance name or does it use the underlying SID to identify what to connect to?

Apologies for my ignorance.


texts: Which order should the columns in a composite key as a foreign key be specified?

texts:
Looking at the code from this answer which uses a composite key to control which child tables can contain the parent key, I noticed he specifies the primary key of the foreign table first, then the smaller lookup value second.

Does the order in the foreign key matter? When creating an index on this foreign composite key, would it be more efficient to specify the lookup value first (since there would only be a few possible values) then the primary key? Or is specifying the primary key first still faster because it's the clustered index on the foreign table?

Also, does it matter per DB? I'm using SQL Server.


texts: Using sql server management studio, I want to create a full text index where a certain field is treated as html, tags should be ignored and umlaut-codes should be taken into account. How can I do that?


texts: What's the term that describes our data processes

texts:
We provide an online subscription product that allows our users to research and analyze financial data of their interested industries. As such, we have various tools for loading data from various sources, cleansing data, procssing data, tagging data based on user interactions etc. Basically all sorts of processes and tools that deal with data manipulation.

We are looking into overhauling our these data processes, and I am trying to find the right term to call these processes, both for research purpose and for my presentation. The names I have considered so far:

ETL - this term seems too narrowly focused on loading data. Our data processes deal with user inputs extensively too.
Data Warehousing - this term seems too generic and broad. I am not sure if our processes are data warehousing in nature.

I am looking for a term that I can use to name or describe our processes.


texts: some InnoDB settings in My.cnf are messing up everything, why?

texts:
When I edit my.cnf in MySQL 5.0 in filemanager and add the following settings, I get logged out automatically from Kloxo and when I remove these line and restart server everything is ok then:

innodb_buffer_pool_size = 50M
innodb_log_file_size = 15M

but when I add the following line and restart I have no problem and this setting has been set:

bulk_insert_buffer_size=25M

Why is this happening?

P.S.:
I should note that Horde_groupware database have innoDB tables, when everything is messed up and I SELECT * FROM Horde_groupware.tblname I get the error says bad information in .frm file.


texts: Find "second-greatest" string in each "group"

texts:
I'm doing some data analysis and want to find an easy way to examine all the members of each "group" in a group by function.

Like, 3 agents may be involved in an order. I want to quickly examine the three agents that were 'grouped' in this order for various reasons.

Usually, I would use group_concat for this (easy way to see all grouped strings). However replicating that using a 'group by' appears difficult and unwieldy for now in SQL sever.

Right now, rough-and-dirty, I would max(username) and min(username) to quickly find 2 (and 90% of orders probably have 2 or less people. Is there a way to do mid(username) or 2nd-highest(username), or percentile(50th, username)? That would be a great, quick way to find this relevant data. For some reason, the previous answers I've seen describing group_concat on SQL Server do not sound straightforward to me.

Sample data for instance:

employee  purchase_id
bill        1
bob         1
chrissy     1
mike        2
bill        2
bob         3

Currently I have this:

purchase_id, employee_count, complicated metric
1                 3              blahblah
2                 2                dsflsajf
3                 1                98%

I would like to see at a glance:

purchase_id, employees, complicated metric
1            (bill,bob,chrissy)   blahblah

However the group_concat seems very confusing to use with a group by statement - or simulating group_concat with SQL Server. So instead, how bout this.

select max(employee), min(employee)

purchase_id,  max(employee), min(employee)
1              bill            chrissy

in the example you see that bob is omitted, as max/ min will only find the two endpoints. If there was some kind of function to pull the second highest value, or 50th percentile value, on strings, that would be helpful.


texts: Is it possible to refer to alias before it is assigned?

texts:
Does the query shown below work? (Sorry for this type of question, but I am a novice and it is not possible for me to run it right now).

SELECT
   table.id
   SUM(10/ta.value) AS amount
JOIN tableA ta
         ON ta.key = t.key

What I am curious about if it is possible to refer to a alias "before" (i.e higher up) it has been assigned.

Thanks!


texts: Star join query optimization - Change partitioning, use columnstore?

texts:
I am trying to know the best possible way to improve performance of a query given to me by my client. It contains a few tables which are joined and one of table is called dwh.fac_sale_detail which contains 1.5 billion rows.

This table dwh.fac_sale_detail is partitioned based on one of its columns called TradingDateKey1.  It actually stores data in yyyymmdd format but it is of INT Datatype.

This has TradingDateKeys from 2005 to 2015, but the partitions are created until year 2014 only.

One of the guys in another team advised the following and I am trying to follow his advice but I am new to creating or altering of partitions and don't know if this will actually make any difference to query performance:

What he said in his own words is "The FactSalesDetail table is currently about 1.5 Billion Rows and is currently partitioned on TradingDate into 10 partitions by year with about 150 Million Rows per partition. It would be a good idea to further partition the latest year into monthly partitions and apply a columnstore index on all the partitions. Applying the index on each partition will be a once-off and you should only need to maintain the index for the current partition going forward."

Here is the query plan for the query I am trying to optimize.

Please also see the attached screenshots for a better understanding:


texts: How to connect to Oracle 11g using "Navicat for Oracle"?

texts:
My Oracle 11g Enterprise Manager runs like this:

I have taken the following screen-shot after my Oracle installation has been completed:

I have the following Oracle-related services running:

I am able to connect to Oracle 11g with SqlDeveloper like this:

But, this is not working:

It gives the following message:

Now, how to connect to Oracle 11g with Navicat?


texts: Option to uncheck all or invert selection in SSDT?

texts:
In SQL Server Data Tools (SSDT) after compared the schema, I want to update few changes of mine only (as red-circled in the screenshot).

But there are many other changes are listed in the window, I need to manually uncheck all the other items.

Is there any option to uncheck all or invert selection the items?


texts: Using SQL script in Toad, how do I suppress the echo of the SQL statements

texts:
I'm using a script (Toad, Oracle) so that I can use variables and enter two values that get repeated in multiple SQL statements.

That's working great, but the SQL statements echo into the console output.

I don't expect grid output, but I would like the SQL statement echo to be suppressed
(I'm glad it's an option, but once things are working it's just in the way).

Is there a way to get rid of that?


texts: Storing CREATE EXTERNAL DATA SOURCE in a SSDT project without exposing the DATABASE SCOPED CREDENTIAL or MASTER KEY secret;

texts:
I have a SSDT project that deploys to Azure SQL DB. Recently I added an external table. This required an external data source as follows:

CREATE EXTERNAL DATA SOURCE [data_warehouse]
    WITH (
    TYPE = RDBMS,
    LOCATION = N'mydb.database.windows.net',
    DATABASE_NAME = N'MainDW',
    CREDENTIAL = [dw_reader]
    );

When I import this into my .sqlproj, it complains the credential isn't there, and then when I add that it complains the master key isn't there. The problems is I don't want to store those secrets in the SSDT project. How do I store dummys, or defaults that can be overridden and retrieved from the azure keyvault or other method of storing secrets in Azure Decops?


texts:
MySQl Slave replication server got crashed and I am missing around 60 tables, some table structure and data which present in master and by DB size is more than 25Gb, How I Can restore to resolve issue.
Please help to solve it ASAP.
Thanking you in advance...
Nikhil


texts: How to run PostgreSQL without root privileges

texts:
I want to use (or hack) the following library for unit testing PostgreSQL-based code: https://github.com/Microsoft/pgtester - this basicly starts a new server with data in temporary directory and runs all queries against it.

However, in my Linux system (Fedora 26 but quite probably other distributions as well), it cannot be used because it is difficult (or impossible) to run the server without root privileges. Namely, I am stuck at the point of creating Unix socket /var/run/postgresql/.s.PGSQL.6433. The directory is, by default, writable only for postgres user. On my own machine, I can of course change the writing rights but for a general-use client-side tool, it would be nice to have solution that would run out of the box.

So, is it possible to start the postgresql server with pgctl specifying the directory where to write the server unix socket? Or any other workaround?

Thanks.


texts: An unexpected error occurred in function 'XMTMStorageFileHelper::CreateOrModifyStorageFile' when processing SSAS tabular model

texts:
I'm trying to process partitioned table in SSAS tabular model and I got following error multiple times:
An unexpected error occurred (file 'xmutiltm.cpp', line 444, function 'XMTMStorageFileHelper::CreateOrModifyStorageFile').
Only solution I was able to find was related to PowerBI desktop and it was to reinstall. I'm wondering if anybody experienced this problem before and how it was fixed?


texts: Use Windows 2016 Storage Replica for SQL Server failover cluster instance

texts:
We have two standalone servers with local storage. Based on our needs we have determined that we need to use AlwaysOn failover cluster instances.

Can we use the "Storage Replica overview" feature of Windows Server 2016 to create the failover cluster?

We also open to buy new SAN storage. Can this be possible?


texts: Best way to ALTER date column to timestamp on a large table?

texts:
I have to ALTER a date field to timestamp on a huge table having 1 billion records.

I am using Postgres 9.5. I learnt that when we use alter query a heavy “access exclusive” lock is acquired, which shuts everything else out of the table.

How can we achieve this efficiently?


texts: Putting a Select statement in a transaction

texts:
What is the difference between these 2 queries:

START TRANSACTION;
SELECT * FROM orders WHERE id=1;
UPDATE orders SET username='John' WHERE id=1;
COMMIT;

And without transaction:

SELECT * FROM orders WHERE id=1;
UPDATE orders SET username='John' WHERE id=1;

What is the effect of having a SELECT inside a transaction?

If DELETE FROM orders WHERE id=1 was called from another session right after the SELECT in both cases, when will it be processed?


texts: Can select read index page while modify this page in innodb?

texts: When mysql innodb insert or update secondary index page , will at the same time this index page can searched by select ?


texts:
Index merge is used very seldom and often is not efficient.
But search engines like Elastic, Sphinx can use few indexes efficiently.


texts: Postgres database zero down time production deployment

texts:
I need some idea how I can deploy a financial application in a production environment using PostgreSQL with zero down time.

I have two servers in Master-Slave design. Currently I am giving downtime while deploying applications and executing database script.

During deployment, I don't want to turn off my database because of transactions. If I turn off one node for changes and second for real transactions, now both database nodes have some changes and there will be sync issue. I am looking for a solution where I can deploy DB changes and real time transactions too.

For zero downtime, do we need any configuration or any third party software or PostgreSQL utility that will handle this scenario, so that in production realtime environment we don't lose any transactions, and I also can update database script simultaneously?

"Zero downtime" means "no loss of transactions" and "no interruption of services" both. I understand it's challenging, and also possible like Google never shows any downtime. But still it's not clear how to do it.


texts:
Article Table:

ID | INT(10)
Category | INT(10)

Category Table:

ID | INT(10)
Parent | INT(10)

What I want to generate is a tree that includes a count of articles at all levels e.g.

A (30)
--> B (20)
--> --> C (10)
--> --> D (10)
--> --> --> Q (6)
--> --> --> L (4)
--> E (5)
--> F (5)
G (8)
--> H (8)
--> --> I (7)
--> --> J (1)

I'd really like to be able to generate this output in a single SQL query; however I can do anything to the database structure if that helped. At the moment the only way we can see to do this is:

SELECT COUNT(*) FROM articles GROUP BY category

and then create the hireachy in code. That's what we are trying to avoid.

The only other approach I can think of is a complicated IF() statement which is auto generated based on a pre-computed list of ID hierachies.


texts:
I have two tables say tab1, tab2
here tab1 is the parent table of tab2, Both have triggers enabled say tab1_trg, tab2_trg. Also in tab2_trg there is a SELECT on tab1.

Now I try to delete some records in tab1, it throws error:
ora-04091 table is mutating trigger/function may not see it
ora-04088 error during execution of trigger tab2_trg

I got the issue that I can not select a table that is being changed at the same time.

But I am not getting this error while constraints are disabled, error is occurring  only when constraints are enabled.
Any idea ?

Thanks in Advance


texts: Stored procedures, binlog and SUPER privilege

texts:
I tried to create this function on Amazon RDS for MySQL:

CREATE FUNCTION GetRootZoneId (zoneId INT) RETURNS INT READS SQL DATA
BEGIN
    DECLARE _id, _parentId INT;
    SELECT zoneId INTO _parentId;

    my_loop: LOOP
        SELECT id, parentId
        INTO _id,_parentId
        FROM Zone
        WHERE id = _parentId;

        IF _parentId IS NULL THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;

    RETURN _id;
END;

This would only be used in a one-off query to update an existing database table.

Running it got me the following error:

  ERROR 1419 (HY000): You do not have the SUPER privilege and
  binary logging is enabled (you might want to use the less safe
  log_bin_trust_function_creators variable)

Does this restriction apply to stored procedures as well, or just functions?

If it does apply to stored procedures as well, is there any alternative to execute an equivalent code without setting log_bin_trust_function_creators=1? Please note that I know how to change this setting on RDS, I'd just like to avoid that if possible.


texts: how to identify the transactions that are causing the transaction log to grow?

texts:
I have a database of 20 GB which insists in having its transaction log over 7GB.

when I used this script to find out the size of the biggest objects in that database, I see they are relatively small.

I have been using the default trace to see when this transaction log has been autogrown, but I found none.

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 93 -- log autogrow event
ORDER BY StartTime DESC;

I have also tried some scripts from here, to check what transactions are filling the log, but could not find any.

I believe there must be some very long transactions going on at the same time, or at least one of more long transactions being executed.

How can I check for these (long transactions in the database)?

In LIVE this is a full recovery mode database, part of alwayson.
In TEST this is a simple recovery mode database, but because all the jobs are there, the log grows to 7GB all the same.


texts: MySQL - 450 columns per table good or wrong practice

texts:
I have table having 450+ columns. Can you please confirm is this fine or should I need to split this table?

If yes, what value will be recommended for the number of columns per table in MySQL?


texts:
I have the same behavior in 2 SQL Servers where Stolen Server Memory and Database Cache Memory counters are decreasing (Db Cache) and increasing (Stolen Memory) in a linear way. The amount of memory decreased from Db Cache Memory is almost the same amount of memory increased in Stolen Memory.
Has anyone seeing this behavior before? Is it an expected behavior?

Both server's configuration:

SQL Server 2012 ENTERPRISE with SP3
32GB memory

Thanks for the attention


texts:
I am absolutely stumped as to why my query is not using what I think is a selective index.
My Model consists of Claims, Contacts, and Phone Numbers. Each Claim has 1 Contact and Each Contact has Many Phone numbers. A Claim can have a Status and a Phone Number has a Type.

I have added an index on the Claim for the Status and it includes the ContactID.
create index Status on tClaim(Status) include (Name,ContactID)

I have added an index on the Phones for the ContactID and Type that includes the Number.
create index ContactID_Type on tContactPhone(ContactID,Type) include (Number)

I am trying to write a query that returns all Claims that have a status of 'Won' and the corresponding 'Home Phone' for the Claim's Contact. I have tried it 2 ways. One including the join to Contacts and one without. Neither generate a plan that I expect.
select
    c.ID,
    c.Name,
    p.Number
from
     tClaim c
     left join tContactPhone p on
        c.ContactID=p.ContactID and p.Type='Home'
where
    c.Status = 'Won'

select
    c.ID,
    c.Name,
    p.Number
from
     tClaim c
     inner join tContact co on
        co.id=c.ContactID
     left join tContactPhone p on
        co.ID=p.ContactID and p.Type='Home'
where
    c.Status = 'Won'

The plan I am getting back, refuses to use the tContactPhone.ContactID_Type. It suggests indexing by Type, which doesn't make sense, because it seems less selective than the ContactId.
Paste The Plan
Here is the script I used to create a sample data set to test. Please note my actual data set is much larger, named better, and has a lot more fields; but this is distilled down to replicate my situation [AKA I don't even like the naming conventions and data generation, but it gets the job done :)]
/*
        Create Tables and Constraints
*/
CREATE TABLE tContact(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    CONSTRAINT [pkey_tContact] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
)
GO

CREATE TABLE tContactPhone(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ContactID] [int] NOT NULL,
    [Type] [nvarchar](25) NOT NULL,
    [Number] [nvarchar](12) NOT NULL,
    CONSTRAINT [pkey_tContactPhone] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
)
GO

ALTER TABLE tContactPhone  WITH CHECK ADD  CONSTRAINT FK_tContactPhones FOREIGN KEY(ContactID)
REFERENCES tContact ([ID])
GO

ALTER TABLE tContactPhone CHECK CONSTRAINT FK_tContactPhones
GO

CREATE TABLE tClaim(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ContactID] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Status] nvarchar(10) not null,
    CONSTRAINT [pkey_tClaim] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
)
GO

ALTER TABLE tClaim  WITH CHECK ADD  CONSTRAINT FK_tClaim FOREIGN KEY(ContactID)
REFERENCES tContact ([ID])
GO

/*
        Add Test Data
*/
declare @Count int = 0
declare @ContactID int =0

while(@Count<100000)
begin
    set @Count = @Count+1
    insert into tContact(Name)
    select 'Name' + convert(nvarchar(10),@Count)

    set @ContactID= SCOPE_IDENTITY()

    insert into tContactPhone(ContactID,Number,Type)
    select @ContactID,@Count+1,'Home'
    union select @ContactID,@Count+1,'Cell'

    insert into tClaim(ContactID,Name,Status)
    select @ContactID, convert(nvarchar(10),@ContactID)+'_ClaimName',case @Count % 25 when 0 then 'Won' else 'Closed' end
end

/*
        Add Indexes for Queries
*/
create index Status on tClaim(Status) include (Name,ContactID)
create index ContactID_Type on tContactPhone(ContactID,Type) include (Number)


texts: Replication broken after copying .frm .myd .myi files

texts:
I copied .frm .myd .myi files for MyISAM tables from old_prod server to new_prod.

Then I did a mysqlcheck on DB and repair tables whenever necessary. All is good for MyISAM tables in the new_prod.

What happen is I have a slave configured to replicate from the new_prod.

Error from slave,

  mysql> show slave status\G;
    ....
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 1049

 Last_SQL_Error: Error 'Unknown database 'ReportDB'' on query. Default database: >'ReportDB'. Query: 'create table table1 (name varchar(40))'

  .....

I do not wish to skip the slave error as I need these tables to be in the new slave. Any thoughts to remedy this please?


texts: What is not persisted in a database backup / restore? (SQL Server 2005)

texts: If I take a backup of a database on a SQL 2005 Server, and then restore that backup to a different (new) database on the same database server instance, what things will not be persisted (things like cached execution plans, statistics etc...)


texts: Partial (selective) restore from physical backup of a MySQL instance, possible in version 8+?

texts:
I need to perform the restore of some selected tables of my MySQL 8.2 database.
I was wondering if it's possible to shutdown the service, replace the related files  (from the physical cold backup) in the datadir and reactivate the service to obtain the desired partial restore.
Any tip, suggestion and contraindication is appreciated.


texts: Can I use a Percona Xtrabackup backup to restore a single table?

texts:
I've been playing around with the Xtrabackup tool for a month now, and I got to admit : it is really powerful !

I'm still testing it to backup database servers between 5 and 150 GB and the backup/restoration time are impressive. So I'm trying to push the use of this tool to my manager, saying the only inconvenient we could have is the backup archive size (which is 3x/4x time larger than with mysqldump) and on the other part we gain faster times on backup/restoration, no lock during the backup process and the ability to stream backups to servers.

He was quite impressed by the capability of the tool, but the most important part for him in a backup/restore process is to be able to restore a specific table of a database quickly. As for what I know about this tool, I'm still thinking that this is not possible with Xtrabackup right now. Am I right?

We're using the following softwares :

Ubuntu 12.04
MySQL Server 5.5.32
Xtrabackup 2.1.3-608.precise

PS: I've seen some solutions to restore a specific table from a backup created with Xtrabackup, but it always require Percona Server.

If it is not possible to do with Xtrabackup, do someone have another solution to this problematic?


texts: Users groups/permissions when working with PostgreSQL

texts:
While I've been working with PostgreSQL for approximately 1 year, I still feel like a bit of a Newbie.  I feel like I'm constantly fighting with psql and pg_dump when it comes to files and permissions.  For example, just trying to do a "COPY FROM" and not having permissions to access the file.

How do dbas normally set up their users, etc to make life "easy" (or at least a little less painful).

Also, to "come clean", I have spent most of my career working on Windows.  I now primarily work on Linux.  I would probably give my self a D+ or a C- (always dangerous to try and self grade) when it comes to understand Linux user/groups/permissions.  I'm not completely in the dark and can usually make things work (or at least understand a blog post that I find by Googling a problem) - but I'm certainly not a pro.

I'm currently using PostgreSQL 9.1.5 on Ubuntu 12.04


texts:
What’s the difference between an index-only query and a covering index one (in PostgreSQL 11)?

Both seem to fit https://use-the-index-luke.com/sql/clustering/index-only-scan-covering-index, based on my understanding.


texts: Should dbatools be installed on all SQL Servers

texts: I'm looking to leverage dbatools powershell module for our SQL Server management needs.  The install process does not lend itself for installation across a large number of servers.  Is it really necessary or even a best practice to install dbatools across all SQL Servers in the environment or leverage a server we use for our database management as a central place to install to and manage from there?  Seems like a lot of the commands I've looked at can be used remotely.  Thank you.


texts: Primary node of MongoDB ReplicaSet constantly high cpu usage

texts:
We use mongo replicaSet version 3.4.2 on our production environments. The replicaSet works with 6 nodes , one primary and 5 secondaries. Few weeks ago we have started to get alerts on high cpu consumption on the primary node.

At the beginning we thought that we should separate the load between the members so we configured our service to make reads from secondaries and write to the primary. Unfortunately we are still on the same point.

The cpu of the primary node is between 70-100% even on idle times while the secondaries consumes maximum of 20%. It is important to mention that we creates DB for each one of our customers with average of 9 indexes each - we have 1500DB's like that.

diagnostic data - metrics: https://drive.google.com/file/d/0B11ejEjDA-XiSHFUWll3cmlHeUE/view?usp=sharing

mongostat log: https://drive.google.com/file/d/0B11ejEjDA-XiNEJwTXkzSkZMNWM/view?usp=sharing

let me know if more information requierd.

Thanks,

Roie.


texts:
I wanted in the first place to log connections to a collection and while I was searching I found that it's possible to configure profiling and auditing and logging.

So my question is : What's the difference between the 3 and when to use which?


texts:
Following users are created in MongoDB.

First user created with name "user" having read write role for "project" database use project.

db.createUser(
    {
      user: "user",
      pwd: "user123",
      roles: [
         { role: "readWrite", db: "project" },
         { role: "readAnyDatabase", db:"admin" }
      ]
    }
)

Second user created with name "stud" having read write role for "student" database use student.

db.createUser(
    {
      user: "stud",
      pwd: "stud123",
      roles: [
         { role: "readWrite", db: "stud" },
         { role: "readAnyDatabase", db:"admin" }
      ]
    }
)

When I connected to database having name project using "user" I am getting the project as well as the student database and I'm also able to create new collection in the student database. I haven't give any permission to "user" to access the student database.

Still it is possible to access the student database. How can I avoid this?


texts: High Available PostgreSQL with Patroni and Replication

texts:
I want to build a highly available PostgreSQL cluster using patroni with etcd. I want to have 3 server (1 master and 2 slaves) in my cluster, so if something goes wrong one of the slaves automatically takes over the master role. Only the master server should accept write operators, the slaves are only for safety and read operations.
On the other hand i also need approximately 10 servers with an up-to-date copy of the data on the cluster.
These servers are only there to read data and none of them ever can be promoted to a master server. They shouldn't even be part of the high available cluster.
They are just supposed to get the data using streaming replication etc.
Is such a setup even possible? Can i use replication to other servers when they are part of a patroni cluster?


texts:
we are about to replicate our database servers into several slaves in different physical locations.

The company which is doing replication for us, has asked to give them a sql select statement on one of our tables to check lag and latency like

SELECT id FROM table1 ORDER BY id DESC LIMIT 1

Is it a common practice to check the lag between several instances?

Thank you in advance.


texts: Which are the most often used SQL queries?

texts: the question sounds pretty simple but I couldn't find anything on topic except for this article here. So is there any (even remotely) scientific (or at least accredited) opinion on the matter? The actual DBMS doesn't matter at all - I just want to know if joins outweigh selects in the long run, or inserts outweigh creates and (if possible) by what margin. Something like that.


texts: SSRS Scale-Out Deployment: cannot decrypt the symmetric key

texts:
I have a single 2008 ssrs instance (#1) up and running and need to add a 2nd server also running ssrs 2008 (#2). The error message on the 2nd server is:

  The report server cannot decrypt the symmetric key that is used to
  access sensitive or encrypted data in a report server database. You
  must either restore a backup key or delete all encrypted content.

Searching on that error, I find plenty of links and posts about backing up and restoring keys, but that seems to move the problem from server #2 to server #1. Server #2 will start working, but #1 will get the same error.

I found this regarding rskeymgmt.exe , but that seems to have no impact. The rskeymvmt command reports 'success', but even after restarting both instances of ssrs on each machine, nothing changes.

I have also attempted to follow these steps, but my #2 instance never shows up in the scale-out deployment tab in the configuration manager.

Any suggestions?


texts: Can I add 1 primary node in existing cluster?

texts:
I have a 2 node windows 2012 cluster and SQL Server 2012 installed on both. In this I have added a database on nodeA as primary and one on nodeB as secondary. I want to add more 2 databases which will utilize more CPU. So I was thinking I should add one more primary node nodeC which will have nodeB as secondary and  have one listener for all.

Is this possible?


texts: SSIS - the 1st item in the data flow package view always imports way slower than the others

texts:
In the SSIS data flow task, I've been importing CSV files to my SQL Server via Flat File Source -> OLE DB Destination.

When I'm setting things up in the package design preview, if I have just one import set up, it's pretty slow; usually 1.5 seconds per 1,000 rows.  Now if I drag and setup a 2nd Flat File to OLE DB Destination import to the right of it - same criteria as the first - it's lightning quick.  I've actually started setting it up a dummy import in the upper left of the package design with a relatively small # of rows, like 50,000, just so I can quicken the pace on the other imports that are 1 million + rows.

Is this recognizable to anybody?  I find it very unusal and assume I'm doing something wrong in the process.  Sometimes I only want to import one CSV at a time in my package, but I have to set up at least 2 since the first item in my package preview screen always imports so slow.

edit on further review  - I found the slowness is not caused by where the import data flow is placed in the preview screen, but which OLE DB destination is set up first on the connection manager is the slow one.  When I created a 2nd import, I tried deleting the original OLE DB destination (default name "OLE DB Destination") on the Connection Managers tab and using the 2nd iteration (default name "OLE DB Destination 1") for both imports, and they both imported quickly.

EDIT #2 - ok, so apparently on default it was loading my first OLE DB destination as "Table or View" data access mode, and the subsequent ones as Table or view - fast load" data access modes...so problem solved.  Nobody look at me, I'm a moron.


texts: Must declare the scalar variable question

texts:
That is my first post here, need helps:
in a stored procedure I had some following code

CREATE PROCEDURE [dbo].[SP_getAvg] (
    @projectId INT
    ,@carrierId INT
    ,@fetchType VARCHAR(20)
    )
AS
BEGIN TRANSACTION GetDataSet * *

DECLARE @recAvg FLOAT * *
DECLARE @FACCT VARCHAR(20)
DECLARE @counter INT
DECLARE @carrierAlias AS VARCHAR(20)
DECLARE @tmpDate VARCHAR(20)
DECLARE @sql1 VARCHAR(500)

SET @counter = 1

IF @projectId > 0
BEGIN
    SELECT @FACCT = FACCT
    FROM projects
    WHERE projectId = @projectId
END

SELECT @carrierAlias = carrierAlias
FROM carriers
WHERE carrierId = @carrierId

SET @sql1 = 'SELECT @recAvg = ISNULL(AVG(cast(FWEIGHT as float)/case when CAST(FPIECES as float) = 0 then 1 else cast(FPIECES as float) end),0) from psorderh where (DATEPART(m, manifestDate) =' + cast(@counter AS VARCHAR(2)) + ') AND (DATEPART(yyyy, manifestDate) = DATEPART(yyyy, GETDATE()))' + CASE
        WHEN @projectId > 0
            THEN 'AND FACCT = ' + @FACCT
        ELSE ''
        END + CASE
        WHEN @carrierId > 0
            THEN 'and FCARRIER=' + @carrierAlias
        ELSE ''
        END

EXEC (@sql1)

PRINT @recAvg

......

I got this error message:

  Must declare the scalar variable "@recAvg".

where I did wrong?


texts: Partition trigger and serial column concurrence

texts:
I'm using the following trigger to auto generate monthly partitions for a given table. It works fine.

create or replace function beat_insert_function()
returns trigger as $$

declare
  partition_date text;
  partition_name text;
  start_of_month text;
  end_of_next_month text;

begin
  partition_date := to_char(new.created_at, 'yyyy_mm');
  partition_name := 'beat_' || partition_date;
  start_of_month := to_char((new.created_at), 'yyyy-mm') || '-01';
  end_of_next_month := to_char((new.created_at + interval '1 month'), 'yyyy-mm') || '-01';

  if not exists (select 1 from information_schema.tables where table_name = partition_name) then
    raise notice 'A partition has been created %', partition_name;
    execute format(E'create table %I (check(date_trunc(''day'', created_at) >= ''%s'' and date_trunc(''day'', created_at) < ''%s'')) inherits (public.beat)', partition_name, start_of_month, end_of_next_month);
  end if;

  --- this generates only par keys
  execute format('insert into %I (something) values ($1)', partition_name) using new.something;

  return null;
end

$$
language plpgsql;

This trigger is configured to run before insert, like so:

create trigger beat_insert_trigger
before insert on beat
for each row execute procedure beat_insert_function();

Because the trigger intercepts each insert and the master table contains a id serial column primary key, I ended with only par keys (2, 4, 6, 8, etc), so I changed the insert to:

  execute format('insert into %I (id_beat, created_at) values (currval(''beat_id_beat_seq''), $1)', partition_name) using new.created_at;

Now, the primary key is correct (1, 2, 3, 4, etc)...

My question is: is it safe to use currval in the given scenario? Can the table receive another insert before the trigger finishes, thus creating an insert error, or will the session prevent this from happening?


texts: What are efficient ways to deal with database schemas that are shared between code branches?

texts:
Working on a project with multiple branches, where each branch is eventually merged back to the main branch, and in essence is isolated in order to develop a new feature.

The database, which is MS SQL Server, has a shared schema, however each branch makes changes to the schema as it progresses.

My primary inquiry is what are good ways to deal with sharing the schema from the main branch down to the derived branch, such that changes that are made to the main branch are easily merged into the derived branch, without stepping on new changes in the derived branch?


texts:
I have a cloud-based MySQL instance (slave) and am planning a solution where a remote instance (master) will be based on a ship.  The ship will go in and out of international waters and therefore will have an intermittent terrestrial connection.  When there is no connection, data will be stored locally.  When the vessel arrives in an area with a terrestrial connection, I want the vessel database to synchronize with the cloud-based instance.

This scenario will be duplicated with multiple vessels, with their own instances, and each vessel will need to send local changes to the cloud-based instance.

We have looked at 3'rd party open-source tools like symmetric DS and wanted to 'ask-the-experts' the preferred approach.  Is there a best-in-breed 3'rd party tool, or is the out-of-the-box MySQL solution sufficient?  Thank you in advance.


texts:
In a migration plan I am taking a mysql 5.5 database with only MyISAM tables and converted it into mysql 5.7 with InnoDB tables.

The create table statements are here: http://sqlfiddle.com/#!9/bb815d.
They are exactly the same except for the engine:

CREATE TABLE `pageview` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `VisitId` int(11) DEFAULT '0',
  `PageId` int(11) DEFAULT '0',
  `ViewDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `VisitId` (`VisitId`),
  KEY `PageId` (`PageId`),
  KEY `All` (`PageId`,`ViewDate`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3245799556 DEFAULT CHARSET=utf8;

CREATE TABLE `page` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `SiteId` int(11) DEFAULT '0',
  `Url` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `Name` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `Rating` tinyint(1) NOT NULL DEFAULT '0',
  `RawUrl` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `Url` (`Url`,`SiteId`),
  KEY `SiteId` (`SiteId`)
) ENGINE=InnoDB AUTO_INCREMENT=105689724 DEFAULT CHARSET=utf8;

When running this query the results are very different and performance is much worse in 5.7 with InnoDB that in 5.5 with MyISAM:

SELECT VisitId,SUM(page.Rating) AS Rating
FROM pageview
INNER JOIN page ON page.Id = pageview.PageId
GROUP BY VisitId
ORDER BY VisitId DESC
LIMIT 500000;

The explain from 5.5 MyISAM is this:

The explain from 5.7 InnoDB is this:

I don't know what can I do to improve performance in 5.7, or why has performance degraded so much.

The first thing that I notice is that the order of the tables is backwards, but also for the page table its not using the PRIMARY index as it was in 5.5 and I think this is the key difference.

Can you please suggest ways to improve this situation?

Update

After following the suggestion by mendosi I still do not get any improvement, here is the updated SHOW CREATE TABLE:

CREATE TABLE `pageview` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `VisitId` int(11) DEFAULT '0',
  `PageId` int(11) DEFAULT '0',
  `ViewDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `PageId` (`PageId`),
  KEY `All` (`PageId`,`ViewDate`) USING BTREE,
  KEY `VisitId` (`VisitId`,`PageId`)
) ENGINE=InnoDB AUTO_INCREMENT=3268418928 DEFAULT CHARSET=utf8

And the explain:


texts: What is the infile SAS equivalent in SQL Server?

texts:
I am trying to read a csv file in my SQL Server Script. In SAS, I can read it using the infile option. (http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000146932.htm)

I know that I can achieve the same thing in SQL Server using BULK INSERT. (https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017) What I can't figure out is some of the equivalent option in SQL.

Specifically, in SQL I want to achieve the functionality of Missover, DSD, FirstObs and LRECL provided by SAS. I am confident that FirstObs equivalent is Firstrow, I am not sure about Missover and LRECL and I cannot figure out DSD all together.

Any help/suggestion much appreciated.


texts: Difference between Create login, credential and user in sqlserver

texts:
I am bit confused about sqlserver Login, Credentials and User.
How they are connected or mapped?

When we are using connection string in asp.net what are we using Login or credentials..

Thank you.


texts: How to handle id's referring to multiple tables in an SQL database

texts:
I'm working on an MySQL Database for my website but I'm not sure on how to proceed now. I get to the point where actions have requirements in multiple other tables, but also have influences in multiple other tables.

In my end design I will have at least 6 tables that an action has potential requirements for and a potential influence on.

Now, there are 2 ways I've thought of it myself:

1 Make action_req_x and action_inf_x tables for every x which would look like this:

action_id ...
x_id ...
minvalue ...
maxvalue ...

Where the minvalue and maxvalue can be changed upon which table it's refferencing. For example, stats would be Decimal(10,3) and items would be Integer(1). This method would require a LOT of tables and I can't believe I've found the best method on my own.

2 Make action_req_x and action_inf_xtables, but only one of each, which would look like this:

action_id ...
x_tablename ...
x_id ...
minvalue ...
maxvalue ...

but this wouldn't be perfect, because different tables have different types of values and I wouldn't know how to setup FK's :( I don't necessarily think this is a really good way to go, but it's better than nothing..

Now, so far I've only talked about the influences and requirements for actions, now I'm not sure yet, but I might eventually want items to have requirements and influences as well, which would require even MOOOORE tables!

My question: Is there a better way to handle this than my first method? Or is there a way I can alter my second method and make that work better? How would database professionals attack this problem?


texts: Creating a stored procedure and adding date manually to filename

texts:
I am new to SQL administration and have been tasked with creating some nightly jobs that send an email with certain details contained within a spreadsheet and so far I have the following:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'support',
@recipients = 'test@mail.co.uk',
@subject = 'Post Code Analysis',
@query = N'SET ANSI_WARNINGS OFF;SET NO COUNT ON;select substring(PostOfficeBox,1,4) as ''Postcode''
, COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as ''New Connections''
, COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as ''Domestic Metered''
, COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as ''Commercial Metered Low''
, COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as ''Commerical Metered High''
, COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as ''Domestic Keypad''
, COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as ''Generator''
, COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as ''Commercial Keypad''
from be_crm4_MSCRM.dbo.AccountExtensionBase as a
INNER JOIN be_crm4_MSCRM.dbo.CustomerAddressBase as b ON a.AccountId =     b.ParentId
where New_AccountStage = 7
and AddressTypeCode = 1
and substring(PostOfficeBox,1,2) = ''BT''
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4);',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'pca-test.csv',
@query_result_header =0,
@query_result_separator = ' '

In the @query_attachment_filename I would like to append the date and have tried using SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] and appending this to the file name as follows:

@query_attachment_filename = 'pca-test-' + SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] + '.csv'

Any advice would be greatly appreciated


texts: Database mirroring is limited to an original database and how many mirror copies?


texts:
I have an app where a user votes between two local profiles on which one is better. I want to ensure that the same user cannot vote more than once between the same two profiles (say the user votes on two different days or something), but I'm not sure what the most efficient way is to do this.

A simple solution is to have a table with 3 fields: voter (the user #), winner (the profile # who won), and loser (the profile # who lost). not necessarily gonna use those names, but I do want to use the data on who won/lost.

Is it horribly inefficient and stupid to send a request for every new matchup to search that table to see if the voter/votees have already been processed? If not, what are my options? Is it really that huge of a deal? Or should I instead allow repeat matchups and simply update the table fields accordingly if the voter's choice changes?