texts: How to evolve MySQL schema while maintaining integrity

texts:
As I understand it, when you run a MySQL DDL statement, it will often lock the table, and then unlock it. It will even unlock it even if you had manually locked it. Also, schema modifications cannot be grouped by a transaction.

Therefore, if you have a migration which removes some foreign key constraints, alters the related tables, and then replaces the constraints with new ones, you could have a problem. If another database client has inserted an invalid value while the foreign key constraints were removed, the new foreign key constraints may not be possible to apply even if the migration SQL was correctly written. That's just one example, but there are probably other ways that this problem could manifest.

How do you deal with this in MySQL? I am using version 5.6.


texts:
When I installed SQL Server 2017 Standard I installed it under my domain account and added it to the sysadmin role. I have then added the Domain Admins AD group to SQL and have deleted my domain account from SQL Server.
Now when I try to login my login fails with error 18456. All other accounts of the Domain Admins group are able to login.
I ran the following query and it says my domain account is type= user, privilege= admin, mapped login name=domain account and permissions path=DOMAIN\Domain Admins
Exec xp_logininfo 'DOMAIN\USERACCOUNT', 'all'

Looking at the server logs it says:

Reason: Token-based server access validation failed with an
infrastructure error. Login lacks Connect SQL permission. [CLIENT:
<local machine>]


texts: performance impact when renaming a column in 100M rows table

texts:
We are running SQL Server 2012 Standard Edition.

Need to change the name of a column in a table with +100M rows.

This table is heavily used at all times.

I can't say at this moment if the column in question is part of an index (should I expect different behavior in case it is?)

What is the performance impact I should expect when running:

EXEC sp_rename 'myTable.oldColumnName', 'newColumnName','COLUMN'

EDIT: This column was created for future needs, we know is it not used in any part of the system. It has only null values. I'm offline right now, not sure if it is included in an index (by mistake)

EDIT 2:
Most transactions on this table have with(nolock) hint. Do you think running sp_rename at isolation lever read uncommitted is the way to go?


texts: Why does the size of the datafile on disk not decrease after deleting records from a table?

texts:
When I delete rows from a table in SQL Server, the size of the database file does not get smaller.  Why is that?  Even if I delete every row from every table, the file size remains just as large as before the delete.

I'm interested in understanding why, not how to shrink the database file(s).


texts:
I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I update this column by an extra query:

UPDATE user_table SET comments = comments + 1 WHERE user_id='x'

Now I want to introduce Triggers to my database instead of running the second query. Two Triggers for each column: one DELETE and one INSERT. Then, I will have 30 - 40 Triggers!!!

Benchmarks show that using Triggers is about 50% faster than executing a second query. Apparently, it is reasonable to use the modern features of mysql. However, the reason that I am asking this question is that many programmers are strongly against Triggers, as it makes coding vague. Moreover, adding lots of Triggers may make Information_Schema messy.

Am I on the right track?


texts: Last accessed timestamp of a Netezza table?

texts:
Does anyone know of a query that gives me details on the last time a Netezza table was accessed for any of the operations (select, insert or update) ?

Thanks


texts: Scheduled event did not start when expected


texts:
We have a very big table which has around 250 million records. The table is growing bigger as every second many records are inserted. There is no update/delete operation done in this table. The table has structure similar to following sample structure

CREATE TABLE `sample_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `BatchNo` varchar(12) DEFAULT NULL,
  `LotNo` varchar(12) DEFAULT NULL,
  `ModelNumber` varchar(20) DEFAULT NULL,
  `Cost` decimal(4,2) DEFAULT NULL,
  `QualityPercentage` tinyint(3) unsigned DEFAULT NULL,
  `Type` varchar(5) DEFAULT NULL,
  `Vendor` varchar(5) DEFAULT NULL,
  `Send_Time` datetime DEFAULT NULL,
  `InsertTime` int(10) unsigned NOT NULL,
  `VendorRating` tinyint(3) unsigned DEFAULT NULL,
  `ProductType` tinyint(4) DEFAULT NULL,
  `SerialNumber` varchar(4) DEFAULT NULL,
  `ItemID` int(11) DEFAULT NULL,
  `BrandID` int(11) DEFAULT NULL,
  `CategoryID` int(11) DEFAULT NULL,
  `SubTitle` varchar(10) NOT NULL DEFAULT 'gl200',
  `Extras` text,
  PRIMARY KEY (`id`),
  KEY `ModelNumber` (`ModelNumber`),
  KEY `SendTime` (`Send_Time`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

Problem

The performance is slower, specially when we select to fetch records of bigger date range (Send_Time). Like if we try to fetch records from One years before to Now, it takes forever before mysql crashes.

We consulted with DBA and he said, as indexes keeps on changing every time as each minute many many records are inserted, the fetching will be slow, so he suggested -

Don't read and write from/to same database table "sample_table".
Create another database table, say "sample_fetch_table" with same structure as "sample_table"
"sample_table" will only be used to insert records from now onwards.
At night when write frequency is very low, fetch last 24 hours record from "sample_table" and insert them into "sample_fetch_table". Then delete inserted records from "sample_table".
Now use "sample_fetch_table" table to select and display records to webpage.
Archive X years old records to another archive table.

UPDATE

The common query we run is:

SELECT *
FROM sample_table
WHERE (    ModelNumber = "12890"
       AND Send_Time > FROM_UNIXTIME(1556515028) AND Send_Time < FROM_UNIXTIME(1556661540))
       AND (   (    Vendor in('11','12')
                AND ItemID = 31
                AND SubTitle LIKE 'ABC-123'
               )
            OR (    Vendor in('ABTU','RAST','RAMT','ABRT','ABNM')
                AND UPPER(SubTitle) IN('RA200','PR55','XY100','TW20','D1EV','FR091','FR093','FRA12','AB23')
               )
           )
       AND BatchNo != 0
       AND LotNo != 0
 ORDER BY Send_time

Explain of above query

id = 1
select_type =  SIMPLE
table = sample_table
type = range
possible_keys = ModelNumber, SendTime
key = SendTime
key_len = 6
ref = (Null)
rows =  202042
Extra = Using index condition; Using where

We are bit skeptical about this suggestion. Is this optimal solution?? Is there any other better alternatives??


texts:
In times of creating replication by using below query I getting from log

Login failed for user 'NT Service\SSISScaleOutMaster140'

DECLARE @distributor AS sysname;
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @directory AS nvarchar(500);
DECLARE @publicationDB AS sysname;
-- Specify the Distributor name.
SET @distributor = N'ServerName'
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = N'ServerName;
-- Specify the replication working directory.
SET @directory = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\repldata'
-- Specify the publication database.
SET @publicationDB = N'databasename';

-- Install the server MYDISTPUB as a Distributor using the defaults,
-- including autogenerating the distributor password.
USE master
EXEC sp_adddistributor @distributor = @distributor;

-- Create a new distribution database using the defaults, including
-- using Windows Authentication.
USE master
EXEC sp_adddistributiondb @database = @distributionDB,
    @security_mode = 1;
GO

-- Create a Publisher and enable AdventureWorks2012 for replication.
-- Add MYDISTPUB as a publisher with MYDISTPUB as a local distributor
-- and use Windows Authentication.
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = N'ServerName;

USE [distribution]
EXEC sp_adddistpublisher @publisher=@publisher,
    @distribution_db=@distributionDB,
    @security_mode = 1;
GO


texts: professional services (consultants) resource allocation system

texts:
I am developing a resource allocation system using PHP and mysql. I have consultants and projects and I need to allocate the consultants on to various projects for various periods of time on various dates.

There are three types of allocation options.

Straight forward allocation where a consultant is assigned to a project from Jan 1 to July 1st.
Multiple allocations, where a consultant will work on the same projects on random dates. Like Jan 1st to Jan 15th, than March 1st to 31st March and finally June 15th to July 1st.
Recurring assignments, where there is a start date and the end date and a consultant is working Monday and Tuesday for 6 months on one project and Wednesday, Thursday and Friday on another project for 3 or 6 months whatever.

Here is what I have done so far.

I have a user table which contains all the consultants.
I have a project table which contains the projects.
I have an assignment table that contains the information about which
consultant is working on which project. It also contains the starting and end date of the assignment
Now I have a fourth table - assignment_days. This contains the data of which days/dates the consultant is working on a project.

Here is my problem. I am thinking of saving all the days/dates in this table. This table contains: id, assignmentid, projectid, consultantid, dates/days and completed. So if there is an assignment for 6 months than I will insert 182 days/dates (records) in this table.
I am not sure if I am doing it correctly or if it is the most efficient way to do it. If this is not the way to do it. Can you suggest a different solution?

And here is the final output i am trying to achieve

This is the list of all the Mondays dates from today onwards for 6 months (which will go to 12 months eventually). It has a number of days a consultant is working on a particular project and at the bottom is the percent allocation or utilization of a resource in a week.

I sure can use someone elses point of view here.

Thanks.


texts:
Is there a way to physically move a PostgreSQL 9.3 tablespace from /old/dir to /new/dir? I'd like to just mv the directory and tell PostgreSQL that the tablespace is now located at /new/dir. Looks like ALTER TABLESPACE only lets you rename.

I'd like to avoid creating a new tablespace and moving the database to it. I assume that would be a slow copy process that requires a lot of time and disk space. I also want to avoid symlinking /old/dir to /new/dir.


texts: Optimizing queries doing filtering on unindexed fields in large MongoDB collection

texts:
We have a large MongoDB collection, 6TB and growing a lot. The collection is used for user and automated feedback, and as such will be used for all sorts of analytics. One document has about 20 fields. Since collection is used for analytics, filtered fields can be of any combination and as such, creating indexes for every combination would be too much.

Is there a good way to index collection for such (random-like) queries? If not, is there a way to optimize queries themselves?

If this can help with optimizing, queries are paged.

Example query:

db.usage.find({"appVersion": "4.0.0.0", "expression": "abcd"})

where appVersion is not indexed.


texts: 'Refreshing' SSRS subscriptions (parameter values not valid due to lack of data in the tables)

texts:
In a place where I work we have lots of subscriptions set for SSRS reports. Some reports have as many as 100 subscriptions set up.

Our data warehouse is refreshed every night, with all the data being dropped and loaded in again.

Because of that, on occasion when things go wrong and the data isn't loaded up in the morning when the subscriptions are supposed to run, they are unable to fill in the parameters and get bugged like this:

Now to fix them, we have to manually open every subscription and hit save again. Sometimes this is not enough and we have to manually set up every parameter again. This is very frustrating and time consuming. So this brings me to my question:

How to prevent this? Is there a way to automatically 'refresh' all subscriptions? Other solution I have thought of, but lack the knowledge of ReportServer db to execute is:

Perhaps run a script before the subscriptions are set to execute. The script would check if data exists in warehouse database tables. If there is no data, delay subscriptions for an hour and then try again. Is this doable? If it is, I would be extremely grateful for a query that gives me access to execution times of subscriptions. I tried and couldn't build one myself.

Any other solutions are extremely welcome, thanks in advance!


texts: When should I reference the User record or User Profile record

texts:
I know it is a common paradigm to separate tables when building a User profile. For example, having a user table, and another table called user_profile with a foreign key to the user table.

My understanding is that the user table is better for sensitive user account data or authentication data, such as email, password, user type etc. The Profile table could have additional data of that user like first name, last name, date of birth and more.

But, what about other data that can be related to the user, and can also be modeled with a table. Some examples could be Payments and Transactions. My first guess is to link those to the profile table and that way I don't have to make any joins just to have Transactions and the name of User together (which the user table does not have). Linking to the profile, I have the user info and the Transactions. But then, when is it useful to link to the user table? What are the common paradigms?

Thanks in advance!


texts:
I have been learning about SIMPLE and FULL matching foreign keys in PostgreSQL and I am wondering if the following thought process is correct:

Once at least one of the referencing columns of the foreign key contains a NULL value, there is no link to the referenced table. This is because a NULL is not comparable with another NULL value, as per 3VL. This also means that defined cascading actions (such as ... DO DELETE, ... DO SET NULL, ..) when a referenced row which contains at least one NULL value in the relevant columns, is updated or deleted will never happen, because there is no link to the referenced row. More concrete, assume a foreign key from table A(x, y) to B(x, y). If both A and B contain a row (5, NULL) for x and y, there is no link, because the NULL for the row in A is not equal to the NULL for the row in B.

Is this correct? I am quite new to SQL, so I am wondering if I understood this correctly.


texts: How do I copy a schema for an instance of Oracle running in a Docker container?

texts:
Pursuant to this question:
Is this package missing and if so, how do I fix?

...now I actually want to perform the copy of a schema using this package. ...except I can't figure out how to do that. I've seen this: How to create copy of full schema on same database in oracle but I can't seem to execute the commands. As mentioned in the previous question, we are running Oracle in a docker container, so this might be complicating matters. Again, I'm using DataGrip to connect. Since it's in a container, all of our connections are from remote. ...am I going to have to get into the container somehow? Docker is also something that is outside of my expertise.

I've tried executing:

expdp fromUser/fromPword schemas=sourceSchema directory=DUMPDIR dumpfile=dump.dmp logfile=explog.txt

...as well as...

exec expdp fromUser/fromPword schemas=sourceSchema directory=DUMPDIR dumpfile=dump.dmp logfile=explog.txt

I've also created the directory object DUMPDIR and granted read/write to it.


texts: Removing secondary data files. DBCC SHRINKFILE: Page could not be moved because it is a work table page

texts:
I have too many secondary data files (.ndf) created for tempdb. To remove the excess files, I need to empty the file (content will be moved to other files):

DBCC SHRINKFILE('tempdbfile8', EMPTYFILE);

and then delete the file:

ALTER DATABASE tempdb REMOVE FILE tempdbfile8;

But EMPTYFILE command returns the error:

DBCC SHRINKFILE: Page 8:41920 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 2
Cannot move all contents of file "tempdbfile8" to other places to complete the emptyfile operation.

Not to worry, I just need to locate the object that's using this page to do something about it:

DBCC TRACEON (3604)
DBCC PAGE(2,8,41920) --dbid=2, fileid=8, pageid=41920

The command returns a lot of information, object_id among them. But:

Metadata: ObjectId = 0

I have no idea what to do about it. What cat be preventing this page from being moved? How to locate that object, process, session or whatever it is?
Any help will be appreciated, but please note that leaving everything as it is or removing other file instead is not a valid solution to this problem ;).

EDIT:

I'm removing the files, because we used to follow "best practice" of creating one file per processor core (same initial size, same growth rate). But as far as I know, until you run into contention problems, there's no point to create additional tempdb files on the same device. In our case it makes sense, because we have MPIO turned on, and storage device can handle 4 paths.
But there was a mistake, and we ended up with total 5 files with 6-core cpu. It's more than MPIO paths, less than CPU cores, and it's not an even number. It may not cause any problems, but just doesn't seem right :).

I was finally able to empty and remove the file without restarting the server by setting one of the datatabases (that I suspected of causing the problem) to single user mode (rollback immediate). It worked, but I got lucky.
What I really want, is to be always able to track the page down :).


texts:
My application has 4 different threads doing inserts into the same DB2 table simultaneously.

Could this cause a deadlock / lockwait situtation?

Theoretically I don't see a problem here; as one insert statement is not going to lock a row required by another insert statement because all are independent insert statements.

Also, I commit after each insert so transaction log wouldn't fill up.

I'm looking for a high performance model while avoiding any potential lock situtation. Please provide your thoughts.


texts: DIRECT and SORTED in H2 and How to insert data with SORTED technique in H2

texts:
I was working on H2 Database and read Insert Statement Grammar and found that there are 2 types of insertion DIRECT and SORTED @ http://www.h2database.com/html/grammar.html#insert.

As I know that there are no real Stored Procedures in H2 and Derby so I am trying my Queries as fast as possible so my questions are

1) What is this DIRECT and SORTED insertion?

2) How to insert data with SORTED technique?

3) Do I have to set some kind of Table definition for this or what?


texts:
Is it necessary to do a database integrity check (DBCC check) on mirrored databases?

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/automatic-page-repair-availability-groups-database-mirroring?view=sql-server-2017

  Automatic page repair is supported by database mirroring and by Always
  On availability groups. After certain types of errors corrupt a page,
  making it unreadable, a database mirroring partner (principal or
  mirror) or an availability replica (primary or secondary) attempts to
  automatically recover the page.

What is you best practice?


texts: Most efficient way of caching queries / processed data

texts:
I have doubts on database design.

I'm trying to know if there is a way to increase query performance, and the easiest way for me to explain my doubt is with a small example.

I have a news website with news posts, and those have comments. The Comments table is like this:

Table Comments:
comment_id | text | post_id | user_id | ...

This news website has in its mainpage two rankings, making them heavily read, but with a low number of writes:

One with the most commented news posts (The 10 most "popular" news posts)
Another with the users with most comments (The 10 most "active" users)

The "normal" way to do this is to create the appropriate queries and leave them on that index page:

Top10 of Posts:
SELECT TOP 10 post_id, COUNT(comment_id) AS num_comments
FROM Comments
GROUP BY post_id
ORDER BY num_comments DESC

Which could be heavy to process, since it has to process ALL the news comments rows every time the page is accessed. Well, I'm not sure about this because I don't know how efficient caching is.

That's why I'm thinking in a alternative, that would be to create two tables, that would have the processed data for those two rankings.

Table Comments_PostsTop:
post_id | num_comments

Table Comments_UsersTop:
user_id | num_comments

Would this be a good alternative?

If so, is there an automated way to update these tables based on updates on the Comments table besides triggers? Or are triggers the only good way to achieve this?

Or is caching enough to keep high performance? (SQL Server 2014)

Or is there another model that could be more efficient?

EDIT:
After accepting the answer, I'm posting how I created and queried the materialized view.
It's based on the steps provided by the documentation:
https://msdn.microsoft.com/en-us/library/ms191432.aspx

Creation

--Create view with schemabinding.
IF OBJECT_ID ('dbo.commentsposts', 'view') IS NOT NULL
DROP VIEW dbo.commentsposts;
GO
CREATE VIEW dbo.commentsposts
WITH SCHEMABINDING
AS
    SELECT post_id, COUNT_BIG(*) AS num_comments
    FROM dbo.comments
    GROUP BY post_id;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
    ON dbo.commentsposts (post_id);
GO

Query

--Normal query
SELECT post_id, COUNT_BIG(*) AS num_comments
FROM comments
GROUP BY post_id;

--Query for the view
SELECT [post_id],[num_comments]
FROM [teste].[dbo].[commentsposts];

--But SQL Server is not using the view, so we need to force its usage
SELECT [post_id],[num_comments]
FROM [teste].[dbo].[commentsposts] WITH (NOEXPAND);

Only on this 3rd query the query selects the data on the view.
The first two are the same according to the estimated execution plan.


texts: using port range for connecting to Postgres

texts:
I am new to postgres and I would like to use it with postGIS. It all works perfectly. The default port I use is 5432. I would like to know if it is possible to use multiple ports i.e 5430-5431-5432-5433 ...etc or a range i.e 5430-5 to connect to the database.
The reason behind this is that I am trying to have several updates from different sources and I would not like o have a bottle neck using the same port.


texts: What does it mean by '4M blocks' or '4G blocks' in Oracle?

texts:
The following 2 sentences are excerpt from Oracle documentation. You can easily fine them by google them directly. My question is, 4G and 4M are the unite of digital information storage. What's the meaning in the context of the following sentences?

Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks.
A smallfile tablespace can contain up to 1022 files with up to 4M blocks each.

Thanks.


texts: Oracle average active sessions high threshold

texts: I am trying make sense of the DB load from the AWR report for a period handed over to me by DBA. Researching a bit, I see the "Average active sessions" is a metric widely used to measure DB load. It is calculated using (Total DB time/Total wall clock elapsed time). From the AWR, in my case it came a little over 7. Does this number indicate high DB activity? Or do I need to take into account anything else?


texts: Find history of rollbacks on SQL Server 2012

texts:
Is there a way to find the history of any and all rollbacks that happened on a SQL Server database? Specifically, I would like to know the time and the user.
Thanks.


texts:
So a join table for many-to-many is typically(?) a two-column table with foreign keys to two other tables.

I have a colleague that insists on putting an FK in two tables that are FKs to a "join" table that contains only a column of PKs.

Example: A and B have FKs to join table C.  A and B are joined by having a mutual C FK.  An m::m can be represented like rows A1=>C1, A2=>C1, A3=>C2; B1=>C1, B2=C2, B3=>C1, B4=>C2

The many-to-many is then  A1,A2::B1,B3; A3::B2,B4

I guess the problem is if you later want to join A1 to B2, it's not possible because A1 and B2 are already bound to different FK's in C. But this might be okay if A's and B's are immutable.

Hope that make some sort of sense :-P


texts:
On my production server, this query returns no results, so I took it to SQLFiddle to test the results with test data to see if maybe it was a conversion issue.  Well SQLFiddle throws an error anytime I attempt to run this query which leads me to believe that the issue is with my syntax.

Using the subset sample data listed below, I want to return userID & Name from my table named blue where the users title is like '%Manager%'  (I have also tried to use is ILIKE '%Manager%')This is sample DDL that returns no results but if you look at my sample data you can see that I should have these two results returned:

1 Bob
3 Mike

However, my query returns 0 results.  What do I need to edit in order for my query to return relevant results?

CREATE TABLE blue(
  userID INT PRIMARY KEY     NOT NULL,
  name text NOT NULL
);

CREATE TABLE RED(
  userID INT PRIMARY KEY NOT NULL
  ,title text NOT NULL
 );

 INSERT INTO blue (userID, name) VALUES
 (1, 'Bob'), (2, 'Joe'), (3, 'Mike'), (4, 'Sam'), (5, 'Bridge');
 INSERT INTO RED (userID, title) VALUES
 (1, 'Manager'), (2, 'Field Operator'), (3, 'Manager II'), (4, 'CFO'), (5, 'New Hire');

And this is the query I tried to run

Select *
FROM blue
where userID IN (SELECT userID
                 FROM RED
                 WHERE title ILIKE '%Manager%')

This works fine on SQL Server, but it will not work on my PostgreSQL install (which is where my database resides). I was getting the "Something went wrong" error.


texts: MS SQL replication table change causes failed drop/create due to constraints

texts: I have a simple SQL server 2016 merge replication with one publisher and one subscriber. I changed a  replicated table and followed the custom instruction of removing the table from the replication, editing the table, re-adding the table to the replication and creating a new snapshot. To my surprise the replication was not able to properly handle the change. When I looked at the schema script that was built to update the subscriber database I saw that the replication tried to drop and re-create the whole table which cannot work because of foreign key restraints. With ALTER commands this should have been possible. Is there a way to handle such table changes without completely deleting the subscriber database and re-creating the subscription (which would be my brutal but working solution for now)?


texts: How to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql?

texts:
I've a situation where 4 parameters are passed to a stored procedure, on the basis of the existence of these parameters i need to modify the where condition of my select query.

Simply i can use IF-ELSE statement and write the select combinations in it.

Is there any way in sql to avoid this select combinations(all combinations with 4 parameters)?


texts: How to decompose this relation into 3NF relations?

texts:
Today I read about the 3NF decomposition algorithm. It said:

  Find a minimal basis of F, say G
  For each FD X → A in G, use {X, A} as the schema of one of the
  relations in the decomposition
  If none of the sets of relations from Step2 is a superkey for R, add
  another relation whose schema is a key for R

I want to decompose this relation into 3NF.

R(A,B,C)
S={A→B, A→C, B→A, B→C, C→A, C→ B, AB→ C, BC→A, AC→B, A→BC, B→AC, C→AB}

As we can see, the key of R is: {A},{B},{C}

S has several minimal basis, such as:

{A→B, B→A, B→C, C→B}; and
{A→B, B→C, C→A}

The problem is, if we use the 1st minimal basis, then we decompose R into 2 relations: (A, B), (B, C).

If we use the 2nd minimal basis, R turns into: (A, B), (B, C), (C, A).

My question is: which one is correct?


texts: Tran Log file growing even after log backups and Log shipping configured

texts:
I have log shipping configured for a database in SQL server 2005 with below details:

A) Database X hosted on primary: Log backup every 1 hour as scheduled in LS Backup job:

Data file: 40 GB and Log file: 15 GB (Initial file size:13,701 MB with 10 % increment growth)

B) Per Log shipping that Database X is hosted on secondary as X_DR whose log file is also the same and due to shortage of space on this server we get continuous alerts for low disk space.

Therefore to control this Log growth can someone please guide me how to proceed:

Analysed the log file usage on primary and found that for that duration of one hour where log backup will occur, the max log used from its define size is 3-4%! So does that mean give amount of log space is not being used? if yes can i change the initial size to approp value in live prod DB as of now?
If point 1 is not valid should i go with shrinking log files as they grow quite large. Don't want to use the option 2, but kindly help me with any suggestion/solution


texts: Full GROUP BY in MySQL 5.7 breaks de-dupe queries

texts:
Prior to MySQL 5.7, I was able to dedupe queries by using GROUP BY as long as the query results were done in such a way that the duplicates had the same data and were already in the sort order I wanted.

I got really good at that.

Now, with 5.7 and only_full_group_by mode, those queries are invalid. I've already been through the 5 stages of grief over it, and am working to update / improve my queries to be more standard.

the problem is: when I do a LEFT JOIN (in some cases) it produces rows and rows of (essentially) duplicate information. I get that they are not technically duplicates because some column may be different. And, I see a lot of questions about this in a general way. One such use case is "find the highest value for X in the day." You used to just use ORDER BY x DESC with a GROUP BY  and poof you had it. Now, you have to do LEFT JOINs and look for NULLs. (Part of me still wonders "why is this better?" but that's off topic).

....but I want to know what best practice replaced using GROUP BY to remove these duplicates? SELECT DISTINCT?


texts: Replicating a Table Structure With no Data Using Merge Replication

texts:
I am using merge replication with SQL 2012.

I have a table, and it would be useful to propogate that table to the subscribers, but just the table structure, not the actual data.

The data in the table is populated per session, i.e. when you log into the software data is added into the table, and then deleted when you exit the software.

What this means is that the data in the table shouldn't be replicated from publication to subscriber, or from subscriber to publication. But it would be good for the table structure to be replicated so that we don't have to run a script manually at the subscribers.


texts:
I need to find a way how to identify SQL Instance no matter what changes were done to SQL Server and\or Windows Server where it is running, because we're collecting remotely information about instances into our operational database and still encountering way we're identifying it is not that unique as it might look.

In example, if I have server WIN1\SQL and due to server refresh will install on different server WIN2\SQL, migrate data and swap server names so that WIN2\SQL will become WIN1\SQL and vice versa, I need to be able identify that current WIN1\SQL is different from previous WIN1\SQL disregards OS version and or SQL version\patch level

Already tried following:

NetworkName\InstanceName - that worked until server hostname\network name has been changed due to server refresh as described above

SQL Server SID - I was searching for some SID of SQL Installation like Windows has, but seems nothing like that really exists

SID of ##MS_SQLAuthenticatorCertificate## - this was last best shot I found, scanned hundreds of SQL Servers and it was really unique - thought I found it finally, until I applied Service Pack to one server and find out SID has changed

Any idea how to uniquely identify SQL Instance (like SQL Instance SID) would be appreciated, because I'm getting lost

EDIT: I'm not administrating all SQL servers. Modifications on out of scope devices cannot be done, thus looking for some "already built-in" way that would work on SQL2005+


texts: Database design - many-to-many relationship between three columns

texts:
I'm noob in database. So don't mind if its a silly question.This is used in my application and is the first one. I need a database design for it.

value 4 is dependent on the other value 1,2&3. even if one of the value changes the combination changes and the fourth value is affected. Each of the value 1,value 2,value 3 are independent on each other. Many-to-many relation between value 1,value 2 and value 2,value 3. value 4 is not derived or computed but a distinct value for all the combinations. So how can I design a database for it?


texts: Locate multiple duplicate columns in PostgreSQL table

texts:
I am trying to report on duplicate records in a single table which has a unique key of app_cao_number.  The duplicates occur if either: 1. The Passport field is duplicated; 2. The ID field is duplicated, or; 3. The Surname+FirstName are duplicated.

I can do this easily enough with three passes of the table using ORDER BY.  But I am hoping to use a single SELECT statement, with subqueries, to do the job.

Starting with just finding duplicate IDs I have the following statement:

SELECT app_cao_number, app_id,
    (SELECT app_id FROM people p2
        WHERE p2.app_id IS NOT null
        AND p2.app_id <> ''
        AND p1.app_cao_number <> p2.app_cao_number
        AND p1.app_id = p2.app_id
        GROUP BY p2.app_id) AS DupId
FROM people p1
WHERE app_id IS NOT null
AND app_id <> ''

This appears to get me the results that I want, but also include rows that have a null DupId - despite my attempts to ignore blank and null values in the SELECT statement.  Once this works I should be able to expand it to include the passport and name checks.

Please can someone explain why I have the following data output with nulls in the DupId column?  Thank you.

Further:
I thought it might be the GROUP BY clause, but I replaced it with a DISTINCT clause (below), but this gave the same result.

(SELECT DISTINCT p2.app_id FROM people p2
    WHERE p2.app_id IS NOT null
    AND p2.app_id <> ''
    AND p1.app_cao_number <> p2.app_cao_number
    AND p1.app_id = p2.app_id
    ) AS DupId

UPDATE

sample fiddle


texts: Timing issues for backups during recovery

texts:
Let's say we did a full database backup of the PRIMARY filegroup at 10:20 am. It finished at 10:45 am. We also took log backups every 5 minutes, hence
10:15, 10:20, 10:25, 10:30, 10:35, 10:40, 10:45,...
Then we do a full backup for secondary filegroup at 10:50am it finishes at 11:00am.

Now I try to recover a database using full filegroup and log backups. I take the full backup of PRIMARY done at 10:15, restore with norecovery. What should I restore next? The logs until 10:45 with norecovery? then the second full backup after that all the logbackups after 11:50am  till the tail log with recovery? Is it the right way or should I do something else?

Thanks!


texts: postgresql : find privileges in meta informations

texts:
With PostgreSQL 9.4 on linux centos 6.6.

I give a privilege to a user with something like :

ALTER DEFAULT PRIVILEGES FOR ROLE DBNAME_adm IN SCHEMA DBNAME_adm GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO DBNAME_usr;

I need to have a SQL statement to find if this privilege is already ok or not.
I did not find any table in pg_catalog nor information_schema which contains such an information.

Where can I find default privileges metadata ?


texts: Understanding Set Returning Function (SRF) in the SELECT List

texts:
Why is there  a difference in behavior between using a Set Returning Function (SRF) in the SELECT list vs using SRF in the FROM clause?

For example, for a simple SRF returning 2 rows:

CREATE OR REPLACE FUNCTION gen_series(out integer, out int)
  RETURNS SETOF record AS $$
  SELECT 1,1
  UNION
  SELECT 2,2;
$$ LANGUAGE SQL;

SELECT gen_series(); returns two single column rows each containing a record:

=>  gen_series
------------
 (1,1)
 (2,2)
(2 rows)

Whereas SELECT * FROM gen_series(); returns two rows with the record expanded:

=>  column1 | column2
---------+---------
       1 |       1
       2 |       2
(2 rows)

By comparison, if the SRF is returning a single column, then calling the SRF in the SELECT or FROM clause makes no difference. e.g.:

=> SELECT generate_series(1,2);
 generate_series
-----------------
               1
               2
(2 rows)

=> SELECT * FROM generate_series(1,2);
 generate_series
-----------------
               1
               2
(2 rows)

My questions are:

I don't quite see why in the second case, the SRF behavior is different from the first case just because the returned table has a single column. Is this really consistent behavior in terms of types, tuples and sets?
What's the difference between the two cases that leads to the different behavior?
SRF can be used as tables as shown above, but can tables be used to replace SRFs as well? e.g.

SELECT my_table;

Apparently, this can't be done, but why is SELECT my_SRF(); possible, whereas
SELECT my_table; is not allowed (in terms of relations and mathematics)?


texts: mysql master-slave replication and status of data consistency

texts:
I have setup a master-slave replication with mysql 5.7 and GTID.

GTID should be more "safe" than the older way where each statement alone was replicated right?

But when replicating with GTID can I then check the data consistency on the slave just by checking SHOW SLAVE STATUS\G ?

Or do I need to checksum each row in each table to make sure everything is replicated correctly?


texts: How to get records from corrupted ibd file

texts:
Ok, in the last two days I thoroughly searched through the internet, and found multiple propositions on how to handle this, but nothing worked so far.
Here is what the problem is and what I tried so far:

The company I worked for had a sudden crash of mysql, I do not know any more about this, just that the service was not working when I got on the server.
Looking at the mysql log I could see the following line being responsible for the error:

InnoDB: Error: trying to access page number 64 in space 709,
InnoDB: space name db/tb_data,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.

Ok, so its a data corruption. First thing I tried, was to get mysql running again, which finally worked with innodb_force_recovery=4.

Then I connected via command line to dump all the data I could with mysqldump which worked fine except for the huge data-table, which is made up of the columns ID, record_id, field_id, content, changed_user_id, changed_timestamp.

This table links the changeable fields of the applications database and the records to the actual content of the field, and stores metadata for a history-feature. So it is the heart of the application, storing all its data.

Now the first approach I read was to dump the data and recreate the table, but dumping the data crashes mysql again with the same error (as well as check table and optimize table by the way).

So I dumped all the data I could up until the last ID I could select without the server crashing, and got all that data in an sql-file. However it is even less, than the data from the backup of four months ago, so it is not really an option to use this.

Another approach I took was to get the corrupted table out of the database, recreate it with the schema and discard the tablespace, to try and import the old one, which gives me this nice error:

ERROR 1034 (HY000): Incorrect key file for table 'tb_data'; try to repair it

After some googling of maybe how to get it to work, no chance. So also a dead end.

At this point I am simply trying to recover as much data from the ibd file as possible, I'm working on windows machines only, and I can't seem to get percona data recovery to work with cygwin. I am pretty much at the end of my wisdom here and I don't know how I should further approach this situation. The database is fine without the ibd-file, and I have the frm-file as well as the ibdata file and the corrupted ibd-file of course. But I can not seem to find any tool, working on windows, to get the records from the ibd-file.

I would be very thankful for any help on this, even if its with getting percona to work (documentation seems to be limited to the readme-file and all wiki-links are dead ends). I will of course supply as much information as you might need to assess the situation.

EDIT: I was just on the phone with the company I work for, and for what its worth, they think just before mysql shut down the database was repeatedly filled with new records from an import of one of the machines data. It is limited to inserting new rows into the records table and data table. If this info is of any help. The records table is still intact. I will check it immediately at home to see how many records were added before the crash.

EDIT2: There were about 36000 inserted records, over a time of 3 hours, beats me how nobody noticed this. This should have created about 2.3 million rows in the data-table. I don't see a problem there. It was all insert commands.
The ibd-file is 6GB by the way. And there was a fulltext-index on the content column. Trying to supply as much info, that could be of interest, as I can collect.


texts: How do i specify a type for a new column through a select statement

texts:
I'm trying to create a new view from an existing table which as 2 columns:

CREATE TABLE writers (
  movie_id integer NOT NULL REFERENCES movies,
  person_id integer NOT NULL REFERENCES people,
  PRIMARY KEY (movie_id,person_id)
) ;

I'm trying to make a view which adds a new column and a default character value so I want a the new to have the columns  movie_id, person_id, position_name  and I need position_name to be of type character so that I can union this view with another view.

Is this possible to do?


texts: Is it possible to run MongoDB javascript scripts in DBeaver

texts:
DBeaver's wiki shows a simple query against a Mongo database but what about admin scripts?  It would be great to develop these in DBeaver but I've tried a few permutations to no avail.

Here's an example of a maintenance script:

var cursor = db.album.find({}, {"k": 1 })
var albumKeys = []

while( cursor.hasNext() ){
    albumKeys.push( cursor.next()['k']);
}

cursor = db.song.find({ "alb" : { $not : { $in : albumKeys } } } )
var songsWithMissingAlbum = []

while( cursor.hasNext() ) {
    songsWithMissingAlbum.push(cursor.next())
}

printjson(songsWithMissingAlbum)


texts:
Im looking for advise because my experience is not really good in SQLite.

My table look like this:

CREATE TABLE IF NOT EXISTS Route (
  id integer PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL,
  name text NOT NULL,
  localizedName blob NOT NULL
);

Im storing the localized name of the route as a binary blob in the localizedName property(it can hold multiple strings like English, French etc...). then to retrieve it I use a special SQL function

SELECT * FROM Route WHERE tkLocalizedString('en', localizedName) LIKE ?1;

  tkLocalizedString is a function written in C programming language and is added to SQLite at runtime and it accept 2 parameters, the first is the language code (ISO 639-2), the second is the localized string blob

  localizedName is a binary string table. The key is the language id and the value is the string.

Is this a good practice? Is there a better approach?


texts: Insert dummy data based on data time found in column

texts:
while doing data collection, I've come to realize that some of the data are missing and could not be re-collected due to some issue with the program. One of the suggestions given was to replace the missing data with dummy data. An example is given below to better explain my issue:

the data is intended to contain data from 00:00hrs till 00:00 the next day. However, due to the shortcomings of the program, the data could not be retrieved. Is there a way too use the time in the data column to insert in dummy data? Using the data in the red box as an example, the dummy data would fill up from timings, 00:00hrs - 05:00 and 14:30 - 00:00 on the 15 of Aug.

I'm thinking of using CTE to complete the task, however, I'm unsure of how to do it.


texts: Create simple database with secure data entry GUIs

texts:
I need to create a relatively simple database management environment with a secure graphic user interface, including forms, for data entry. In my co What has been used since to fill this need is an Access database file stored on a shared drive, which was made by a partner not me. Several issues are stemming from the fact that the database is locked down for security, so no updates are possible to the interface. I cannot fix any form bugs, and I keep finding them.

What are the best resources to meet my needs?
They are:

data must absolutely be secure
data integrity is fundamental so I need every entry/edit action by data enterers to be securely stored
I know Python, SQL and have used Mongo before
-no user including the admin may have backend access to change the data without a trace (like you can in Access)
I need to be able to produce something very quickly and continue to fix bugs if they occur


texts: moving large PostgreSQL/PostGIS database

texts:
I need to move and upgrade a very large (~320 GB) PostGIS database from server1 (PostgreSQL 9.1, PostGIS 1.5) to server2 (PostgreSQL 9.3, PostGIS 2.1).

The upgrade process is well-documented. The problem is I don't have enough space on server1 to dump the file there, checksum it, then copy it to server2 and verify sums. I tried:

Piping the dump from server1 to server2 using nc.
Writing a dump file directly to a server2 filesystem which is mounted on server1 using sshfs.

Both times the dump file appears to have been corrupted. pg_restore broke in different places with errors like this:

pg_restore: [compress_io] could not uncompress data: incorrect data check

Can anyone suggest a better way to get this move and upgrade done?

UPDATE: Tried NFS (and gave SSHFS another try). It's clear that these remote filesystems can't reliably transfer this much data. Blocks are visibly missing from the resulting SQL file, causing syntax errors like this during import:

ERROR:  invalid input syntax for integer: "8266UPDATE spatial_ref_sys o set auth_name = n.auth_name, auth_srid = n.auth_srid, srtext = n.srtext, proj4text = n.proj4text FROM _pgis_restore_spatial_ref_sys n WHERE o.srid = n.srid;"


texts:
Some minutes ago I've tried to do an export but I get the following error (AIX system):

Additional information: 3
ORA-31693: Table data object "XX"."TABLE_NAME" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/dir/back/dump/dump_xx_04.dmp" for write
ORA-19505: failed to identify file "/dir/back/dump/dump_xx_04.dmp"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory

What could the reason for this behaviour be?

I've read in some blog that using just parallel=N without %U that the export is actually slower. Is that true?


texts: stored procedure logic,data flow visualization tool

texts:
I have a very poorly written stored procedure with cursors, if else etc. Is there any toll available to visualise the logic and data flow?

Thanks


texts:
I have a very simple website running PHP and SQLite 3.7.9 (with PDO).

After establishing the SQLite connection I immediately execute the following queries:

PRAGMA busy_timeout=0;
PRAGMA cache_size=8192;
PRAGMA foreign_keys=ON;
PRAGMA journal_size_limit=67110000;
PRAGMA legacy_file_format=OFF;
PRAGMA page_size=4096;
PRAGMA recursive_triggers=ON;
PRAGMA secure_delete=ON;
PRAGMA synchronous=NORMAL;
PRAGMA temp_store=MEMORY;
PRAGMA journal_mode=WAL;
PRAGMA wal_autocheckpoint=4096;

This website only has one writer and a few occasional readers, so I don't expect any concurrency problems (and I'm even using WAL). Every couple of days, I've seen this error being reported by PHP:

  Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 10 disk I/O error' in ... Stack trace: #0 ...: PDO->exec('PRAGMA cache_si...')

There are several things that make this error very weird to me:

it's not a transient problem - no matter how many times I refresh the page, it won't go away
the database file is not corrupted - the sqlite3 executable can open the database without problems

If the following pragmas are commented out, PHP stops throwing the disk I/O exception:

PRAGMA cache_size=8192;
PRAGMA synchronous=NORMAL;
PRAGMA journal_mode=WAL;

Then, after successfully reconnecting to the database, I'm able to reintroduce these pragmas and the code with run smoothly for days - until eventually, the same error will occur without any apparent reason. I wasn't able to reproduce this error so far, so I'm clueless about the origin of it.

I'm really curious what may be causing this problem... Any ideas?

Environment:

Ubuntu Server 12.04 LTS
PHP 5.4.15
SQLite 3.7.9

Database size: ≃ 10MiB
Transaction (write) size: ≃ 1KiB

EDIT: Might these symptoms have something to do with busy_timeout?


texts:
I'm confused about which mysql engine should I choose (lets talk about the most used : MyISAM and InnoDB).

The theories say:

Both work with BTree indexes, but MyISAM can work with FULLTEXT index also.
InnoDB is more strict in data integrity while MyISAM is loose.
InnoDB has transactions while MyISAM does not.
InnoDB has foreign keys and relationship contraints while MyISAM does not.
MyISAM are faster to read but slower to write (I'm not sure about this one).

When I create a table, should I always sacrifice something if I want to keep integrity and I want to speed up the query making search by "text"?

How do you decide which engine to use if you need

integrity ?
speed ?
constraints ?
search by text ?
transactions ?


texts:
I have these settings in my.cnf:

[mysqld]
...
disable_log_bin
general-log      = false
general_log_file = /var/log/mysql/mysqld.general.log

long_query_time     = 1
slow_query_log      = false
slow_query_log_file = /var/log/mysql/mysqld.slow.query.log

[safe_mysqld]
log-error = /var/log/mysql/mysqld.log

[mysqld_multi]
...
log        = /var/log/mysql/mysqld_multi.log

Each time I start the MySQL server an empty file /var/log/mysql.log is created and it never gets filled.
I was searching in web for some solution but I don't find anything regarding the mysql.log as it seems to be outdated.

Can it be it's created by a mistake, again, as I don't find anything regarding mysql.log for MySQL 8.x or do you know how to avoid the creation?

I am running MySQL 8.0.20


texts:
Is there any way to get a progress measure or remaining time estimate from mongorestore?

I used mongodump to export a compressed archive, which generated an 8GB file in about an hour and showed a progress bar, and now I'm trying to load the archive on a separate staging server. However, even though the dump contains no indexes, the restore has been running for over 6 hours.

My restore command is simply:

mongorestore --drop --gzip --archive=dumpfile

From what I've read, mongorestore is terribly inefficient, so long restore times is a common complaint. However, it would be nice to know if I have to wait a few more hours vs a few more days.

The manpage doesn't list anything like a --progress option. Is there any way to get even a rough estimate for the remaining restore time?


texts: Query not using index with jsonb - Postgres

texts:
For some reason it seems as Postgres is not using the index we created. This is the query I'm testing:

SELECT  "public"."influencers".*
FROM "public"."influencers"
WHERE (ig -> 'id' @> '"4878142508"')
LIMIT 1

And after running EXPLAIN:

    ->  Seq Scan on influencers  (cost=0.00..32800.14 rows=216 width=1110)

Which indicates (as I see it) that no index is being used.

This is our database and indexes created:

CREATE TABLE public.influencers
(
    id integer NOT NULL DEFAULT nextval('influencers_id_seq'::regclass),
    location jsonb,
    gender text COLLATE pg_catalog."default",
    birthdate timestamp without time zone,
    ig jsonb,
    contact_info jsonb,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    categories text[] COLLATE pg_catalog."default",
    search_field text COLLATE pg_catalog."default",
    search_vector tsvector,
    ig_updated_at timestamp without time zone,
    CONSTRAINT influencers_pkey PRIMARY KEY (id),
    CONSTRAINT ig_id_must_exist CHECK (ig ? 'id'::text),
    CONSTRAINT ig_username_must_exist CHECK (ig ? 'username'::text)
)

And the indexes created (relevant index first - the one I think it should be used):

CREATE INDEX idx_btree_ig_id
    ON public.influencers USING btree
    ((ig ->> 'id'::text) COLLATE pg_catalog."default")
    TABLESPACE pg_default;

CREATE INDEX ads_search_idx
    ON public.influencers USING gin
    (search_vector)
    TABLESPACE pg_default;

CREATE INDEX ig_last_post_location_aal1
    ON public.influencers USING btree
    (((ig -> 'last_post_location'::text) -> 'administrative_area_level_1_id'::text))
    TABLESPACE pg_default;

CREATE INDEX ig_last_post_location_aal2
    ON public.influencers USING btree
    (((ig -> 'last_post_location'::text) -> 'administrative_area_level_2_id'::text))
    TABLESPACE pg_default;

CREATE INDEX ig_last_post_location_aal3
    ON public.influencers USING btree
    (((ig -> 'last_post_location'::text) -> 'administrative_area_level_3_id'::text))
    TABLESPACE pg_default;

CREATE INDEX ig_last_post_location_country
    ON public.influencers USING btree
    (((ig -> 'last_post_location'::text) -> 'country_id'::text))
    TABLESPACE pg_default;

CREATE INDEX ig_last_post_location_locality
    ON public.influencers USING btree
    (((ig -> 'last_post_location'::text) -> 'locality_id'::text))
    TABLESPACE pg_default;

CREATE UNIQUE INDEX ig_unique_id
    ON public.influencers USING btree
    ((ig ->> 'id'::text) COLLATE pg_catalog."default")
    TABLESPACE pg_default;

CREATE TRIGGER influencers_search_vector_update
    BEFORE INSERT OR UPDATE
    ON public.influencers
    FOR EACH ROW
    EXECUTE PROCEDURE tsvector_update_trigger(s, e, a, r, c, h, _, v, e, c, t, o, r, \, 0, 0, 0, p, g, _, c, a, t, a, l, o, g, ., s, i, m, p, l, e, \, 0, 0, 0, s, e, a, r, c, h, _, f, i, e, l, d, \, 0, 0, 0);

CREATE TRIGGER update_search_field_influencers_on_insert
    BEFORE INSERT
    ON public.influencers
    FOR EACH ROW
    EXECUTE PROCEDURE update_influencer_search_field();

CREATE TRIGGER update_search_field_influencers_on_update
    BEFORE UPDATE
    ON public.influencers
    FOR EACH ROW
    EXECUTE PROCEDURE update_influencer_search_field();

What's wrong?


texts: How to update sys.login_token without restart SQL Server Service

texts:
I query sys.login_token to check if the current user is in a particular windows group.

SELECT principal_id, name, [type], usage
FROM sys.login_token
WHERE principal_id > 0

But after I added the user to a new group, the group didn't show up in the query result.

I tried to log out and disconnect but it appears only restart the SQL Server Service will refresh the result of sys.login_token.

Therefore, is there any way(stored procedure or something) that I can trigger SQL Server to update sys.login_token?


texts: FULLTEXT CONTAINS search returns no results in SQL SERVER 2014 with multiple search terms

texts:
When I try to use multiple search terms in a CONTAINS clause I get no results.

SELECT *
FROM dbo.Address
WHERE CONTAINS([Address].*,'"Detroit"') AND CONTAINS([Address].*,'"48226"')
OPTION(QUERYTRACEON 9481)

SELECT *
FROM dbo.Address
WHERE CONTAINS([Address].*, ' "Detroit" AND "48226" ')
OPTION(QUERYTRACEON 9481)

The first query returns almost 20k rows.  The second, which should be logically the same returns nothing.  When I try the same test on my 2012 server, it returns the same number of rows, so I'm pretty sure it isn't syntax.

My version is Microsoft SQL Server 2014 (SP1-CU6) (KB3144524) - 12.0.4449.0 (X64)
    Apr 13 2016 12:41:07
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: )

There is no stopword list set up for this fulltext index.

The QUERYTRACEON 9481 forces the optimizer to use the cardinality estimator for SQL Server 2012 - this solves another issue we have had with FULLTEXT indexes and TOP in 2014, but makes no difference in this case; the queries return the same number of rows with or without it.


texts: Does ANSI SQL standard inlcude FORALL predicate?

texts:
I need to get all predicate of SQL (that was included in ANSI standard any version). Some databases (Oracle) have FORALL predicate. But I am not sure if that is part of the SQL specifications.

Does it include this predicate?

P.S. Please, do not send me to the standard. My English is not so good to I can get it otherwise I wouldn't ask it here.


texts: Can autoshrink permanently damage a database?

texts:
I've been given the task of finding out why our server is slow. I ran a stored procedure I downloaded called sp_Blitz. It let me know that our database had auto-shrink enabled, and to disable it. So, I did that. I don't know how long it's been enabled, but I'd guess at least 3-4 years. When I checked the fragmentation percent of the indexes, a lot of them were 60% - 99%. I then ran a script to rebuild the indexes and update the statistics, but the fragmentation percent didn't really change for these indexes.

Is there something else I need to do to repair what auto-shrink has done?

Any advice for me?


texts: Datepart logic for custom sql agent job schedule

texts:
Wanted help in code for below logic.

Job A needs to be run on 8th of month.
Job B needs to be run on Tuesday only.
Job c needs to be run on Daily(Minus Tuesday & Sunday).
And If Its Tuesday and 8Th Job A takes the precedence of Job.

i have coded logic for half of it.

Else If Begin logic,

--Tuesday Logic(0=Monday,1=Tuesday,2=Wed,3=Thu,4=Friday,5=Saturday,6=Sunday)
IF (SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,0) + 1)=1 -- If it is tuesday execute the Weekly+Daily Job
       Print 'executing weekly and daily tables'

ELSE
BEGIN
       Print 'executing daily job'
END
END


texts: convert 1 row as column and concatenate the results to the corresponding column


texts:
SQLite has a pragma for journal_mode, which I assume is available across other sql database programs (MySQL, PostgreSQL, MS-SQL, etc.) so this question would also apply to all those database programs which offer journaling mode like SQLite does.

Most modern file-systems (BtrFS, Ext4, NTFS, etc) use journaling.  As databases are typically stored upon a journaling file-system, is it not redundant for the database program to also be journaling?

When SQLite journal_mode is enabled, large numbers of updates and/or inserts take an extraordinarily long time to complete compared to journal_mode being disabled.


texts: What does NVL stand for? I'm talking about the Oracle and Informix (perhaps some others too) function used to filter out non NULL values from query results (similar to COALESCE in other databases).


texts: Expired archived logs not deleted from RMAN catalogue

texts:
If I issue a LIST EXPIRED command in RMAN, I see a list of files with expired status, that is, they exist in the catalogue, but not on the disk (verified by ls). After issuing CROSSCHECK and DELETE EXPIRED commands, I would expect LIST EXPIRED to return nothing... but they are all still here.

This may or may not be causing my backups to sporadically fail on ORA-19588. I have checked and am 100% certain that I am not running two RMANs at the same time. Any ideas? Target is 10g and catalogue is 11g.


texts: Get previous and next row from result set help

texts:
I have to get "relevant" results from a table by joining it on another table and filtering the results on parent id and current id, this all goes fine until your current id is either the first or the last in a result set.

Imagine I have a result set that looks like this:
1,3,6,7,9,23,76
My current id is 3, I need to get the rows with id 1 and 3 back to my application, I managed to do that just fine, now the problem is that the application needs to make a circle.

So using the same result set as above, imagine my current id is 1 I need to get back 3 ( as the next result ) and 76 as my "previous" result.

I also managed to get that to work up to a certain extent, except for when your current id is the maximum, then something weird happens:
it gets the next row from the given result set (in the example case if max = 76 then next is 1), but isn't able to retrieve it's previous one.

If I run the query separately, it works fine.

I am using ifnull, since if you don't have a row with an id that is smaller or bigger than the given id, it will return null and should execute the next statement.

Here is the SQL I'm currently using:

SELECT
CONCAT_WS('/', LOWER(t1.name), LOWER(t2.slug), LOWER(t3.slug)) as slug,
t1.id, t2.id, t3.id
FROM table4 t4
left join table1 t1 on t1.id = t4.t1id
left join table2 t2 on t2.id = t4.t2id
left join table3 t3 on t3.id = t4.t3id
WHERE
t2.id IN (IFNULL((SELECT MAX(id) FROM table2 WHERE parent = 1046 AND id <       1424), (SELECT MAX(id) FROM table2 WHERE parent = 1046)))
OR
t2.id IN (IFNULL((SELECT MIN(id) FROM table2 WHERE parent = 1046 AND id > 1424), (SELECT MIN(id) FROM table2 WHERE parent = 1046)))

This of course is pseudo code, but the only thing that changes relative to the actual DB is the table/column names.

EDIT: the IFNULLs do actually both return the right result when run separately, and the full query works if the current id is anything but the bigges

I hope you can point me in the right direction !


texts: Will the query be quicker if adding more WHERE conditions?

texts:
Assume I have a table t1 and two queries q1 and q2 on t1:
SELECT xxx FROM t1 WHERE <condition-1>

and
SELECT xxx FROM t1 WHERE <condition-2>,

where <condition-1> logically implies <condition-2> (i.e., <condition-1> is stricter than <condition-2>, and the result set of q1 is a subset of that of q2)
Question:
Will the response time of q1 commonly (if ignore extreme cases) be shorter than that of q2?
This question may seem weird, the reason why I ask is I want to figure out some test oracle for the performance test. If the answer to the above question is true, that may serve as a test oracle.


texts: CREATE TABLE AS SELECT creates non-nullable columns

texts:
I am using the CREATE TABLE AS SELECT statement in Oracle 11g to get data from SQL Server 2012 via a database link.

Oracle creates all these tables with non-nullable columns and that causes me problems later when I try to update them.

How can I prevent this behaviour in Oracle and make resulting columns nullable?


texts: Select Statement with Temp Column driven by Bitmask

texts:
I have a role system setup that checks to see if bits are set for specific roles.

Each user has a specific assigned that is stored in our database and gives them access to different parts of the program. What I'm trying to do is pull every user from the database and add a column beside the role number with what parts they have access to.

I can do this with a case statement but that will only find one of the permissions unless I break down all the combinations of cases.

Database Setup:

Name | Role |
Jon  | 77   |

Permissions:

@ONE       = 1
@TWO       = 2
@THREE     = 4
@FOUR      = 8
@FIVE      = 16
@SIX       = 32
@SEVEN     = 64
@EIGHT     = 128
@NINE      = 256
@TEN       = 1024
@ELEVEN    = 2048
@TWELVE    = 32768
@THIRTEEN  = 65471

So for Jon I want to see:

Name | Role | Roles
Jon  | 77   | One Three Four Seven


texts: I want to update mysql vertion 5.5.59 to 5.7.21

texts:
I'm trying to update my mysql version but it showing error I'm using Linux mint 17.3 version

used command

sudo apt-get install mysql-server mysql-client mysql-common mysql-server-core-5.7 mysql-client-core-5.7

it showing this

  Reading package lists... Done
Building dependency tree
Reading state information... Done
Package mysql-client-core-5.7 is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source
However the following packages replace it:
  mysql-cluster-community-client:i386 libmysqlclient-dev:i386
  mysql-community-client:i386 mysql-cluster-community-client
  libmysqlclient-dev mysql-community-client

Package mysql-server-core-5.7 is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source
However the following packages replace it:
  mysql-common:i386 mysql-cluster-community-server:i386
  mysql-community-server:i386 mysql-common mysql-cluster-community-server
  mysql-community-server

E: Package 'mysql-server-core-5.7' has no installation candidate
E: Package 'mysql-client-core-5.7' has no installation candidate

  mysql -v

mysql  Ver 14.14 Distrib 5.5.59, for debian-linux-gnu (x86_64) using readline 6.3

how can i fix this??


texts: How to safely replace the old table with its cleaned copy?

texts:
I have created a table_cleaned like table and inserted unique values from table into table_cleaned. Now I want to replace table with table_cleaned with
DROP TABLE table;
ALTER TABLE table_cleaned rename to table;

but the first statement gives cannot drop table cals_status because other objects depend on it.
I definitely don't want to use cascade as it would delete all the dependent rows.
How can I safely replace the old table with its cleaned copy?
Following Erwin Brandstetter's advice, this is the missing detail of the error message:

DETAIL:  default value for column id of table cals_status_deduplicated depends on sequence cals_status_id_seq


texts:
In database I have many tables like with names:

"t_2015_09_01_z15"
"t_2015_09_02_z15"
"t_2015_09_03_z15"
"t_2015_09_04_z15"
"t_2015_09_05_z15"
...
"t_2015_10_01_z15"
"t_2015_10_02_z15"
"t_2015_10_03_z15"
"t_2015_10_04_z15"
"t_2015_10_05_z15"
...

Please help create function which union tables by mask, for example

SELECT maskunion('public', 'mask', 'tablename');

where public - scheme name, mask - mask name ('t_2015_09_' - union all tables for 09 mounth, 't_2015_' - union all tables for 2015 year), tablename - output table name.

I found a similar function https://stackoverflow.com/questions/4202135/how-to-drop-multiple-tables-in-postgresql-using-a-wildcard footgun, but this function is for deleting tables by mask. Now I manually union each table by query:

CREATE TABLE t_2015_10 AS
SELECT * FROM t_2015_10_10_z15
UNION ALL
SELECT * FROM t_2015_10_11_z15
UNION ALL
SELECT * FROM t_2015_10_12_z15
UNION ALL
SELECT * FROM t_2015_10_13_z15
UNION ALL
SELECT * FROM t_2015_10_14_z15
UNION ALL
SELECT * FROM t_2015_10_15_z15;


texts: Faster alternative to count value from 2 queries and select with WITH

texts:
I have a query which I have to make it run faster.
This is my best shot.
Any recommendations that I can improve this?

WITH cm
     AS (SELECT Count(*) AS count_messages
         FROM   messages AS m
         WHERE  EXISTS (SELECT 1
                        FROM   order_items AS o
                               inner join orders ox
                                       ON ox.order_id = o.order_id
                        WHERE  m.product_id = o.product_id
                               AND m.message_type = 0
                               AND m.seller_id IN( 3, 2, 6, 1, 9 )
                               AND current_date >= ox.order_creation_date)),
     co
     AS (SELECT Count(*) AS count_order_items
         FROM   order_items AS o
         WHERE  EXISTS (SELECT 1
                        FROM   messages AS m
                               inner join orders oy
                                       ON oy.order_id = o.order_id
                        WHERE  m.product_id = o.product_id
                               AND m.message_type = 0
                               AND m.seller_id IN( 3, 2, 6, 1, 9 )
                               AND current_date >= oy.order_creation_date))
SELECT cm.count_messages :: DECIMAL / co.count_order_items :: DECIMAL * 100 AS
       result
FROM   cm,
       co


texts: MySql 5.7 master-master (restore a master by replication vs dump)

texts:
We have MySql 5.7 running in Centos 7 in cloud environment.
We have the master-master working properly. One master gets the writes which replicates to the other master. The second master is a hot standby in case the 1st master dies.

Currently we need to rebuild the masters on a somewhat regular basis. We are doing this by restoring from mysql backups and then adding the new master into the replication.

We would like to avoid using the mysql backups for replacing masters.
We would like to turn off a Master, Build a new Master, configure the masters and then allow the Master (that we did not replace) to populate the Master (that we replaced).

Any suggestions?


texts: How can I verify all stored procedures in a SQL Server database? I need to verify if all stored procedures still work, after some tables/views etc. have been deleted or changed.


texts: Convert or open Oracle 7.3.x database in Oracle 11

texts:
I have a very old Oracle 7.x database that I need to open or convert to a new Oracle 11g.
Here's the content of the old database:

control01.ctl
control02.ctl
control03.ctl
findata01.ora
finind01.ora
gstdata01.ora
rbs01.dbf
system01.dbf
system02.dbf
system03.dbf
temp01.dbf
temp02.dbf
tools01.dbf
users.dbf

Note: the .ora files are binary (spfile).
When mounting the old database the following error appears:
ORA-00205: error in identifying control file

Ok, first I've tried to create a pfile from spfile:

create pfile='c:\ora\pfile.ora' from spfile='c:\ora...\findata01.ora'

I get "File created" but the pfile is empty.

I've tried to change the control files to:

alter system set control_files='c:\ora...\control01.ctl,c:\ora...\control02.ctl,c:\ora...\control03.ctl' scope=spfile;

But then I get the error message:

ORA-32001: write to SPFILE requested but no SPFILE is in use

Anyone know what else to try? I'm running out of ideas.

Maybe the database format has changed and it's not possible to open a 7.x database from inside 11.x? Or maybe there's a third-party tool to convert the database format?

Thanks in advance!


texts:
One of INSERT INTO script is written as following.

INSERT INTO tableName (Column1, Column2,....) VALUES (value1, Value2,...), (value1, Value2,...),....

Following is the error we are facing on parsing above insert statement

  Msg 10738, Level 15, State 1, Line 1007
  The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

My simple question is that, Can we change 1000 values limit ?


texts: How to query cached plans that contain a specific missing index?

texts: I see a potential missing index for a table but would like to first query all cached plans that have that specific missing index (so I can later test those queries after the new index is built). How do you do build such a query?  Can sp_blitzcache help here?


texts: With LocalAdmin become sysadmin SQL Server

texts:
I am a local Admin on my machine.  My account wasn't added when SQL Server was installed, and I don't want to have to reinstall SQL Server.  It worked until I tried to log and run the commands via SQLCMD or SSMS.  I got the error message

  Login failed for user <Machine_Name>/<Windows Name>. Reason: Server is in single user mode. Only one administrator can connect at this time...

I found this blog and added the application name after the m switch.  But no dice, I still get the above error.  The User Account looks fine I guess, I am an Administrator.

Also,
  I had to start the cmd prompt as an Admin to start sqlserver in single user mode, or else I couldn't have access to the log files that the instance was using and it was giving me an error to that.  So I ran the cmd prompt as admin. But still, I am the same admin trying to run SQLCMD or SSMS.  It shouldn't be telling me there is already an admin logged in, because I am that admin.  What am I missing?

This is on SQLExpress.


texts:
I am running DBCC CHECKDB on one of my database but its taking very very long time. I have some questions please help me.

Is there any alternative of it that can check my database integrity?

Should I wait to finish it?


texts: Ideal environment and cost for running a medium size MongoDb server

texts:
I know some people will say this question does not belong here but i didnt find a better place on the internet to ask this. Please migrate to an appropriate place if you think it should be.
I am using MongoDB to store product data. The text data(product names,categories,attributes) account for about 30 GB. The images account for about 200 GB. I am paying 184 USD for a quad core processor and 8GB Ram and 500 GB HDD dedicated server(from SingleHop). Is this setup ok for a medium traffic site(5-7K visitors a day ). I have heard that MongoDB best works when it is sharded(because of the global read write lock) but i have never worked with clusters. I have always worked with VPSs. This is my first time with a Dedicated server. Please advise me about the setup and the costs too. I live in India and expect most of my visitors from India.

Thanks.


texts:
I've installed sp_BlitzCache (and others) from the First Responder Kit, and I am receiving the following error:

EXEC sp_BlitzCache;

  Msg 208, Level 16, State 0, Procedure sp_BlitzCache, Line 3878 [Batch Start Line 0]

  Invalid object name '#trace_flags'.

All of the other scripts appear to be working as expected, but I am not sure if I've encountered a defect, or if it has something to do with my use and/or configuration.

Also worth noting, the procedure does return 10 results, but then fails with the above error.

I've done some searching online and through the stored procedure itself. While I am confused that the #trace_flags temp table does not appear to have been created like the others, I cannot find code on GitHub that seems to indicate that it ever had.. So since I imagine that it has been working for others it leads me to believe that I am assuming the wrong things and/or running it incorrectly.

Server: Microsoft SQL Server Standard Edition
Version: 10.50.6220.0 (SQL Server 2008 R2)

Thank you in advance! Both for the assistance, and for any of you who have been involved in developing the First Responder Kit.


texts: Please I have issue that when I try to import data from excel to table in SQL it fails with below errors even I modified all the tables in the tables to be nvarchar(max) and I tried to enter dummy data in the first row


texts: Room bookings database - does it make sense to create two tables instead of one column?

texts:
There are two types of entities: user and room. A user can

book room
select room
unbook room
unselect room

"Selected room" is a state that indicates when a user has decided to book a room and so he/she selects the room of relevance, but still has not pushed a button 'Book'.

Firstly I have tried to create one table for room with the following columns:

roomId
roomNumber
roomStateId (there is an extra table LookUp_RoomState; possible states are: "free", "booked", "selected")
selectedBy
bookedBy

I didn't like that I have to create three columns that represent the state, especially the fact the selectedBy and bookedBy are related with roomStateId. I thought and created two tables as shown in the screen capture below:

I didn't like that now I have too many tables and when I want to check the state of the room I have to rely on the state of the system. Moreover, it can potentially produce collisions because both tables can be updated independently. In the template that follows, I returned to the one-table arrangement:

roomId
roomNumber
roomStateId  ("free"/"selected"/"booked")
userId

The one thing I don't like in the last approach that meaning of the userId is defined by another column - roomStateId, when roomStateId is booked than userId has value bookedBy, when roomStateId is selected then userId has value selectedBy. I have also thought about some another variants - move roomStateId and userId to a separate table, rename userId to stateChangedByUserId.

I now think that it is impossible to create an ideal schema and all depends on the use cases; however, maybe someone have some ideas on it? I really don't like when one column can have many values dependent on some conditions as userId on room table, but currently I have stopped on the third variant, because it seems handy to me at this moment.


texts: Performance settings for MyISAM tables - keep everything in memory

texts:
I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data).
Since the databases size is several times smaller than the available memory, how can I make full usage of my server's resources?

What settings should I attempt configure in my.cnf?
Can I force data + indexes to stay in memory?


texts: Credentials for mysql fail for mysqldump in bash script

texts:
I am having a very strange problem in a script to back up MySQL schemas.  The script creates a list of schemas and then runs the mysqldump command for each of them. The credentials are stored in a separate file db_credentials.

The relevant lines of code are...

. /home/backupuser/db_credentials
user=$USERNAME
password=$PASSWORD
dbList=$(mysql -u$user -p$password -Bse "SHOW DATABASES")
for db in $dbList
do
   mysqldump –u$user -p$password --single-transaction --routines  $db > $dumpdir/${db}_${timestamp}.sql
   echo $db
done

The output looks like this...

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
db_1
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
db_2
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
db_3
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
...

This shows that the $dblist array variable is filled correctly, but for some reason, the myslqdump command is not using the credentials supplied.

I am running the script from the command line using sudo, but I have no idea where it is getting user=root or the password from.

I just checked the backup directory, and the relevant files are being created, but they are empty.

MySQL version 5.1
O/S Centos 6.8
Bash version 4.1.2

Any help would be much appreciated


texts:
Is there a way to serialize a record/row in SQL? And conversely, to deserialize something that has been serialized.

By serialize, I mean put all of the fields in a record into one field or string.

Preferably with MySQL compatibility.

I am guessing that xml, a udf or stored procedure might be necessary.

is there a way to use xml or json directly in sql/mysql?

Thanks!


texts: ALTER TABLE with missing FROM-clause entry for table "inventory"

texts:
ALTER TABLE inventory.subtitles
   ALTER COLUMN id SET DEFAULT (inventory.subtitle_subtitle_id_seq);

produces missing FROM-clause entry for table "inventory"

but even pgadmin 3 gives me the same syntax.


texts: Group by multiple columns with different members of the same hierarchy?

texts:
I have customer data in a table structure like this:

customerID | productLine1 | productLine2 | productLine3
1          | 1            | null         | null
2          | 2            | 1            | null
3          | 4            | 2            | 1

Each customer is assigned to at least 1 product line but a maximum of three product lines.

I would now like to see how many customers I have for each product line.
Any ideas on how to solve this?

Important: I do not wish to turn columns into rows (which would be a solution).
So the result would be:

productLine "1" --> 3 customers
productLine "2" --> 2 customers
productLine "4" --> 1 customer


texts: select newest update record among similar record

texts:
I have  2 tables segments and summaries.
I want to list the shows in a date interval and just show the latest updated segments (in bold) since it is the freshest copy of the segment for that person.
Here is the query I used to have the result below:

select seg.summ, su.channel, seg.block_id, seg.person, seg.updated_at as segupdate
from segments seg
  left join summaries su on seg.summ = su.id
where su.created_at between '2018-06-06' and '2018-07-13'
order by summ, block_id, seg.updated_at desc;

I want this:

How can I get change my query to show just the bolded rows? to remove the duplicates that are an older version?


texts: PostgreSQL: result of range difference would not be contiguous

texts:
How to substract a smaller subrange from a bigger range?

123456789
    567

Result:

1234   89

SQL:

select '[1,9]'::int4range - '[5,7]'::int4range;
ERROR:  result of range difference would not be contiguous

The result does not fit into a int4range since it is not contiguous.

How to get the result as a set of int4ranges?


texts:
I have 2 Windows 2012 virtual machines set up in Virtualbox - one called "AppServer" where I'll be installing applications, and another called "DatabaseServer" - where the application databases will be stored, using SQL Server 2008.

I'm running some install files on the application server to create a corresponding database on the database server, but it is just hanging, most likely due to permissions.

The VMs are connected using the Internal Network function of Virtualbox, and they can successfully ping each other.

How do i set up permissions on the database server to allow accounts from the application server to create databases, etc. on it?


texts: Multiple availability groups--can each availability group have its own listener?

texts: I have created two availability groups containing multiple databases on the same SQL Server instance.  Can I have two different listeners for these two availability groups?


texts:
I just created a full text catalog :

Enable full text (right click on db => file => enable ful text indexing)
Create catalog (Storage => Fulle text Catalog => New Catalog => "FTCatalog" => OK)
Add column to catalog (right click on catalog => properties => table/views add table/columns , automatic => ok)

The table has 270k lines, I created the catalog this morning and still it has 0 Item Count and the populate status is 1.
The logs in [...]\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLFT0000800007.LOG doesn't show anything more than (i tried to recreate it 10 min ago)

2014-06-30 16:32:03.93 spid23s     Informational: Full-text Full
population initialized for table or indexed view '[XXX].[dbo].[XXX]'
(table or indexed view ID '1771153355', database ID '8'). Population
sub-tasks: 4.

All SPs are installed.
I tried with some smaller table, it's the same.
The service "SQL Server FullText Search" is running.
Nothing on the EventViewer.
Any idea ?
UPDATE
In Sql Server Log I have these two thing :

Changing the status to PAUSE for full-text catalog "XXX" (7) in database "XXX" (8). This is an informational message only. No user action is required.
Changing the status to PAUSE for full-text catalog "XXX" (7) in database "XXX" (8). This is an informational message only. No user action is required.


texts: postgresql logical replication -- subscriber size discrepancy

texts:
I have a postgres 11 database in Amazon RDS with a little over 2 TB data in it whose tables have incrementing integer ids that are nearing exhaustion.  I've been vetting using logical replication to have the existing db synch data to a new db with a schema modified to have bigint ids.  Everything looks ok with a lot of due diligence and I am about ready to make the cutover.

One thing I have noticed is that the subscriber database being a smaller overall size, even though many of its columns and indexes have been switched to using bigint ids vs ints.  Does anyone have any idea why this might be the case?  This is the one question I have not been able to discover a confirmation for vs my guesses (fragmentation, etc...).

Using the queries described here, I have the following usage:

total: 2.171 TB in publisher, 2.020 TB in subscriber
indexes: 0.737 TB in publisher, 0.581 TB in subscriber
toast: 0.212 TB in publisher, 0.215 TB in subscriber
tables: 1.223 TB in publisher,1.223 TB in subscriber


texts:
I have 2 separate SQL Server instances, for the purpose of the question, we will call them Source and Target.

I have an ever changing database on Source called MyDatabase, this database is populated once a day from a piece of software. When this is dumped, I need to create a copy of the database on Target. It must be an exact replica.

My first method was using a SSIS package, to export and import the data, however will this method work if the database schema is changing?

For example, my package copies:

Target.Table1 (ColA, ColB, ColC)
Target.Table2 (ColA, ColB, ColC)

After my package has been set up, the schema becomes:

Target.Table1 (ColA, ColB, ColC)
Target.Table2 (ColA, ColB, ColD) (ColC has been dropped)
Target.Table3 (ColA, ColB)

How can I insure the changes in Table2 are picked up, and Table3 is exported and imported?

Sorry I can't give much more of an example, however hopefully this is enough detail for the question.


texts: Grouping categorical values in a sequence

texts:
I have a table with categorical values in a sequence, as such:

CREATE TABLE cat (name, v1, v2)
AS VALUES
    ('John', 1::int, 3::int),
    ('John', 3, 4),
    ('John', 4, 9),
    ('Mike', 9, 11),
    ('Mike', 11, 17),
    ('John', 17, 24),
    ('John', 24, 25),
    ('Dave', 25, 29);

And I want to group them by the name column, getting the minimum value_1 and maximum value_2, however group them as the names appear in unbroken sequence, as such:

+-----------+---------+---------+
| name      | value_1 | value_2 |
+-----------+---------+---------+
| John      | 1       | 9       |
| Mike      | 9       | 17      |
| John      | 17      | 25      |
| Dave      | 25      | 29      |
+-----------+---------+---------+

However, doing normal GROUP BY selection returns all the entries for a particular name together, regardless of whether they appear together or not:

SELECT name, MIN(value_1), MAX(value_2)
FROM table
GROUP BY name;

+-----------+---------+---------+
| name      | value_1 | value_2 |
+-----------+---------+---------+
| John      | 1       | 25      |
| Mike      | 9       | 17      |
| Dave      | 25      | 29      |
+-----------+---------+---------+

Now, I could probably do this with plpgsql, using traditional programming logic with variables, loops and such, but I was wondering if there is a more SQL-flavoured approach to this.

Software: PostgreSQL 9.6.3


texts:
I have a 2 node cluster on which I need to setup AlwaysOn since it is a standard edition we would be going with Basic AGs. I can easily get the user databases added into the AGs but I am not sure about how it would be if we add the ReportingServer databases into the Basic AG. Is there a documentation that suggests how it needs to be added in a Basic AG. Also, what are the precautionary measures once should take in order to make sure that in the event of a failover it works correctly. I am also a little unsure how it would be since both the ReportServer and ReportServerTempDB would be in 2 separate AGs...will this cause problems etc.

Appreciate the guidance on this.

Thanks


texts: Can geography datatype Lat/Long properties be returned using a view in SQL Server 2008?

texts:
Hopefully the question is fairly self-explanatory.

I've been ferreting through the, what appears to be quite minimal/poor microsoft documentation regarding both views and geography datatypes in the sql server 2008, and I think the answer is "no, geography datatypes are either not supported or minimally supported within a view"; but I'm not entirely sure, and I'd love some corroboration.

I've been attempting to add geography data to an existing view. And my end goal is to retrieve longitude/latitude data from the field in question.

Here's the select statement (simplified) that works when applied to the table but doesn't work when defined within the view:

SELECT
    [dbo.vendor.location].Lat AS latitude,
FROM
    dbo.vendor

Note that [dbo.vendor.location] is the field, and Lat is the latitude property of the geography datatype.

And when that sql is run within the view, the following error is returned:

The multi-part identifier "dbo.vendor.location.Lat" could not be bound

Edit:

Note that I've also tried the following query, with similar results:

SELECT
    [dbo].[vendor].[location].Lat AS latitude,
FROM
    dbo.vendor

In this case, SQL Management Studio removes the quotes and returns the same "multi-part ... could not be bound" error.


texts:
I'm getting error 833 on my ldf file and i'm getting it a lot.

Please see the following. When this happens, my application is hang already.

I know error 833 has been reported a lot, but what does it mean if it is consistently pointing to my LDF file which it could not access?
Is it because the LDF file is already large?

I'm using SQL Server 2008 R2 on a windows 2008 environment.
The database is in a state in which it is unresponsive, a restart was the only that removed the hang I/O. Once it started, it didn't end already.

What's a possible explanation for this? Please see the log files in the sql error logs.

MIght it also be because of this bug?

http://support.microsoft.com/kb/2708012

Btw, the sql server was unresponsive the whole time, and this happened during the night time. Only the restart made the server responsive again.

01/21/2013 12:22:09,spid3s,Unknown,SQL Server has encountered 19 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\MSSQL.1\MSSQL\DATA\test_database_log.LDF] in database [test_database] (6).  The OS file handle is 0x00000000000008B8.  The offset of the latest long I/O is: 0x00001028a8ac00
01/21/2013 12:15:29,spid3s,Unknown,SQL Server has encountered 17 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\MSSQL.1\MSSQL\DATA\test_database_log.LDF] in database [test_database] (6).  The OS file handle is 0x00000000000008B8.  The offset of the latest long I/O is: 0x00001028c6ac00


texts: Error using sp_trace_create with parameters

texts:
I'm running the following SQL on SQL Server 2000:

exec sp_trace_create @options = 2,
         @tracefile = N'h:\\trace.trc',
         @filecount = 2

and am getting the following error and can't understand why:

  Procedure expects parameter '@tracefile' of type 'nvarchar(128)'

If I pass all of the parameters in like so:

declare @id int

exec sp_trace_create @id output,
         @options = 2,
         @tracefile = N'h:\\trace.trc',
         @filecount = 2,
         @maxfilesize = 5,
         @stoptime = '2013-8-15'

I get the error:

  Procedure or function sp_trace_create has too many arguments specified.


texts: InnoDB: Is a covering index in the form of (PK, col1, col2, col3) redundant?

texts:
Table structure for config:

property (primary key)
value
description

Query:

SELECT property, value FROM config

If I put a covering index on (property, value) the optimizer still chooses the PRIMARY index, but the extra in EXPLAIN is NULL. If I tell the optimizer to use my covering index, the extra in EXPLAIN is USING INDEX.

What's really happening here? Why is the optimizer choosing the PRIMARY index over my covering index by default? Am I avoiding disk IO by telling the optimizer to use my covering index?


texts:
I am deploying Oracle Golden Gate to sync data from Sql Server 2008 to Sql Server 2008. One of the extract process have stopped.This is the error:

2014-12-21 17:22:09  ERROR   OGG-00146  Call to VAMRead returned with error stat
us 600: VAM Client Report <[mssqlvam::CGGSApi::PartialUpdateOperation] Updates a
re not supported on tables that do not have TRANDATA added. TranId: 0000:001b210
9, Table: "dbo"."Sys_MaxKey">.

I try to use command:

add trandata Sys_MaxKey

but the problem still exists,and i can't start ext002 (extract process name).


texts: SQL Server 2014 network connectivity issues

texts:
After one of our servers had been up fine for a couple of months, we started receiving a vague network error for small time windows in the late hours of the night, when some jobs were running. It would only appear to happen for 30 minutes or so at a time, then the same jobs would work fine.

Today, the issue occurred during business hours, but I was unable to find anything that stood out as an issue (server was not under a great deal of load, and there were no open transactions).

The error message was:

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections.

I found that I was able to resolve the issue by disabling and re-enabling the internal NIC. Given we hadn't had a restart for a few months and the windows updates were piling up, I updated and restarted.

From the server itself, management studio was unable to connect to the server, receiving the same "Cannot Connect to Server - A network-related or instance-specific error” error. I tried uninstalling windows updates and rebooting, but this did not resolve the issue.

I also tried restarting the NIC, but this didn't work either.

I was able to resolve the issue by going into the SQL Server configuration manager, and in the TCP/IP settings I set "Listen all" to "Yes". After doing this, I was able to connect again. I've since run a netstat -a and can see the default port 1433 is listening.

Any ideas what is going on here, am I somehow running on the wrong port? Let me know if there's any more information i can provide to help?


texts: How to store a sequence of events related to a transaction

texts:
I am using Sql Server 2008 R2.

I have two tables that describe import processes and export processes.
Import table has these columns with others that just have details about each type of import

ImportID, ImportFileFormat, ImportFileLocation,...

The Export tbl is similar to the import table except it describes outputs and other export specific data.

ExportID, ExportFileFormat, ExportFileLocation,...

Now I need some way of storing a 'Transaction' and I am stuck on how to do this exactly.

A 'transaction' is any number of imports and exports in any unique sequence.  Eg: T = I1, E1, E2 or I2, E1, E3, I1 or I1 or E3 or any unique combination of importing / exporting with no limit to number of Imports/ exports.

A 'transaction' may eventually include other 'eventtypes' other than just import/export like 'run_sp' or some sort of action in the database or application.

How might I go about doing something like this? I have looked at what I think may be a similar question on stackoverflow. This seems like something that may do what I want but I am unsure.

Any help would be greatly appreciated.

Edit #1:

tblTransactions:

TransactionID   TransactionNumber   EventTable   EventID
1               1                   'tblImport'  1
2               1                   'tblExport'  3
3               2                   'tblImport'  1
4               3                   'tblExport'  3
5               2                   'tblExport'  2


texts: How to get all connected values as a single column

texts:
I'm trying to use CTE recursion.
This is my scenario SQL Fiddle.
It is a simple tree structure:

CREATE TABLE [dbo].[AL](
    [IdAL] [int] IDENTITY(1,1) NOT NULL,
    [ID1] [int] NOT NULL,
    [ID2] [int] NOT NULL,
 CONSTRAINT [PK_AL] PRIMARY KEY CLUSTERED
    (
      [IdAL] ASC
    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
            ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] ;

INSERT INTO AL (ID1, ID2)
VALUES (1092, 284917),
       (1092, 248957),
       (1092, 789475),
       (   1, 789475),
       (   1,      2),
       (   3,      2),
       (   6,      4) ;

Simply put, the user gives me an id (e.g. 1092). I would recursively run all rows in the table and do the merge of the id (ID1 + ID2) starting with the id provided by the user.

I would like to get something like this (in just one column called ID):

ID
1092,
284917,
248957,
789475,
1,
2,
3

Note that id 4 and 6 are not in the result list because there is no node that points to these ids.