texts: Login to SQL Server using Windows Authentication

texts:
I have an (.Net Console App) EXE which connects to various SQL databases using SQL server Authentication with appropriate credentials.
Now there is change required in the EXE to use windows authentication and use the Network Credentials to log in SQL server. (All SQL servers are on diffrent locations)
I have the network credentials with me, but am confused how to connect to remote SQL server using these and retrive data from that.
Please help.


texts: Multiple update of records in the same table

texts:
I have an update query e.g.:

UPDATE table SET name = ? WHERE id = ?

I have all the relevant ids collected. I am looping over the ids and executing the update query for each one. In pseudo code:

for (id..ids)  {
   stmt->executeUpdate(query, nameForId(id), id);
}

I was wondering is there a smarter/more efficient way to do this query update without looping? For instance, is there a way to create a big string once, like:

UPDATE table set a=? where id=? AND a=? where id=? ...etc.


texts: Database Tuning Advisor Index Recommendations

texts:
How does DTA make an index recommendation? What is the technology behind that? If we can use same on our side then we can shorten the whole process of Tuning recommendation:

creating trace file using SQL Profiler
Use that trace file in DTA
Then look for recommendations
Follow the recommendations & see the results in the Query Execution Plan.

I just wanted to know if it is there any way to create a predictable index.


texts: SSRS: UserName value in Subscriptions table, ExtensionSettings column

texts:
SSRS subscription to a Windows File Share.  Is there a way to query for all the "credentials used to access the file share" in plain text?  It appears that the UserName value in the ExtensionSettings column is hashed (and different for the same user for many reports).

I am trying to identify all the reports that a single account is being used to access the file shares.


texts: Only return recordset(s) from Stored Procedure if it has rows

texts:
I have a stored procedure that returns multiple recordsets for use in an application. Sometimes some of those recordsets are empty.

I'd like to reduce overhead and only return those which have 1 or more rows.

My question is - How can I only return those recordsets which have rows?

The application simply expects 0 or more recordsets, and loops through each, and prints them out.

I know I can skip them in the application code, but I am trying to prevent them from being returned at all, if empty.

The procedure is as simple as this:

CREATE PROCEDURE bfsp_PROC_NM
AS

    BEGIN

        SELECT * FROM TABLE_1

        SELECT * FROM TABLE_2

        SELECT * FROM TABLE_3

        RETURN

    END
GO

In the actual procedure, some of the queries are expensive, so I don't want to have to test the query, then if it returns a row or more, execute it again... as it would be too expensive.


texts: creating user related user specific content

texts: Should you create a table for each user to contain information on specific content for an account, say favorite articles, interests, ect. User defined lists that would be used to generate useful content using a tag system to define groups of content. Would i be approaching this close to right, or is there a better way. i know u should not define more than one thing in a column, but i also would want the fewest number tables too?


texts: Looking for ideas on maintaining data warehouse in close to real-time - ETL tools, etc

texts:
I have done a fair number of traditional data warehouse implementations where the loads were done in a batch-oriented manner, i.e. the data is refreshed nightly or, at most, every few hours.  I now face a challenge of creating a system, where the data in the data warehouse needs to be maintained close to real-time (a few minutes delay is OK, but no more than that).  I have done a lot of reading and it seems that getting close to real-time has been a trend in DW over the last few years.  However, I am having trouble finding specific examples and concrete information about the available tools, which support this kind of "trickle-feed" ETL.  It seems that the right tool would be able to read the database transaction log and send those changes over to the data warehouse, while allowing to do some data transformations in-flight.  Does anybody has experience with real-time data warehousing and can recommend a good tool or point out a good reading on this subject.

Here are a couple of relevant links:
http://proc.conisar.org/2012/pdf/2214.pdf
http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=37&cad=rja&uact=8&ved=0CGQQFjAGOB4&url=http%3A%2F%2Fwww.researchgate.net%2Fpublication%2F226219087_Near_Real_Time_ETL%2Ffile%2F79e4150b23b3aca5aa.pdf&ei=G_dOU8HXI4iy2wW954GoDw&usg=AFQjCNHZVJuxfmuqwjrdi10oxM8v51WcNA&sig2=6OI-xoIz9b0mH_hzITBVrQ

Thank you!

P.S. I work in a Microsoft shop, so the source databases are on Microsoft SQL Server.  I do have a good handle on SSIS, but it doesn't seem to fit here.


texts: Execute query using daily timestamp (mysql event)

texts:
I was trying to find a way for give users a daily "point" per online day.

So my plan was setup a MySQL event to run this query:

UPDATE users SET points=points+1 WHERE timestamp > "X"

(Where X is the timestamp running since the 12.01AM of the current day).
The problem is that I didn't find any way to change daily timestamp (or the X in query) automatically. There is anyway to do it?
Thank you.


texts: Best Practices to reconfigure SQL Server for VMware

texts:
What I want to know is why would someone configure a server this way.

3 instances of SQL Server on the machine, 64GB RAM, no affinity mask settings and MAXDOP 0 with CostThreshold 5.

I want to give better recommendations for the SQL Server and I'm still analyzing the cause of the performance issues and have collections enabled, as well as using DPA (formerly Ignite) I'll have more info tomorrow.

In my previous infrastructure architecture work, I would have never gone with a 2x10 config on VMware ever, but wondering if things have changed in best practices since vSphere5 days.


texts: Calculating new date with date/time operators fails with variables

texts:
I have two fields, one timestamp (calldate), and one bigint (duration - no idea why it's this big).

Through a query I would like to construct a 3rd field that is the timestamp plus the bigint as seconds.

With 9.3 I don't have access to make_interval, but the functions and operators seem useless.

select calldate + interval + duration || ' seconds' as "completion_date";
select calldate + interval + '900 seconds' as "completion_date";

The above is abbreviated a bit, but the 2nd line works while the 1st line fails. I'm unable to pass or construct the interval in a way it will accept. I was hoping to use the make_interval function that would return an interval type, but alas I'm using 9.3. I've tried many, many, different variations using () to try and isolate it as well as casting values.

Nothing works. How can I add the duration in seconds to a timestamp value?


texts: SQL Server Clustering - Active/Active or Active/Passive?

texts:
What is the logic of the Active/Active cluster vs Active/Passive cluster?

I have searched a lot and read lots of documents but have not found the answer.

In active active cluster can I have 1 database which is in shared storage and that database have 2 instances?

What is the pros/cons of active active cluster?

In active passive cluster can I have one database which is in shared storage and it use active node. If active node is shutdown, will the passive node become the active node?


texts: SQL Server-Is It possible 'lock waits and timeouts high but average wait time does not'?

texts:
I use Windows Server 2008 Standard and SQL Server 2008 version.
I always thought that if the 'Lock Waits' number was high, the 'Average Wait Time' number would be high as well.
In addition, it is a situation that even lock timeouts occur.
However, this time Lock Waits was high, but Average Wait Time was not.
Is this possible? In what cases is this possible?


texts: Code review database design for storing college assessments & enrolments

texts:
I'm desigining a database for a university testing tool – running tests in the cloud, very similar to a CI system.

It needs to store users, courses, enrolments, assessments, submissions & results.

It's not a system powering the entire institution, but it is important for a few different courses. I'd like the database to be well-structured, extensible, and "intuitive" for future maintainers.

Requirements:

Two distinct courses may have the same name (but be run in different years/periods)
A single course can only be run once per period (of which there are multiple per year)
Assessment names must be unique within a course, but not across other courses.
A user may be enrolled in a course as an instructor, tutor, or student, but not multiple at once. A student may become a tutor for the same course in the future.

I've included my schema below, with relevant comments where I feel they're needed.

CREATE TYPE PERIOD          AS ENUM ('summer', 'first', 'autumn', 'winter', 'second', 'spring');
CREATE TYPE ASSESSMENT_TYPE AS ENUM ('assignment', 'lab');
CREATE TYPE TEST_RESULT     AS ENUM ('warnings', 'errors', 'perfect');
CREATE TYPE ROLE            AS ENUM ('student', 'tutor', 'convenor', 'admin');

CREATE TABLE users (
    uid      VARCHAR(20)  PRIMARY KEY,
    email    VARCHAR(255) UNIQUE NOT NULL,
    password BYTEA               NOT NULL
);

CREATE TABLE courses (
    -- id is used instead of (code, period, year) to
    -- uniquely identify a course, since its easier.
    id     SERIAL       PRIMARY KEY,
    -- code is used within the university to identify a course,
    -- but not when it was run (a single course can be run
    -- multiple times, with different students/assessments/etc)
    code   VARCHAR(20)  NOT NULL,
    name   VARCHAR(255) NOT NULL,
    period PERIOD       NOT NULL,
    year   INTEGER      NOT NULL,
    -- ensure that a single course can only be run once per period.
    CONSTRAINT once_per_period UNIQUE (code, period, year)
);

CREATE TABLE assessment (
    id           SERIAL          PRIMARY KEY,
    name         VARCHAR(255)    NOT NULL,
    type         ASSESSMENT_TYPE NOT NULL,
    comments     TEXT,
    course_id    INTEGER         REFERENCES courses(id),
    -- assessment names must be unique within a course,
    -- but not across courses.
    CONSTRAINT unique_name_per_course UNIQUE (name, course_id)
);

CREATE TABLE test_results (
    id            SERIAL      PRIMARY KEY,
    result        TEST_RESULT NOT NULL,
    warnings      TEXT        NOT NULL,
    errors        TEXT        NOT NULL
);

CREATE TABLE submissions (
    id            SERIAL      PRIMARY KEY,
    title         TEXT        NOT NULL,
    description   TEXT        NOT NULL,
    feedback      TEXT        NOT NULL,
    uid           VARCHAR(20) REFERENCES users(uid),
    assessment_id INTEGER     REFERENCES assessment(id),
    result_id     INTEGER     REFERENCES test_results(id)
);

CREATE TABLE enrol (
    user_uid  VARCHAR(20) REFERENCES users(uid),
    course_id INTEGER     REFERENCES courses(id),
    role      ROLE        NOT NULL,
    -- ensure a user cannot have multiple roles for a single course
    CONSTRAINT one_role_per_course_run UNIQUE (user_uid, course_id)
);

I'd like some feedback on my design – what can I do better? Is there anything I missed? Any common/idiomatic design choices?

I was going to post in the code review stackexchange, but I saw a previous answer that mentioned this as a better location.

Cheers


texts: Is it ok to create additional column for helping other column to be a PK?

texts:
Let's say a Person can have more than one nickname. He(she) must enter his(her) nicknames alongside with other details:

Here is the table columns:

  User_Id, Nickame, Age

Of course, for now there is anot any column or columns which are candidate to be a PK(composite or not).

In order to create PK, I have added Row_Num column to the table, which will start from 1 for each user. I will calculate the Row_Num in insert trigger based on the User_Id.

For example, table content will look like this:

15, 1, Assassin, 18
15, 2, Fighter, 14
15, 3, Gentleman, 5

16, 1, Lady, 5
16, 2, Clever, 15

Of course, the Pk will be UserId and Row_Num.

Could you please tell me is that idea Ok or not? If not, why and what is the better choices?

Is it ok? Or, I must include ID column wit autoincrement option?

UPDATE:

Let's say user will enter his nicknames in order. And one user can have same nicknames in different ages. And a user can have 3 nicknames in same age. And the firsta nd the last of nickname can be same:

15, 1, Assassin, 18
15, 2, Fighter, 18
15, 3, Assassin, 18


texts: Use An Out Variable And Perform Calculation In One Query

texts:
I am wanting to use an out variable, but I need to perform calculations as well.  Any way I write my query I keep getting this error:

  Msg 141, Level 15, State 1, Line 1
  A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Here is my DDL

Declare @SQL nvarchar(MAX), @stat float

Create Table PPerformance
(
  name varchar(500)
  ,attempts int
  ,success int
)

Insert Into PPerformance (name, attempts, success) Values
('Bob', '22', '10')
,('Red', '14', '12')

And this is my query syntax

Set @SQL = 'Select name, @stat = attempts/success FROM PPerformance where name = Bob'

EXECUTE sp_executesql @SQL, N'@stat float OUTPUT', @stat OUTPUT;
Select @stat As Stat

Drop Table PPerformance

What do I need to change so that I can store the value from the equation in the variable @stat


texts: the mystery of the table showing up in the subscription, but not in the publication

texts:
TL;DR: This question refer to sql server 2016, transactional replication

I've got a table that shows up in the subscription but not in the publication.

Here are the details:

In one of my servers in the USA I have a publication called ORCA_Repl_Pub.

In one of my servers in Europe I have a subscription (you guessed right: Orca_Repl_Sub) to the ORCA_Repl_Pub publication.

My issue is with table dbo.repl_application.

On the publisher I have a look at the articles and it is not there, you can see it is unchecked on the picture below:

or the same when I go to my publication_db and run the following:

sp_helpsubscription

or the following query has the same effect:

select
db_name() PublisherDB
, sp.name as PublisherName
, sa.name as TableName
, UPPER(srv.srvname) as SubscriberServerName
from dbo.syspublications sp
join dbo.sysarticles sa on sp.pubid = sa.pubid
join dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid

But then when I check on the subscriber_db side, on the subscriber server, by running the following query, to find out everything possible about the table dbo.repl_application I get:

SELECT *
FROM
    dbo.MSreplication_objects R
        INNER JOIN sys.objects so
                ON r.object_name = so.name
               AND so.type = 'P' --stored procedures

        INNER JOIN sys.sql_expression_dependencies dp
                ON so.object_id = dp.referencing_id

        INNER JOIN sys.objects ot
                ON dp.referenced_id = ot.object_id
               AND r.article = ot.name
where r.article = 'repl_application'

I have broken the picture down in 3 pieces for it to fit here:

Basically everything seems to be correct!!!

and using some magic I can see that the table in question has been used, as you can see on the picture below (the date of posting is 14-aug-2019:

So the question is:

how and why this repl_application is in the subscription but not in the publication?

I think it might be:

because on a previous version of that publication it was there, and as we deployed a new system, that table was no longer included.

But what about the table creation date and the last table update time, and last time the table was read?

the database was not wiped out completely or the subscription was not 100% dropped and that table remained with a reference on dbo.MSreplication_objects

Either way, all is working fine, I just would love to know if I could find out on the subscription side, if there is any indication anywhere, that that table should not be showing up.

so, if you have any ideas regarding that, please let me know and I will dig around it.


texts: Restore Database on Active Passive Failover Cluster

texts:
Well, I helped my boss's boss install 2 nodes of SQL Server 2008 on a Windows server 2008 failover cluster. I did this remotely and I don't have accesse to it.
(I run a active-active cluster on Server 2003).

I am waiting for the call on how to Restore a database from a stand-alone database to the new cluster (this is ACTIVE-PASSIVE).

I know the two nodes share the same disk resource,
but do I need to RESTORE it to the Active and then to the Passive ?

Or since they share the master database, do I just need one restore?


texts: MySQL: Filter results by joining a vertical table

texts:
Situation: I've got a table users and a table preferences along with some others. The table users is aligned horizontally (id, username,...) whereas the table preferences is aligned vertically (user_id, preference_key).

Table users has about 300K rows, preferences about 60M (about 20 out of 100 preferences per user)

Task: Find me all users that have a given combination of preferences (and/or pattern)

My first approach is this, run by a subquery

SELECT users.id FROM users LEFT JOIN ..other tables .. WHERE .. AND users.id IN (
   SELECT t1.id FROM
  (
     SELECT user_id as id, count(*) as total
     FROM preferences
     WHERE pref_key IN (key1, key2, key3, etc..)
     GROUP BY id
     HAVING total > 3
  )
)

having > 3: number of keys to match in an and-pattern, >0 for or-pattern

This approach is rather slow and I don't know if this approach is any good at all, as it looks complex with the subquery and IN () arguments.

For the performance - way more reads than writes - is the vertical alignment of the table structure optimal?


texts:
I have a few databases involved in replication as subscribers and they have triggers and are used by a different set of applications.

Every time I need to reinitialise those database I need to script the triggers, permissions and indexes, apply the snapshot and then re-apply those triggers, indexes and permissions.

Is there a script that I can use to script all the triggers of a current database?


texts:
My website has a form page that has radio buttons yes|no corresponding to 7 questions. When the user selects yes, the user needs to specify a value (either a location, name, or medical condition, depending on the question).

However, I'm torn whether I should include the specified values in the same table as the answers.

One one hand, when I include the specified values into the same table as the answers, it is easier to query.

One the other hand, the specified values can have null values (when the user selects yes).

I went with the latter and made 7 tables for each of the specified values. Refer to this diagram:

After looking at the table, I feel it's a bit of an overkill just for a simple functionality.

I would like suggestions as to what route should I go.

Should I merge everything into one table?

--OR--

Should I stick with this diagram?


texts: SQL Server Agent Jobs - Long running job delaying other jobs?

texts:
I have several agent jobs that run on different schedules.

Job 1 runs every 5 minutes, only executing a stored procedure that usually takes less than 30 seconds to run.

This job Selects from table "users" then uses a cursor to loop through the results and insert records into local tables. After the cursor finishes 4 batch calls are made to a remote linked server to insert the records from the local tables to remote tables. Then the stored procedure ends.

The stored procedure sql is wrapped in a Begin, Begin Try, End Try, End statement.

Job 2, runs every hour, it too only executes a stored procedure, however, this job can take 20 minutes to run.

Job 2 performs two different Selects both referencing the "users" table (which Job 1 also selects from) and inserts the results into local tables, but without using a cursor. Then 4 batch calls to a remote linked server to insert the records from the local tables to remote tables. This stored procedure is also wrapped in a Begin, Begin Try, End Try, End.

When I check the Job Activity Monitor log for Job 1, it shows the duration is consistently under 30 seconds, except for the time that it runs on the hour, with Job 2. At this time it shows the duration 20+ minutes - I guess as a result of Job 2, the long running job, running.

Does this mean that Job 1 begins and as it begins Job 2 starts causing Job 1 to not complete until after Job 2 finishes (the reason why SQL agent log shows the duration of Job 1 as 20 minutes, even though this job never takes 20 minutes to actually execute)?

Is there a way that I can keep this long running job isolated so that it doesn't cause other fast running jobs to be delayed?


texts: increased tablespace but dba_tablespace_usage_metrics order shows same

texts:
Although I appear to have solved the problem of the tablespace being full I am perplexed that after altering the the datafile size that the tablespace size has remained the same. What is it that I don't understand?

SQL> alter database datafile '/usr/lib/oracle/xe/oradata/XE/system.dbf' resize 1G;

Database altered.

SQL>  select * from dba_tablespace_usage_metrics order by used_percent desc;

TABLESPACE_NAME            USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
SYSTEM                  76600       76800   99.7395833
USERS                   18784      655360   2.86621094
SYSAUX                  45176     4194302   1.07708029
UNDO                      160       64000      .25
TEMP                    0     4194302        0


texts: MySQL Replication : Duplicate entries - SQL_SLAVE_SKIP_COUNTER or delete row?

texts:
I have a system which replicates one database to four slave servers. Every now and then, when traffic is high, one or more of the slave servers hits a duplicate insert error and the slave stops running.

When this happens, I have two choices - I can either SET GLOBAL SQL_SLAVE_SKIP_COUNTER or I can delete the offending row in the slave. Both seem to work and my logic says that given something happened to cause this problem, there is a possibility that the data in the slave is corrupted. Given that this can only happen on INSERTs, by deleting the row I guarantee the slave data will match the master once replication resumes. By skipping, if the data for that row is corrupted in the slave, it will remain corrupted.

Am I missing anything?

Further, given that this happens once every couple of months on two specific tables, is there any reason I shouldn't automate a process that triggers when this error is encountered, deletes that row in the slave and re-starts the slave?

EDIT: MySQL 5.5.29 and statement replication I believe.


texts: Automatically updating Remote Server(B) Table when there is a Update in Server(A) Table with different schemas

texts:
I am having an Update trigger in Server A calling a Stored Procedure on Server B(through Linked Server) to update rows in Table2 in Server B, whenever there is a update in rows in Table1 (in Server A).
The problem I am currently am facing is that it is only partially updating the values into Server B table2.
And I have no clue how the data is missing randomly while writing to the remote Table.

Update Trigger:
ALTER TRIGGER [dbo].[trgAfterUpdateTable1] ON [dbo].[Table1]

  After UPDATE
AS
       declare @col1 nchar(10);
       declare @col2 nvarchar(50);
       declare @col3 nvarchar(50);
       declare @col4 nvarchar(100);
       declare @audit_action varchar(100);
       declare @lastUpdated datetime;

       select @col1=i.col1 from inserted i;
       select @col2=i.col2 from inserted i;
       select @col3=i.col3 from inserted i;
       select @col4=i.col4 from inserted i;

       SET NOCOUNT ON;
if UPDATE(col1) OR UPDATE(col2) OR UPDATE(col3) OR UPDATE(col4)
    -- Insert statements for trigger here
       BEGIN
 exec [ServerB].[dbo].[sp_UpdateDetails_INTO_ServerB_FROM_ServerA] @col1, @col2, @col3, @col4

END


texts: Designing a database structure to retain route and transshipment details

texts:
Need help to design a table that can help finding the route / transshipment details for an address.

We have “Hub” and one Hub can have multiple “DeliveryPoints”.
When we check one address our system tells what is the final “Hub” and “DeliveryPoint” that will be responsible to deliver the product. But there may be no direct transport available from "Source Hub" to "Destination Hub".

Sample data

Source  Destination  Route
------  -----------  ----------------------------------------------------
City-1  City-2       Direct
City-1  City-3       First Transfer to City-2 and then City-3
City-2  City-3       Direct
City-4  City-5       First Transfer to City-1 and then City-2 then City-5
City-4  City-1       Direct

Question

What will be the table design where we can maintain above "Routing Details" so that when our system knows "Sending from City-1 to City-3" it will tell to send to "City-2" first. And then in "City-2 Hub" when we see the "Product it shall say send directly to City-3".

The present database structure

Currently we have one table called TransitDays that contains the "Transit Days from a City to another City":

CREATE TABLE TransitDays (
    TransitDaysId...
    FromCityId...
    ToCityId...
    TransitDays...
);

And another table named Mapping that retaines the ”City” served by the ”DeliveryPoint”, but this table does not keep the ”Middle Route”:

CREATE TABLE Mapping (
    Mapping...
    FromCityId...
    ToCityId...
    DeliveryPointId...
);


texts: Can a replication subscription also be used as a publication?

texts:
Some background:  We are working with a vendor who makes a read-only copy of their source database available to us in the cloud via filtered transactional replication.  The cloud database is a subscriber to the source database's publications.  The publishing database is multi-tenant, so only our customer data is replicated to our read-only copy.
The question is:  Could we could we publish from the subscriber/cloud database to a subscribing database in one of our own data centers?  If so, how might that work and would it be a management nightmare?


texts: how can I create an where in query that only check on field and ignore another fields?

texts:
query is something like this:
but I can not figure out how to get phones from inner select and ignore c field.

select * from users where phone in
(
            SELECT phone, COUNT( id ) AS c
            FROM users
            GROUP BY phone
            HAVING c >1
) and verified=true

I know I can do this like this:

select * from users where phone in
    (
        select t1.phone from (
                SELECT phone, COUNT( id ) AS c
                FROM users
                GROUP BY phone
                HAVING c >1) t1
    ) and verified=true

But I think it should be cleaner way.


texts:
I have a task which receives an input and performs a series of tasks on the SQL server, which some depend on the content of the server and on the input. For that I use a lot of selects and then logic based on input and then insert or update. The network activity between the server and the client is high while performing those tasks because they depend on the content of the server.

Will I gain any significant boost or degrade in performance if I move this to a stored procedure while the sql server and client are on the same machine?


texts:
Does anyone have any insight on how to view backups in Data Domain? Preferably with T-SQL. Thanks in advance.

Found the following T-SQL statement in EMC's documentation but I'm receiving the following error when using the correct "Client":

T_SQL Statement_______________________

USE [master]
GO
DECLARE @returnCode int
EXEC @returnCode = dbo.emc_run_delete ' -b "2 months ago" -e
"now" -N mssql -a "DDBOOST_USER=user" -a
"DEVICE_PATH=/path" -a "DEVICE_HOST=host" -a
"CLIENT=name"'
IF @returnCode <> 0
BEGIN
RAISERROR ('Fail!', 16, 1)
END

Error______________________

4690:ddbmexptool:The name 'CLIENT' does not correspond to a valid LNM parameter.
Msg 50000, Level 16, State 1, Line 10
Fail!


texts: MySql ORDER BY slow with join, fast as 2 queries

texts:
Why is the following query slow but fast when I provide the values inline?
select u.* from user u
join user_group g on u.group_id = g.id
where g.account_id = 1
order by u.id limit 10;
-- takes ~30ms

select id from user_group where account_id = 1;
-- which is (99,198,297,396,495,594,693,792,891,990)

select * from user
where group_id in (99,198,297,396,495,594,693,792,891,990)
order by id limit 10;
-- takes ~1ms

Sub query is also slow. The plan is the same as the join.
select u.* from user u
where u.group_id in (select id from user_group where account_id = 1)
order by u.id limit 10;
-- ~30ms

All queries produce the same result.
98  0   99
197 0   198
296 0   297
395 0   396
494 0   495
593 0   594
692 0   693
791 0   792
890 0   891
989 0   990

Schema
I've got the following simple table structure, with 100 accounts 1k user_groups and 10 million users.
create table account(
  id int primary key auto_increment
);

create table user_group(
  id int primary key auto_increment,
  account_id int not null,
  foreign key (account_id) references account(id)
);

create table user(
  id int primary key auto_increment,
  deleted tinyint default 0,
  group_id int not null,
  foreign key (group_id) references user_group(id)
);

-- I've been trying with this index, but it doesnt seem to help.
create index user_1 on user(group_id, id, deleted);

Plans

The plan with the join using indexes and doing temporary filesort.

I don't understand why MySql seems to think it needs actually do the complete join to filter the data down. We clearly don't read anything from user_group.
For comparison I've tried the same thing in PostgreSQL and both queries run fast.
Why is it slow and is there a way to write the query (a single query!) that does it quickly? Sub select doesnt work.
This dbfiddle shows the problem.
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8ed68310d8ca72e9daef389dc0469a6f
Using MySql 8.0.23
Thanks
Edit
Here are the complete Session Status Handler debug details. It looks like the slow query is reading every value unlike the fast one.
-- FLUSH STATUS;
-- select u.* from user u join user_group g on u.group_id = g.id where g.account_id = 1 order by u.id limit 10;
-- SHOW SESSION STATUS LIKE 'Handler%';

Handler_commit  1
Handler_delete  0
Handler_discover    0
Handler_external_lock   4
Handler_mrr_init    0
Handler_prepare 0
Handler_read_first  0
Handler_read_key    11
Handler_read_last   0
Handler_read_next   100110
Handler_read_prev   0
Handler_read_rnd    0
Handler_read_rnd_next   0
Handler_rollback    0
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  0
Handler_write   0

-- FLUSH STATUS;
-- set @uGroups := (select group_concat(id) from user_group where account_id = 1 group by account_id);
-- select * from user where group_id in (select @uGroups) order by id limit 10;
-- SHOW SESSION STATUS LIKE 'Handler%';

Handler_commit  2
Handler_delete  0
Handler_discover    0
Handler_external_lock   4
Handler_mrr_init    0
Handler_prepare 0
Handler_read_first  0
Handler_read_key    2
Handler_read_last   0
Handler_read_next   19
Handler_read_prev   0
Handler_read_rnd    0
Handler_read_rnd_next   0
Handler_rollback    0
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  0
Handler_write   0


texts: InnoDB Buffer pool not showing as much I configured in MariaDB 10.5

texts:
I'm using MariaDB 10.5 on Centos7 with 4G RAM and 2 CPU.
Here's my.cnf configuration
[mysqld]
log-error=/var/lib/mysql/mysql-3.kannel.com.err
max_allowed_packet=1G

event_scheduler = ON
innodb_file_per_table=ON
#innodb_file_format=Barracuda
innodb_buffer_pool_size=3G
innodb_buffer_pool_instances=6
innodb_log_file_size=1G
innodb_log_files_in_group=1
innodb_log_buffer_size=32M
innodb_log_write_ahead_size=32M
innodb_flush_log_at_trx_commit=2
innodb_read_io_threads=32
innodb_write_io_threads=128
innodb_io_capacity=10000
innodb_thread_concurrency=6
innodb_flush_method=o_direct

join_buffer_size=128M
sort_buffer_size=20M
read_rnd_buffer_size=128M
datadir=/var/lib/mysql
open_files_limit=10000
default-storage-engine=InnoDB
max_connections = 500

# CACHES AND LIMITS #
tmp-table-size                 = 20M
max-heap-table-size            = 32M
query-cache-type               = 1
thread-cache-size              = 50M
table-definition-cache        = 128M
table-open-cache               = 128M
query_cache_size = 50M
query_cache_limit=128M

But when I see InnoDB engine status using SHOW ENGINE INNODB STATUS\G; it gives me below information where Buffer pool size   193296 but Total large memory allocated 3288334336
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 3288334336
Dictionary memory allocated 19166328
Buffer pool size   193296
Free buffers       179141
Database pages     49186
Old database pages 18176

So if Buffer pool size   193296, where rest of the memory is being allocated?
Thanks!


texts: Recommendations on Partitioning Multi-Tenant Data in SQL-Server

texts:
I would appreciate input on design considerations for partitioning (or not) a multi-tenant business application.  All data tables use a composite key that looks something like:
CREATE TABLE SomeTable(
  TenantId SMALLINT NOT NULL,
  ID INT NOT NULL,
  SomeData ...,
  CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED (TenantID, ID)
)

Almost all reads are keyed to the specific tenantid.  Writes come in randomly by tenantID but any data in place for 3-5 days tends to never change after that.
Questions

If I don't partition, is (tenantID, id) or (id, tenantid) a better choice.  Assume reads are all by tenantid, writes are random by tenantid, and a there is enough downtime nightly to run a maintenance plan.
What are the pros and cons of creating a separate partition for each tenantID?
Will partitions reduce page fragmentation if the tenantID is the first key?
If the partitions are in the primary file, does that alter the arguments?
If there are 10 or 1000 partitions, does it alter the arguments?
Does the answer depend on whether I use SQL 2016 or SQL Azure?


texts: Dynamic script to Drop Indices of a specific column

texts:
I´m currently writing an SQL script to update our database and for that I need a script to drop indices that rely on a specific column (because I also want to drop the column).

In my script I have used the normal approach by using the following SQL for known indices (before dropping the column via ALTER TABLE).

IF EXISTS(SELECT * FROM sys.indexes WHERE name = '{IndexName}' AND object_id = OBJECT_ID('{TableName}'))
BEGIN
    DROP INDEX {Table}.{Index};
END

But on some systems of our software I cannot use this approach, because there are some indices that are added manually and I need to identify these indices to drop them also.

I have found a good SQL statement on Stack Overflow to list all indices, columns and tables, but I´m stuck how I can select the names of the indices so that I can pass them to DROP INDEX.

Pseudo-code (something like that):

 declare indiciesToDrop = Select indexName from sql-statement where t.Name = {TableName} and col.Name = {ColumnName};

 DROP INDEX indiciesToDrop;


texts: Is there a way to mirror databases in MySQL?  In SQL Server, you have the ability to link 2 databases in a master to master relationship.  One DB will accept transactions and the transactions will be committed on both DBs at the same time.  Is there a a similar concept in MySQL?


texts: SQL Server 2005 and Standalone VB application

texts:
I'm new to this forum and just want to ask some questions/suggestions on how to manage my database..

I'm having an application with sql server 2005 database as a backend.
I want make the application standalone, the application is installed along with an sql server 2005 installer, so I don't have any control on my app when it is deployed/installed in other computers.
I have my app working fine, but the database is not secure if ever the app is installed on other computer.

Now here's the scenario. (if the user is a techie guy and wants to dig things up)
The user installs sql server 2005.
The user installs the app.
The user can see the database via windows explorer (database is .mdf file)
Opens up SQL Server, uses windows authentication.
Attaches the database, and tada! Instant access to the database.
Now he can freely modify the contents of the database without the use of the app.

So how can I solve this? Is there any possible ways to avoid this? Any suggestions?
Take note that I can't control the application because it is installed on a different place
P.S., I'm quite new to this, actually this is my first project on a company :)


texts:
I am struggling with a DAX measure. Here is the fact and 2 dimension tables I have:

factAction(dimAccountID, dimActionID, Date, ActionQty)
dimAction(dimActionID, ActionCode, ActionDescription)
dimDate(Date, WorkingDay)

The relationships are:

dimDate.Date -> factAction.Date
dimAction.dimActionID->factAction.dimActionID

And I have a DAX measure for ActionQty = SUM('factAction'[ActionQty])

We want to report on the number of actions of ActionCode AAA. Fine, that's easy. Just select ActionCode = AAA into the Power BI filter.

Now we want to also report, for those Accounts that had ActionCode AAA, the sum of ActionQty for ActionCode AAB where the ActionDate is within 2 working days of the AAA Action Date.

Any suggestions would be welcome!

Edit: added sample data and expected output

So when Filtering for AccountID AC1 and April 2020, this new measure would return 0 (because the number of working days between the AAA and AAB ActionCodes for this AccountID is greater than 2)

When filtering for AccountID AC2 and Aptil 2020, the new measure should return 1 (because the number of working days between 16 April and 20 April is less than 2)


texts: Can't shrink Log file - "An invalid Floating point operation occurred. (Microsoft SQL Server, Error: 3623)"

texts:
When selecting the Transaction Log file from the dropdown on the Shrink Files screen in SSMS, I get the following error:

  Failed to retrieve data for this request.  (Microsoft.SqlServer.Management.Sdk.Sfc)  Additional information:  An invalid Floating point operation occurred. (Microsoft SQL Server, Error: 3623)

It is happening on the same database on 2 different servers (restored from a SQL 2008 R2 Enterprise Ed. server to a SQL 2016 Standard Edition server nightly).  There are older copies spawned of the same database on QA and Dev from dates 2 months ago, and they do not experience the issue, so it is something that happened recently.

I have tried:

DBCC CHECKDB - no errors
Took DB offline, deleted transaction log, recreated t-log using T-SQL. - No effect on error message
Detached DB, Reattached - No effect on error message
Set DB to FULL recovery mode (was previously on SIMPLE), allowed users to generate a good amount of transactions successfully. - No effect on error message
Set DB back to SIMPLE recovery mode. - No effect on error message
Attempted DBCC SHRINKFILE (<database_name>_log, 1).  - No effect on error message.  However, running this did generate the following message:

  Msg 8985, Level 16, State 1, Line 24
  Could not locate file '<database_name>_log' for database '<database_name>' in sys.database_files. The file either does not exist, or was dropped.

... Which made me decide to go do a select over sys.database_files and see if anything looked out of place.
It looks normal to me... look and see if you see anything that looks abnormal:

file_id= 2
file_guid= 2BA3CDBB-3F3F-4F81-B067-25712493E963
type= 1
type_desc= LOG
data_space_id= 0
name= <database_name>_log
physical_name= L:\MSSQL\Data\<database_name>.ldf
state= 0
state_desc= ONLINE
size= 64000
max_size= 268435456
growth= 64000
is_media_read_only= 0
is_read_only= 0
is_sparse= 0
is_percent_growth= 0
is_name_reserved= 0
create_lsn= NULL
drop_lsn= NULL
read_only_lsn= NULL
read_write_lsn= NULL
differential_base_lsn= NULL
differential_base_guid= NULL
differential_base_time= NULL
redo_start_lsn= NULL
redo_start_fork_guid= NULL
redo_target_lsn= NULL
redo_target_fork_guid= NULL
backup_lsn= NULL

UPDATE  Since now I'm able to shrink the log using T-SQL  (DBCC SHRINKFILE (_log,...) ), I'm lowering the urgency of a fix.  However, I would still like to know if anyone has a fix for the original error, when shrinking using the GUI in SSMS.


texts: Is there a way to identify the object behind the database synonym?

texts:
I have a script which uses a synonym. I have to alter that code and the problem is I can't understand which object that synonym refers to. Is there a way to find out? I am using the original database instance where that synonym has been created.

PS I am using MS SQL Server.

Thanks for your time.


texts: Is it ok to use 4 application server against one database server?

texts:
Initially I have two database server (Master Master Replication config with Haproxy load balancing) A and B. And two application server say P and Q.
Both P and Q is pointed towards A will move to B in case of failure. OLTP operations are huge in numbers per minute.

Now we are adding two more app servers R and S.

Just want to know experts opinion. Is it ok to point both R and S towards A?
Or should I use B this time.

Please suggest.


texts: How do I make a nullable "parent" hierarchy level?

texts:
I'm setting up my first dimensional database with SSAS, and I have this [Materials] dimension that needs a hierarchy that goes something like this:

[PriceCode    v] --> nullable
  Price Code
  ...
[Material     v]
  Code
  AltCode
  Name
  ...
[Id           v]  --> not actually exposed as a hierarchy level
  DateInserted
  DateUpdated
  DateDeleted
  EffectiveFrom
  EffectiveTo

The problem is that the [PriceCode] attribute is nullable; the DSV has a FK between a [Materials] and a [PriceCodes] table, and [Materials].[PriceCodeId] is nullable.

Is there a way to still define a hierarchy where a nullable attribute is a parent? I've fiddled with UnkownMember and UnknownMemberName and the attribute key's NullProcessing setting, but couldn't get the dimension to process.

Bonus points if someone could confirm whether I'm approaching the slowly-changing dimension problem correctly, by making a hierarchy level based on business keys (i.e. the Code field; the natural key includes the EffectiveTo field, which is null for current image of a record), and treating the SCD metadata as a level of its own.


texts: Is there a shorthand way to 'Auto_Fix' all Orphaned Users in an SQL Server 2008 R2 database?

texts:
It's fairly straight forward to fix up a single orphaned SQL user to a login using:

  EXEC sp_change_users_login 'Auto_Fix', 'user'

I could script this, but is there an existing stored procedure which automatically tries to fix up every orphaned user in a given database?


texts: Replication error: the principal "dbo" does not exist

texts:
I have replication set up between two servers.  This morning I came in and it was failing.  I was getting these errors:

  Error messages:

  The process could not execute 'sp_replcmds' on 'PSQL3'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011) Get help:
  Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated,
  or you do not have permission. (Source: MSSQLServer, Error number:
  15517)
  The process could not execute 'sp_replcmds' on 'PSQL3'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)


texts:
I have no control over the source table.
There is a mixture of NULLs and Empty strings.
What I need is to get all users on 1 line and semicolon delimit a list of all their entitlements. I can strip the leading semicolon in later code but the double semicolons and empty string semicolon are killing me.

My table:

EMPLOYEEID | USERNAME | ISPRIMARY | ENTITLEMENTS
123456     | BSmith   |   Y       | ac-les-all-full;ac-haw-all-full
123456     | BSmith   |   N       | ac-sco-all-full
987654     | ZJones   |   N       | NULL
987654     | ZJones   |   Y       | ac-trn-std-full
987654     | ZJones   |   N       |
456789     | RTaylor  |   Y       |

I want to return:

USERNAME | ENTITLEMENTS
BSmith   | ;ac-les-all-full;ac-haw-all-full;ac-sco-all-full
ZJones   | ;ac-trn-std-full
RTaylor  |

My Current SQL:

SELECT
    USERNAME
    ,ENTITLEMENTS = (
        SELECT DISTINCT ';' + ENTITLEMENTS
        FROM [staff_user_data] STS
        WHERE STS.[EMPLOYEEID] = STP.[EMPLOYEEID]
        FOR XML PATH(''))
FROM [staff_user_data] STP
WHERE ISPRIMARY = 'Y'

What I am getting is

USERNAME | ENTITLEMENTS
BSmith   | ;ac-les-all-full;ac-haw-all-full;ac-sco-all-full
ZJones   | ;;ac-trn-std-full
RTaylor  | ;


texts: I am learning sqlite from a book which has mentioned collation and collating  sequence multiple times. What does it mean exactly in the database world?


texts: ORA-02070: database does not support antijoin in this context

texts:
I don't think this is even a question, but in case someone has seen something like this before or has any thoughts besides 'file a bug with Oracle' (which I'm doing), I'd love to hear it.

create table pat (patientid VARCHAR2(32), pcpid VARCHAR2(32), bval1 NUMBER(1), bval2 NUMBER(1));
create table tab (setmemberid VARCHAR2(32), uuid VARCHAR2(32));

SELECT COUNT(patientId) n FROM (
SELECT DISTINCT pat.patientId, pat.bval1, pat.bval2
FROM pat
 LEFT OUTER JOIN
 (SELECT /*+ CARDINALITY(tab 10000) */ setmemberid FROM tab WHERE uuid = '7C06216F-EFC8-418A-A6D9-77C7F6FE7AB1') fsq1
 ON pat.pcpid = fsq1.setmemberid
WHERE fsq1.setmemberid IS NULL
) pat
GROUP BY GROUPING SETS ((pat.bval1), (pat.bval2), ())
;

If I remove the CARDINALITY hint query works fine.


texts: How to debug problems with SQL Server multiserver (Master and Target server) administration?

texts:
I've tried to set Mater and Target Servers on various cobinations of SQL Servers 2012 R2 on Window Server 2008 R2 and Windows 7 machines but always getting one or 2 of the following errors during setting either target or master server:

Enlist TSX Progress
1) Create MSXOperator (always Success)

Checking for an existing MSXOperator.
Operator does not exist. Creating.
Successfully created MSXOperator.

2) Make sure the Agent service for '*Master-Server-Name' is running (sometimes an Error, sometimes not)*

An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.SqlWmiManagement)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.InnerWmiException&LinkId=20476

ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

Invalid class  (System.Management)
3) Ensure the agent startup account for 'Master-Server-Name' has rights to login as a target server (if Success here, then 4) is error or both 3) and 4) are errors)

Checking to see if the startup account for 'Target-Server-Name' already exists.
Login exists on server.
Checking to see if login has rights to msdb.
Login does not have rights to msdb. Adding.
Created new user in msdb.
Checking to see if user is a member of the TargetServersRole.
Adding user to the TargetServersRole.
Added user to TargetServersRole.

4) Enlist 'Target-Server-Name' into 'Master-Server-Name' (if Success here, then error in 3) or both in 3) and 4))

Enlisting target server 'Master-Server-Name' with master server 'NOVOSIB-1C'.
Using new enlistment method.

Messages
MSX enlist failed for JobServer 'Target-Server-Name'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

The enlist operation failed (reason: SQLServerAgent Error: Unable to connect to MSX 'Target-Server-Name'.) (Microsoft SQL Server, Error: 22026)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3128&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476

In all cases I was using a domainUser login with sysadmin role for setting SQL Server Agent service and owner of an SQL Server Agent job (or, in the latter, tried also sa), Windows Authentication to localhost in used by them maintenance plans. The used domainUser is part of (local) Administrators group in each of the Windows (machines).

The SQL Server Management Studio is run As Administrator.

How can I troubleshoot these errors?
What should I check and look for?

Specifically, I do not quite understand:

Which server and which login to check in from the phrase:
"Ensure the agent startup account for 'Master-Server-Name' has rights to login as a target server (Success)"
How to check that this login has rights for msdb from the phrase:
 "Checking to see if login has rights to msdb"
  -


texts: SQL Server 2008 R2 - Set tempDB size on full disk

texts:
One of my SQL Server 2008 R2 instances had a job that went mad last night, ended up with the job auto-growing tempDB until it ran out of disk space. To compound the issue I noticed that tempDB is using default values, so if I restart it will go back to 8mb data file growing in 10% chunks.

So at this time there is 8mb free space left on the TempDB drive. Whilst it is in this state, can I set the desired TempDB size of 30GB using ALTER DATABASE commands ready for the next restart, or will the command fail as there is no free space on the drive?

EDIT:-

As per Robert's comments below. This only needs 1 restart as running ALTER DB command on tempdb before restart will cause SQL Server to set tempDB correctly on restart


texts: How dependent are backups on msdb tables like backupset?

texts:
I am trying to get some practice making a backup maintenance plan and restoring those backups. I have a script that churns through the most recent full database backup and any subsequent transaction log backups, all of which are in a single appended backup file.

My question is in two parts. First, is it possible to determine everything I need to know to restore a database from my backup file itself, without querying the msdb backup tables? Second, If I was to accidentally run sp_delete_backuphistory and wipe out all history, how bad of a day would I have?

Bonus question: How do expiration dates work when I have a backup that I continually append to? Does the expired data get deleted on subsequent backups, or do I need to create a new backup file, or something else? If I was creating a new file for every backup, I could simply check that files expiration date and delete it, but I am more confused with the appended backup sets.


texts: SSIS Excel Import - First row getting skipped

texts:
I am using VS 2017(SSDT) with SSIS(2012). I'm trying to import an Excel file, but the issue is that the first row is getting skipped.
I've unchecked First row has column names in Excel Connection Manager, but in vain.
Please advice on how to import the first row.


texts: Report based on MV: Deliberately make the report fail if MV is out of date?

texts:
I have a query that is used in a BIRT report (in IBM's Maximo Asset Management platform).
I'm considering storing the query in a materialized view in order to improve performance and reduce the load on the system.
Question:
Is a way to deliberately make the report fail  if the MV is out-of-date?

In other words, we'd rather have no data in the report, than wrong data.

Edit:
I removed the excessive details from the question.


texts: Split MySql table into two tables with One to many Relationship

texts:
I have an old database table in MySql. It is used to store the orders from online ecommerce.  Simplified diagram looks like this:

+----+------+-----------------------+-------+--------------+
| id | name | address               | phone | order_number |
+----+------+-----------------------+-------+--------------+
|  1 | Joe  | Joes Address          | 1111  | 1390842      |
|  2 | Paul | Pauls Address         | 2222  | 9082309      |
|  3 | Greg | Gregs Address         | 3333  | 0928340      |
|  4 | Lucy | Lucys Address         | 4444  | 9028340      |
|  5 | Paul | Pauls Address         | 2222  | 8958399      |
|  6 | Tom  | Toms Address          | 5555  | 9084024      |
|  7 | Lucy | Lucys Another Address | 4444  | 9801983      |
|  8 | Paul | Pauls Another Address | 2222  | 0982304      |
+----+------+-----------------------+-------+--------------+

For each order there is a separate name address and phone number. Some customers placed the order 2 or more times ( Lucy and Paul).  Paul placed two orders when he lived at Pauls Address and one order when he moved to Pauls Another Address.  Lucy placed an order and than another order once she moved.

What I need to do is split this table into two tables.  One table will be user_account table that will hold only the name and the phone number, while another table will hold the addresses and will be related to users_account by FK.

user_accounts should look like this:

+----+------+-------+
| id | name | phone |
+----+------+-------+
|  1 | Joe  | 1111  |
|  2 | Paul | 2222  |
|  3 | Greg | 3333  |
|  4 | Lucy | 4444  |
|  5 | Tom  | 5555  |
+----+------+-------+

while addresses should look like this:

+----+-----------------------+-----------------+
| id | address               | user_account_id |
+----+-----------------------+-----------------+
|  1 | Joes Address          | 1               |
|  2 | Pauls Address         | 2               |
|  3 | Gregs Address         | 3               |
|  4 | Lucys Address         | 4               |
|  5 | Toms Address          | 5               |
|  6 | Lucys Another Address | 4               |
|  7 | Pauls Another Address | 2               |
+----+-----------------------+-----------------+

How would I go about generating those 2 tables from my old single table.  Any help appreciated.  I'm completely stuck with this, and not even sure what query should I look for in google to point me into right direction. There is about 100k rows in the original table.

PS.

I would like to create the user_accounts table based on distinct phone numbers in regular table.


texts: Is there a neat way to build a user posts/likes/comments query?

texts:
Say we have table users and three tables posts, comments, likes, which are many-to-one to users. I want to build some report query for all users, even if they have 0 posts, comments or likes.
Is there a way of doing this more elegant, than

select
  users.id,
  users.name,
  ifnull(u1.post_score,0),
  ifnull(u1.comments_score,0),
  ifnull(u1.likes_score,0)
from users left join (
select id,sum(post_score),sum(comments_score),sum(likes_score) from
(
(select
  user.id,
  posts.score as post_score,
  0 as comments_score,
  0 as likes_score
from users inner join posts on posts.user_id = users.id)
union all
(select
  user.id,
  0 as post_score,
  comments.score as comments_score,
  0 as likes_score
from users inner join comments on comments.user_id = users.id)
union all
(select
  user.id,
  0 as post_score,
  0 as comments_score,
  1 as likes_score
from users inner join likes on likes.user_id = users.id)
) t1
group by id ) u1
on u1.id = users.id

? Thank you.
P.S. MySQL


texts: In MSSQL, when a lock is acquired with a statement, the locking mode is decided by the engine not by the programmer. Is there a way to force the engine to use a specified Locking Mode (i.e. Row Lock)?


texts: table_open_cache increase issue - MariaDB 10.2

texts:
I'm trying to increase to 9000 but MariaDB sets it at 4895 automatically.

Here is my simple config
https://pastebin.com/raw/4eFiuFSv

MySQLTuner output:

Control warning line(s) into /var/lib/mysql/cp.goal.ge.err file
Control error line(s) into /var/lib/mysql/cp.goal.ge.err file
Restrict Host for user@% to user@SpecificDNSorIp
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:

Beware that open_files_limit (10000) variable

should be greater than table_open_cache (4895)

Optimize queries and/or use InnoDB to reduce lock wait
Consider installing Sys schema from https://github.com/mysql/mysql-sys

Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 125M)
    table_open_cache (> 4895)


texts:
I'm trying to write a munin plugin to graph DB sizes. Alongside using pg_database_size I want to graph the components thereof as well.

So far, I've come up with the following:

SELECT
    SUM(pg_relation_size(oid, 'main')) AS main_size,
    SUM(pg_relation_size(oid, 'vm')) AS vm_size,
    SUM(pg_relation_size(oid, 'fsm')) AS fsm_size,
    SUM(
        CASE reltoastrelid
        WHEN 0 THEN 0
        ELSE pg_total_relation_size(reltoastrelid)
        END
    ) AS toast_size,
    SUM(pg_indexes_size(oid)) AS indexes_size
    FROM pg_class
    WHERE reltype != 0 -- 0=indices, covered by pg_indexes_size

However, summing up those 5 values returns me something which is not the same as the result of pg_database_size. The difference seems to be less significant for larger DBs.

Example on a larger DB:

┌──────────┬────────┬─────────┬─────────┬──────────┬───────────────┬──────────────────┬─────────┐
│   main   │   vm   │   fsm   │  toast  │ indexes  │ sum_of_values │ pg_database_size │  diff   │
├──────────┼────────┼─────────┼─────────┼──────────┼───────────────┼──────────────────┼─────────┤
│ 72441856 │ 753664 │ 2392064 │ 4677632 │ 41377792 │ 116 MB        │ 111 MB           │ 5222 kB │
└──────────┴────────┴─────────┴─────────┴──────────┴───────────────┴──────────────────┴─────────┘
(1 row)

Example on a smaller DB:

┌─────────┬────────┬─────────┬────────┬─────────┬───────────────┬──────────────────┬─────────┐
│  main   │   vm   │   fsm   │ toast  │ indexes │ sum_of_values │ pg_database_size │  diff   │
├─────────┼────────┼─────────┼────────┼─────────┼───────────────┼──────────────────┼─────────┤
│ 2809856 │ 385024 │ 1351680 │ 557056 │ 2924544 │ 7840 kB       │ 6642 kB          │ 1198 kB │
└─────────┴────────┴─────────┴────────┴─────────┴───────────────┴──────────────────┴─────────┘
(1 row)

What am I missing?

Maybe related, maybe not: I'm shocked to see the index size. They are HUGE. Is something in my query wrong?

Here is a script I used to inspect the different values:

SELECT
    SUM(pg_relation_size(oid, 'main')) AS main,
    SUM(pg_relation_size(oid, 'vm')) AS vm,
    SUM(pg_relation_size(oid, 'fsm')) AS fsm,
    SUM(
        CASE reltoastrelid
        WHEN 0 THEN 0
        ELSE pg_total_relation_size(reltoastrelid)
        END
    ) AS toast,
    SUM(pg_indexes_size(oid)) AS indexes,
    pg_size_pretty(
        SUM(pg_relation_size(oid, 'main'))::bigint +
        SUM(pg_relation_size(oid, 'vm'))::bigint +
        SUM(pg_relation_size(oid, 'fsm'))::bigint +
        SUM(pg_indexes_size(oid))::bigint +
        SUM(
            CASE reltoastrelid
            WHEN 0 THEN 0
            ELSE pg_total_relation_size(reltoastrelid)
            END
        )::bigint
    ) AS sum_of_values,
    pg_size_pretty(pg_database_size(current_database())) AS pg_database_size,

    pg_size_pretty(
        SUM(pg_relation_size(oid, 'main'))::bigint +
        SUM(pg_relation_size(oid, 'vm'))::bigint +
        SUM(pg_relation_size(oid, 'fsm'))::bigint +
        SUM(pg_indexes_size(oid))::bigint +
        SUM(
            CASE reltoastrelid
            WHEN 0 THEN 0
            ELSE pg_total_relation_size(reltoastrelid)
            END
        )::bigint - pg_database_size(current_database())::bigint
    ) AS diff

FROM pg_class
WHERE reltype != 0;


texts: SQL Server (2014) Failover Cluster Performance with many databases

texts:
We are considering implementing SQL failover clustering and I have a question about how SQL Failover clustering works with regards to performance.  Specifically, we would have about 1000 dbs mounted on said cluster, and my concerns are:

Performance during a failover: Will there be an outage during the failover operation?
How long can I expect the failover to take?

I'm not sure how it's implemented, but I'm just imagining the analogy of shutting down a SQL server service on one machine, and once complete starting up on another machine.  With all of these dbs, just recycling the SQL service takes about 10 minutes.  Should I expect the same from the failover?


texts: How much RAM should I get for a cloud-hosted PostgreSQL data warehouse?

texts:
I'm looking at migrating a current PostgreSQL data warehouse to a cloud host with SSD storage and RAM as one of the main sizing variables. The bulkiest data we're dealing with at the moment will live on monthly partitioned tables. Each month is about 70GB with indexes (40-ish w/o). Data is likely mostly bulk-loaded periodically, and will then be accessed by a small team of 5 researchers.

I've been trying to search for recommendations for spec'ing RAM on this site and all I've found is:

Fit the entire DB (>1TB, unrealistic)
More is better

Should there be enough RAM to at least load an entire index (16GB) to RAM? Are there any other details I should provide?


texts: sp_recompile does not recompile inline TVF. What alternatives are there other than using an ALTER statement?

texts:
In the following, I would expect the first SELECT of the new column [Bit01] to be successful however it is not. Is there any way of avoiding the use of the ALTER statement to get it to work?

I have not been anything to find anything that confirms or denies my findings on the relevant MSDN page: https://msdn.microsoft.com/en-us/library/ms181647(v=sql.110).aspx

-- Shell table
CREATE TABLE [tblRecords]
(
    Id INT NOT NULL PRIMARY KEY CLUSTERED
);
GO

-- Inline TVF
CREATE FUNCTION [ufnAllRecords]()
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM [tblRecords]
);
GO

-- Add a column
ALTER TABLE [tblRecords]
ADD [Bit01] BIT NULL;
GO

-- Recompile the function
EXECUTE sp_recompile N'ufnAllRecords';
GO

-- Attempt to select the new column
SELECT [Bit01]
FROM [ufnAllRecords]();
GO

-- Column does not exist and so it errors...
-- However it works after ALTER'ing it
ALTER FUNCTION [ufnAllRecords]()
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM [tblRecords]
);
GO

-- Now works
SELECT [Bit01]
FROM [ufnAllRecords]();
GO

-- Cleanup
DROP FUNCTION [ufnAllRecords];
GO
DROP TABLE [tblRecords];
GO


texts:
Is it possible to know all the tables a user has access to using a single query?

I tried with pg_role and pg_user with no luck. Can anyone tell me about a system table which has the data of all the objects that a user has access to?


texts: SQL Server second transaction log seems to be used as well - not only after first one is full

texts:
we have come across a strange phenomenon using a second transaction log file for a database. Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) Standard Edition. On Windows Server 2016, a single instance, no cluster or replication.
We have added the second log file after the first was already in use the whole time. We thought the second file would only be in use after the first one has no more space to grow / is full.
I know that it is probably not such a good idea to have two log files in the first place, but for the moment we would like to have the second one as an insurance, so to speak.
Currently there is enough free space on the first log volume, 416 GB, there are 983 GB free on the second. The first log file is allowed to have a maximum size of 300 GB, currently has a size of 40 GB, growth by 1024 MB, limit 300 GB. The second log is currently 16 GB, growth by 1024 MB, limit 2097152 MB.
Now when we execute
select * FROM sys.dm_db_log_info(DB_ID('MultileLogFiles'))

we see that both log files are actually in use (please see below) - or at least that is our conclusion, we may be wrong. The Resource Monitor supports our claim and shows that the second log file is also used, both appear from time to time and the 2nd one has also grown, not only the first one.
Could anyone give us a hint why that should be the case, why SQL Server does not wait until log file 1, the first in the list, is full or may be the other one and not the first, but why both? That seems strange.
Best regards,
Harry
database_id    file_id    vlf_begin_offset    vlf_size_mb    vlf_sequence_number    vlf_active    vlf_status    vlf_parity    vlf_first_lsn    vlf_create_lsn
    7            2             8192              0.24              603616                1            2            128    000935e0:00000010:0001    00000000:00000000:0000
    7            2             262144            0.24              603617                1            2            128    000935e1:00000010:0001    00000000:00000000:0000
    7            2             516096            0.24              603598                1            2            128    000935ce:00000010:0001    00000000:00000000:0000
(...)
    7            3             4437573632        128               603683                1            2            128    00093623:00000010:0001    00092757:0003fd82:002e
    7            3             4571791360        128               603684                1            2            128    00093624:00000010:0001    00092757:0003fd82:002e
(...)
    7            3             15040774144       1024              603464                0            0            64     00000000:00000000:0000    00092787:001ffd67:00c0
    7            3             16114515968       1024              603466                0            0            64     00000000:00000000:0000    00092789:001ffd45:002e
(...)


texts: Forced Parameterization generating duplicate plans

texts:
https://www.brentozar.com/pastetheplan/?id=H1qoS7BSu
I am using Azure SQL 18 vCore db with Forced Parameterization, and MAXDOP 8.  The vCore count may change much lower for other dbs but for the same query.  Running sp_blitzcache has shown a query with the following warnings:
Compilation Timeout, Forced Parameterization, Multiple Plans (11), Plan created last 4hrs, Long Running With Low CPU, Many Rows Table Spool, non-SARGables

The main table has about 600Krows, and the two supporting tables have about 200 rows.
I'm a newbie curious about a number of these things.  There are about 2700 plans in the cache currently for this query_hash, all using the same query_plan_hash.  Compilation times out a very large portion of the time (can't confirm that it's every time).  I'm about to turn on traceflag 4199 in hopes of getting rid of the timeouts, and the performance of the query is acceptable within the app context that I know of, but I'd like to understand what is stumping the compiler.  Thanks for any help on this!


texts: Is it safe to copy DB files directly from an LVM snapshot?

texts:
I saw two different approaches for backing up database with LVM snapshot. This article suggests spawning another MySQL instance to do an SQL dump. MYSQL Performance Blog suggests that it is safe to copy directly the database files:

  6) Copy data to backup. Normally you can skip slow query logs and
  error log while taking backup. You also can skip most of binary logs –
  however if some of your slaves are far behind you might want to keep
  some of last binary logs just in case, or you can assume in case of
  recovery from the backup you will need to restore slaves as well and
  skip binary logs in your backup process.

For database in InnoDB format, is it ok that I just backup the /var/lib/mysql/ folder?


texts: Block user from revoking CONTROL to schema when granted CONTROL on a schema

texts:
I recently ran into an interesting conundrum. We grant CONTROL on schemas to developers to lock them down to just changes in the schema. Recently we started using database projects. One of the developers made some changes to a proc and then used the schema compare tool to push the changes to the development sql server. The database project did not contain the roles that had been granted CONTROL so the compare generated REVOKE CONTROL on the schemas. So his compare script failed as he immediately revoked himself when applying the compare changes.

I know the easy fix is to add the roles and grants to the schema in the database project so this will not happen again, but is there a way to block the developer from revoking their control of the schema? I've tried seeing if I could do it with permissions or a server ddl trigger, but nothing seems to pop out.

For now I have done the easy fix, but I would like to know how to block them from accidentally doing this again if possible.


texts:
I'm trying to add a column to a database. The query has been running for 25mins and it's locking web access to the table and breaking our website:

alter table MyTable
add MyNewColumn varchar(max) not null
default ('')

The table contains binary data in a different column and is quite large.

Will cancelling by using the red "Cancel Executing Query" button cause additional problems? I'm just trying to figure out if I should attempt to cancel the query at this point, and what will happen since it's been running for so long.


texts: Should I keep pg_xlog on the same disk as data if using disk snapshotting?

texts:
We're running on EBS volumes on EC2.  We're interested in leveraging
EBS snapshotting for backups.  However, does this mean we'd need to
ensure our pg_xlog is on the same EBS volume as our data?

(I believe) the usual reasoning for separating pg_xlog onto a separate
volume is for performance.  However, if they are on different volumes,
the snapshots may be out of sync.


texts: Postgres replication: Do I need to rsync pg_xlog files if I have archiving setup in master?

texts:
I am trying to replicate a master to slave, without shutting down my master.

In the tutorial here: http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial, they recommend these steps:

pg_start_backup()
rsync -av --exclude pg_xlog --exclude postgresql.conf --exclude postgresql.pid \
 data/*  :/var/lib/postgresql/data/
pg_stop_backup()
rsync -av data/pg_xlog :/var/lib/postgresql/data/

Steps 1-3 are straightforward. However, If I setup archiving in the master and archiving in my slave, do I need to execute step 4? Will it mess things up? Should I NOT setup archiving until everything is rsync'd and my slave is running?

My master's config file looks like this:

wal_level = hot_standby
archive_mode = on
archive_command = 'rsync -a %p <INSERT SLAVE IP>:/var/lib/pgsql/data/archive/%f'

Likewise, my slave has a recovery.conf with a restore_command.


texts:
Below is my simplified scenario. We have a AdCampaign table with the below columns:

ID   Provider     Campaign         AdFlag
 1   Instagram    Influencer123    1

I have been updating the AdFlag based on a lookup table that contained the list of Providers, like so

update t
set AdFlag = 1
from AdCampaign t
join lookup.AdFlag s on t.provider = s.provider

Now the requirements have changed and the logic is something like this:

set AdFlag = 1 according to:

when Provider = 'instagram'
       and campaign = 'InstagramCampaign'
when Provider = 'instagram'
       and keyword like '%foobar%'

when Provider = 'google'
        and keyword like '%foobar%'

when Provider = 'bing'
        and campaign like '%abc123%'
        and keyword like '%foobar%'

... and so on for about five more providers with slightly different logic

Before this requirement change, it was data-driven and clean. Now, it looks like I'll need to replace my lookup table with a lengthy and harder to read CASE statement.

My question is: is there a better approach?


texts: what is the fastest way to import data into mysql 5.7

texts:
Now I want to import a 10000000+ record table from database A mysql cluster to B mysql cluster,now I am using :
source xx.sql

It is so slow. Now what is the fastest way to import? I want to import like this:

import a new table,like table_new(it should be very fast like copy data,may be 1000000/s)

remame old table to table_legacy

rename table_new to table

4.add index or constaint if needed
is it possible or what is the best way to complete this task?


texts: Log non indexed queries for multiple databases

texts:
I have about 50 MySQL databases, each with more than 20 tables. My Sort_rows, handler_read_rnd_next, and handler_read_first stats are really high.

I can't add indexes manually for every table if not necessary, and could miss some, so I'm looking for a tool or a configuration which could help me to find which rows / requests have missing indexes.

Which tools are you using, or how should I configure MySQL to tell me more about these non indexed queries?


texts: Restore backup fails - not enough disk space

texts:
I have a backup that's about 6Gb. It's a "light" backup of the original (with the log tables purged), which is about 14Gb.

I try to restore the backup on my SQL Express local server. It fails with a message like : System.Data.SqlClient.Error: insufficient disk space. It asks for 227,891,019,776 bytes, which is absolutely crazy, and almost as big as my whole hard drive.

As found on other sites, I tried RESTORE FILELISTONLY FROM DISK = 'backupfile.bak'.

The data file (column Size) is 6,888,226,816 in size, but the log file is 221,006,987,264. The column BackupSizeInBytes returns 6,259,736,576 and 0.

So, if I understand correctly, restore checks that I have enough space to restore the "theorical" size of the log file before proceeding, disregarding the actual log file size ?

How can I bypass that ? It's a bit difficult to get a backup, so if I can resolve my problem without having to return on the production server, it would be great.

Thank you !

Oh BTW, I am on SQL Server 2008 R2 Express.


texts: Linux sqlcmd connecting to Windows Sql Server using kerberos

texts:
I'm pretty sure I am going to have to expand upon this a bit as I am new, and my question is probably not clear.

I have an Active Directory environment that includes a Microsoft SQL Server running Windows Server. The SQL server is using "Windows Authentication Mode." It looks like Microsoft does not support "Windows Authentication Mode" when using sqlcmd from Linux unless it's Azure (correct me if I am wrong), so I am trying to create a service account and then generate a Kerberos ticket for that account. The problem is I am not even sure where to start. I did find some information around how-to create a service account and, maybe, a Kerberos ticket for that. But how do I use this on Linux afterward? If you know some existing guides that I missed on this topic feel free to point me there.

Thanks


texts: Postgresql pg_hba.conf changes are ignored or not loaded

texts:
Using Postgresql 9.3 on Ubuntu 14.04.4 LTS

I've been changing the /etc/postgresql/9.3/main/pg_hba.conf config file madly trying to make a change that will allow me to log in with any user remotely.

My current configuration looks like so:

host all postgres <remote-ip>/32 trust
asdf asdf asdf
1 1
1-1-1-1-

This is, of course, a completely bogus config file.  However, when I run

sudo service postgresql restart
sudo service postgresql reload

Both commands succeed with status [ OK ] - there are no parse errors.  I have also disabled all entries that start with local and yet I am still able to login as the postgres user.

Any attempts to log into the server remotely from the  host
using:

psql -c 'SELECT version()' -h pg -U postgres

Result in:

psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

I am clearly not trying to use password authentication.

EDIT:

Running SHOW hba_file; as the postgres user yeilds

                hba_file
--------------------------------------
 /etc/postgresql/9.3/main/pg_hba.conf
(1 row)

Edit 2:

Checking the logs shows that the parse errors were happening.  I have cleaned out the bogus lines, leaving

host all postgres <remote-ip>/32 trust

I am still getting a password prompt for some reason.  There is nothing in the log after a failed login.


texts:
Today I was trying to restore a database over an already existing database, I simply right clicked the database in SSMS --> Tasks --> Take Offline so I could restore the database.

A small pop up window appeared and showed Query Executing..... for sometime and then threw an error saying Database is in use cannot take it offline. From which I gathered there are some active connections to that database so I tried to execute the following query

USE master
GO
ALTER DATABASE My_DatabaseName
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

Again at this point the SSMS showed Query Executing..... for a sometime and then threw the following error:

Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'My_DatabaseName'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

After this I could not connect to the database through SSMS. and when I tried to Take it offline using SSMS it threw an error saying:

Database is in Transition. Try later .....

At this point I simply could'nt touch the database anything I tried it returned the same error message Database is in Transition.

I got on google read some questions where people had faced similar issue and they recommended to close the SSMS and open it again,  So did I and
Since it was only a dev server I just deleted the database using SSMS and restored on a new database.

My question is what could have possibly caused this ?? and how I can Avoid this to happen in future and if I ever end up in the same situation in future is there any other way of fixing it other then deleting the whole database ???

Thank you


texts: How do you merge older data with an apend only stream database?

texts:
I've been reading about streaming databases that record all events as immutable entries in an append-only log. Then map/reduce jobs or something like a "materialized view" is constructed reading the logs to get the current state.

However, my question is about interfacing with external systems that must be pulled from. How are you supposed to add their data to the append-only database in a way that prevent duplicates and orders the data correctly?

Consider a nightly cron to pull XYZ and insert it into the stream of todays data.


texts:
I'm running percona 5.6 on a new centos 6.4 server. Things seem to work OK. I personally only really use InnoDB tables for my databases, but only the standard 'mysql' table is in MyISAM format.

I checked the msyql-error.log and saw these messages:

2013-11-14 10:10:26 9013 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_2335_0.MYI'; try to repair it
2013-11-14 10:10:26 9013 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.6-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.6.14-rel62.0/storage/myisam/mi_write.c:226
2013-11-14 10:10:27 9013 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_2335_2.MYI'; try to repair it
2013-11-14 10:10:27 9013 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.6-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.6.14-rel62.0/storage/myisam/mi_write.c:226
2013-11-14 13:53:33 9013 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_2335_0.MYI'; try to repair it
2013-11-14 13:53:33 9013 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.6-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.6.14-rel62.0/storage/myisam/mi_write.c:226
2013-11-14 15:51:54 9013 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_2335_0.MYI'; try to repair it
2013-11-14 15:51:54 9013 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.6-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.6.14-rel62.0/storage/myisam/mi_write.c:226
2013-11-14 19:39:09 9013 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_2335_0.MYI'; try to repair it
2013-11-14 19:39:09 9013 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.6-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.6.14-rel62.0/storage/myisam/mi_write.c:226
2013-11-15 00:18:24 9013 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_2335_0.MYI'; try to repair it
2013-11-15 00:18:24 9013 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.6-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.6.14-rel62.0/storage/myisam/mi_write.c:226

This seems to be relating MyISAM tables, which is then the 'mysql' information table.

Any idea what might be causing this?

I checked the /tmp dir, and it has plenty of space available.


texts: Error while configuring REST for Oracle APEX on Windows

texts:
I'm trying to deploy Oracle APEX 18 on Windows 8.1 PC using Oracle REST Data Services and Oracle Database 18c XE following this guide.

But when I'm trying to run @apex_rest_config.sql I get this error - Enter: GetConsoleMode failed, LastError=|6| at Drive_letter:/DB_install_folder/dbhomeXE/perl/site/lib/Term/ReadKey.pm line 334.

In CMD it looks like this:

Every apex_rest_config#.log file looks similar to this:

What could be causing this problem? Why @apex_rest_config.sql doesn't ask new passwords for APEX_LISTENER and APEX_REST_PUBLIC_USER, as it does in the guide?
Does it have something to do with this ORA-03113: end of file on communication channel error? Is there any fix or workaround?

Update 1: trying to implement this solution.


texts: Syncing data between onprem and Azure SQL

texts:
I have the following

Scenario: an on-premise SQL Server 2008 R2 and an app using it as db. I want to create an Azure SQL database that will be synced (asynchronous replication) with the on-premise instance. I do not want to have just a one-time migration like you can get with SQL Database Migration Wizard - (if I understand it correctly).

Goal: I need to have the on-premise instance synced, let's say via daily job that will transfer the data, with my Azure SQL database.

Is it neccessary to setup Azure Data Factory for this? What about Data Sync? I can see that it is not available on the "new portal". Any advice how to approach this in a simple way would be great. Thanks!

EDIT: So, I have looked into the case of using transactional replication as Kin suggests. However it states only versions over SQL Server 2008. Any other ideas how to cope with this? Thanks!


texts: Why does ALTER COLUMN to NOT NULL cause massive log file growth?

texts:
I have a table with 64m rows taking 4.3 GB on disk for its data.

Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text.

I added a a NULLABLE column with data-type Datetimeoffset(0).

I then UPDATED this column for every row and made sure all new inserts place a value in this column.

Once there were no NULL entries I then ran this command to make my new field mandatory:

ALTER TABLE tblCheckResult
ALTER COLUMN [dtoDateTime] [datetimeoffset](0) NOT NULL

The result was a HUGE growth in the transaction log size - from 6GB to over 36GB until it ran out of space!

Does anyone have any idea what on earth SQL Server 2008 R2 is doing for this simple command to result in such huge growth?


texts: #deleted in all fields using ODBC driver between PostgreSQL and Access 2013

texts:
I planning to use MS Access (2013) as a front end to a PostgreSQL database server (version 9.5.2). However, when updating a table in Access the new row appears with “#deleted” in every column until I manually click the “refresh all” button (Home tab). At which point the data appears as entered. Further, all new data saves successfully to the db, but the constant need to refresh will frustrate my users.

My research found that the problem relates to the primary key and the data type – apparently Access is picky about the data type and the presence of an auto-increment field (PG sequence). This post is helpful, but changing the data type to integer or varchar does not fix the problem (indeed, I have tried integer, big integer, varchar, etc.). This post from MS is not helpful, but provides some insight into the root of the problem.

Here are the steps taken:

I installed the most recent ODBC driver from here (version: 09_05_0300)
Set up ODBC connection as a data source (using Unicode x64)
Create a simple table with an auto-increment (i.e. bigserial) primary key. For example:

CREATE TABLE test.user (
user_id bigserial,
username varchar(300),
firstname varchar(300),
lastname varchar(300),
CONSTRAINT user_pk PRIMARY KEY (user_id)
);
Open linked ODBC table in Access.
Enter new row of data save.
"#deleted" appears where data was just entered.
Press refresh and entered data appears. All data are saved to DB.

Does anyone know how to fix this problem and eliminate the need to continually refresh the linked ODBC table?


texts: How can I control the order of a query regarding to inner join

texts:
I have 3 tables.

They have

table_a 5m records

table_b 20m records

table_c 20m records

I'm performing the query below.

SELECT * FROM table_a a
INNER JOIN table_b b ON a.a_id = b.a_id AND b.b_come='100'
INNER JOIN table_c c ON b.c_id = c.c_id AND c.c_type IN ('A','B')
WHERE date(a.a_dt) = '01/01/2012 00:00:00.000'

The query is quite slow. I think it is because the data in its nature they have

about 1000 records of a.a_id = b.a_id

and

about 20m*20m records of b.c_id = c.c_id.

I think I should get the result of a.a_id = b.a_id then do the one of b.c_id = c.c_id so that the performance could be a lot better. But it seems INNER JOIN has no concept of order. How should I do this to improve the performance?


texts:
I have multiple users in Postgres. I would like to set up different statement timeouts for different users.
Eg: Guest 5 minutes and Admin 10 minutes.
Is it possible in Postgres 11.11?


texts: denydatawriter does not work in SQL Server

texts:
I have a couple of admin roles in a SQL Server 2012.

To prevent myself from changing data in one database, I added my login as a member of denydatawriter role to that database.

It seems does not work, still able to insert

Did I miss anything?


texts:
I was trying to capture blocking query using blocked process report past 1 hour as this link
https://www.mssqltips.com/sqlservertip/4480/find-blocking-processes-using-sql-server-profiler/

I can get the blocked process query using sqlhandle but for blocking process query i cannot get it because no sqlhandle and inputbuf doesnt have any SQL statement.

Any idea? This is first time i m using this blocked process report.


texts: What is causing all of the msrepl_tran_version columns to become out-of-sync between locations in transactional replication setup?

texts:
I have been having a recurring issue with transactional replication setups at the firm I work for. We use SQL 2008 R2. Seemingly without warning or any replication errors, all of the msrepl_tran_version columns (or nearly all of them) get out-of-sync between the two locations in a given setup.

I look in replication monitor and don't see any errors, and it's not as though the data changed for all the rows. Instead, most of the data remains identical between the two instances, but as users make changes at one location they don't update at the other due to the msrepl_tran_version columns being out-of-sync.

This issue is extremely frustrating, because I can't tell what's causing it and it requires substantial resynchronization to fix. Does anyone know what causes this symptom? Otherwise, does anyone have any ideas on how I can look to diagnose the issue? I would appreciate any advice anyone can provide.

Thanks.


texts:
How can I find the definitions of below system functions in SQL Server:

has_access
permission_name

I tried with DAC connection but getting below error:

Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54 [Batch Start Line 118]
The object 'has_access' does not exist in database 'master' or is invalid for this operation.

Please let me know if any further details are required. Thanks!


texts: Distributing a semi-large key/value list

texts:
I have a semi-large (around 30 million records) key/value list which I want to load into redis.

The data is stored in PostgreSQL (as master data) and we want to distribute this to clients for faster (local) lookups.

The keys are Belgium telephone numbers (national format, so strings between 9 and 10 digits). The values are 5 letter strings.

There would be a redis-server on each client (around 10 clients per customer). The full dump will distributed once or maybe twice a year, while the updates are between 100-1000 a day (only business days between 08:00 and 18:00).

Is this an efficient way to do this?

Side remark: if redis isn't the correct choice, other suggestions are also welcome.


texts: MySQL 5.5 vs 5.6,5.7 above same database same query but different output, limit have duplicated ID

texts:
I am using Running same source code and same database for WordPress, only the mysql version is different. It make my pagination post have duplication.
Here is what I found out. Any idea how to solve it in higher version?

This is for Page 2, and Page 3 query output. Left-Hand side is MySql 5.5, Right-Hand side is MySql 5.6.  up_posts.ID is unique.

SQL Statement:-

SELECT SQL_CALC_FOUND_ROWS up_posts.ID FROM up_posts  WHERE 1=1  AND up_posts.post_type = 'post' AND (up_posts.post_status = 'publish' OR up_posts.post_status = 'closed' OR up_posts.post_status = 'private' OR up_posts.post_status = 'hidden')  ORDER BY up_posts.post_date DESC LIMIT 10, 10

SELECT SQL_CALC_FOUND_ROWS up_posts.ID FROM up_posts  WHERE 1=1  AND up_posts.post_type = 'post' AND (up_posts.post_status = 'publish' OR up_posts.post_status = 'closed' OR up_posts.post_status = 'private' OR up_posts.post_status = 'hidden')  ORDER BY up_posts.post_date DESC LIMIT 20, 10

Page 2:- different is LIMIT 10,10

Page 3:- different is LIMIT 20,10

Seem like MySQL 5.5 will sort by the ID, but 5.6 above does not have this ability.


texts: How to create SOAP endpoint in SQL Server 2016?

texts:
When I'm trying to create a SOAP endpoint in SQL Server 2016:

CREATE ENDPOINT Weather_CurConditions
STATE = Started
AS HTTP
(
    PATH = '/CurrentConditions',
    AUTHENTICATION = (INTEGRATED),
    PORTS = (CLEAR), CLEAR_PORT = 8080,
    SITE = '*'
)
FOR SOAP
(
    WEBMETHOD 'GetWeather'(NAME = 'Weather.dbo.usp_GetWeather'),
    WSDL = DEFAULT,
    DATABASE = 'Weather',
    NAMESPACE = DEFAULT
);

I get the following error:

  Msg 7878, Level 16, State 1, Line 1
  This "CREATE ENDPOINT" statement is not supported on this edition of SQL Server.

Here is the output of SELECT @@VERSION:

  Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58   Copyright (c) Microsoft Corporation   Enterprise Edition (64-bit) on Windows 8.1 Enterprise 6.3  (Build 9600: )

What can I do ?


texts: How to display all entries that have had a box ticked since a particlar date

texts:
Good afternoon,

I would be very grateful is someone would be able to offer some assistance to a query I have regarding some SQL I'm needing to implement.

In our database we have a range of customers that have specific properties/category tick boxes associated with them. We're wanting to display a list of all customers that have a certain tick box selected/ticked since a set date. This date is 05-07-17. Would this be possible? Kind of lost as how to display this. Se screenshot below..

Kind regards,
Chris


texts: Microsoft SQL Server 2016 Restore command many files

texts:
I have a question regarding proper syntax for restoring a 2016 SQL Server database.
One database 1 mdf no issue. But I need to restore a database that has 3 mdf files and 73 ndf files and then apply 2 differential back ups.
RESTORE DATABASE [Database]
FROM DISK = N'E:\Data\Server_Database_20210213221000_full_1_of_2.bak',
     DISK = N'E:\Data\Server_Database_20210213221000_full_2_of_3.bak'
     DISK = N'E:\Data\Server_Database_20210213221000_full_3_of_3.bak'
WITH FILE = 1, REPLACE,
MOVE 'Database' TO 'E:\Data\Database.mdf',
MOVE 'FactPart00' TO 'E:\Data1\FactPart\FactDatePart00.ndf',
MOVE 'FactPart01' TO 'E:\Data1\FactPart\FactDatePart01.ndf',
MOVE 'FactPart02' TO 'E:\Data1\FactPart\FactDatePart02.ndf',
... all the way to 73

The above part works my question is now once how do I then restore diff file
File Name
Server_Database_DIFF_20210214_220209.bak

To restore the diff will I also need to reference all the ndf files as well or just restore. What should my syntax look like? I don't have an error since I have not ran it as I am nervous about corrupting database.


texts: SQL query fills up RDS free storage space after upgrade to MySQL 5.7

texts:
After the upgrade to MySQL 5.7 the execution of a SQL query fills up the free storage space.

The upgrade was performed through the AWS console, selecting the automatic procedure to upgrade the RDS from MySQL 5.6.27 to MySQL 5.7.11. However, the same query that runs smoothly on mysql5.6 exhaust the available file system space on the mysql5.7 instance.

Researching on the problem, there was identified that the /rdsdbdata/db/innodb/ibtmp1 file was extended up to use all the free storage space. The behaviour is shown on the figures.

An additional comparison of the execution plan between mysql5.6 and mysql5.7 shows differences in the inclusion of 256 million records for 5.7, even after the optimizer_switch parameter was aligned between the two database versions.

Some evidence exhibits a relation to with utilisation of user defined variables, but it is not conclusive. The SELECT statement, for example,  includes a @count := @count + 1 attribute.

Question: How to mitigate the fact that MySQL 5.7 changed the execution plan therefore filling up the available storage space of the RDS instance.


texts: What are the requirements for SQL Replication on Azure SQL DB Managed Instance?

texts:
We are seeing if it is feasible to setup Azure SQL DB Managed Instance (SQL MI) and an Azure VM with SSMS installed for this replication scenario:

We have a vendor who needs access to our SQL Server database and can RDP into it for administration. We will be using ExpressRoute to link SQL MI to our on-prem. It seems the only way to connect to SQL MI is either using site-to-site VPN (vNet) or ExpressRoute. Our vendor needs to setup transactional replication to push data to our SQL MI which will be a subscriber. This link Replication with sql managed instance is the only guide I see but has no details.

Does the vendor need a vNet/ExpressRoute connection to setup replication to our database? Or can they setup replication using our VM with SSMS client tools that is in our SQL MI vNet?
If that is not possible, then I guess we need to setup the Azure VM with SQL Server and they connect to that instance to replicate. Then we need to replicate that db to the Azure SQL DB MI. This seems complicated and wonder what other options are there.


texts: MSX Job deployment: Not creating job on certain servers

texts:
I am deploying a new index maintenance jobs through MSX to about 100 servers, I am in the process of manually checking that each job has been created and I have already discovered that for some currently unknown reason specific servers are not receiving the new MSX job.

Strangely, these servers have been able to successfully disable an older index maintenance job that already exists.

I am wondering if someone can help me identify the reason for this.

On my CMS I have the Server Agent assigned the TargetServerRole in MSDB.

I have noticed in my agent error log, the above information.

Edit: I have since identified that the servers needed resynchronisation, I defected the target (screen capturing existing MSX jobs before doing so), and then rejoined to the master and re-added the MSX jobs - this seemed to resolve the issue.


texts: Update all data in a table without affecting ID(PK)

texts:
I'm using SQL Server 2005.

I have a table with 4846 customer records. The PK is called ID and spans from record 40003 to 79870.
This numbers are used a other tables to hold info about user orders, for example.
Within the table there's a decimal field that stores the amount of credit each user is entitled (CustomerCredit).

Now, the problem is I cant manage to come a up to a way of updating all of the CustomerCredit records without affecting the ID field... The only information that needs to change is the credit amount per each user.

Any clue will be more than appreciated.


texts: how the mysql server settings replicatd in innodb cluster

texts:
I just started learning about cluster so I don't have much knowledge if question is not appropriate please forgive me.

I have a 3 node InnoDB cluster running .suppose if I want to tune my server by changing the value of the innodb_buffer_pool_size variable or let say I want to add a new variable in configuration file do I need to change the my.cnf file of every server or is there any simple way I can achieve this.

and how do I change the server system variable so that the setting will be replicated to other servers too?


texts:
Is there some full list of commands that can cause table to become reorg-pending?

I tried google it for a while, but can't find full list. My exact problem for now - can ADD FOREIGN KEY statement cause table to become reorg-pending?


texts: Partition By A Column When The Value Isn't Continuous From One Record To The Next

texts:
How can I Partition By the PreviousScanIndex column when the value isn't continuous from one record to the next (ordered by the RowIndex column), so that I can rank each continuous group of records with the same ID?

E.g. I want to give the group of rows in red the same ID, and then the group of rows in green to have the next ID, and then the group of rows in blue the next ID after that, etc.


texts: Oracle 11gR2 expdp table converted to CSV from Linux

texts:
With Oracle expdp,  I can export the tables I want with

expdp INCLUDE=TABLE:table1,table2,table3

I need a way to convert this into CSV on a linux machine. I've seen Windows tools but don't know how good they are and this needs to work from linux command line/ bash script.

Is this possible?