texts: How to update primary key table based on foreign key with ON DUPLICATE KEY UPDATE

texts:
I have a users table with autoincrement id as primary key, and unique key on email. The email and name fields are from facebook.

+----+-------+------+
| id | email | name |
+----+-------+------+

And a facebook users table,

+--------+----------+
| userId | fbUserId |
+--------+----------+

The userId in the facebook table will be a foreign key value, referencing id in the users table. The fbUserId is guaranteed to be unique by facebook. The reason I split these tables up is I plan to have more social logins in the future, and I'm trying make my schema future proof. Each additional table would also have a userId, linking it back to users.

Right now my insert update query is this

BEGIN;
    INSERT IGNORE INTO users
    (id,email,name)
    VALUES (0,?,?);

    INSERT IGNORE INTO users_facebook
    (userId, fbUserId)
    VALUES (LAST_INSERT_ID(), ?);
COMMIT

From what i understand, ON DUPLICATE KEY UPDATE only applies to a single row in a single table. What i'd like to do is if email or name is different for an existing fbUserId, i'd like to update the changed fields. How would i do this?

Also, any thoughts on my current query? I'm quite new to mysql.


texts:
I have a database called data that is going to contain some data about writers, books and images. I've created these tables so far, but I don't know how to struct the images table. A writer or book can have more than one images, so I thought I could solve this issue by creating a table called img and attach to other tables. Is that a correct solution? If it's not, how can it be improved? I use innodb as a storage engine.

Table name : Book
book_id (Primary Key)
book_name
writer_id (Foreign Key)

Table name : Writers
writer_id (Primary Key)
writer_name
writer_bio

Table name : Images
image_id (Foreign Key for all tables that need to hold an image including book,writers, etc.)
image_url

I have an additional question. I started adding data into my tables, but didn't create the relationships yet. Would it be a problem when I create the relationships? Will the indexes be created automatically?


texts: Update only the oldest entry in a table with foreign key criteria

texts:
I'm working on a forum website.

What I am trying to do is updating the thread Title and Content (first post), which resides in different tables - "Threads" and "Posts".

"Threads"

"Posts"

I'm not really sure how to update only the oldest entry in "Posts" where the foreign key in "Posts" match the Id in Threads.

I have never really tried advanced UPDATE statements with subqueries, and it isn't one of my strongest skills yet.

This is what i have tried (which updated ALL rows in "Posts"-table):

    UPDATE Threads
    SET Title = @ThreadTitle
    WHERE Id = @Id;

    UPDATE Posts
    SET Content = @Content
    WHERE
        (SELECT TOP 1 FkThreadId FROM Posts
         WHERE FkThreadId = @Id
         ORDER BY CreationTime ASC)= @Id

Also is it possible to update joined table instead of using 2 statements, or is it just better to write 2 seperate statements?

Version:

  Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)


texts:
Is there anything in Postgresql  9.1 similar to wait events dynamic views of oracle?
I need to find queries which are waiting for a long time, and the events they are blocked on.


texts: Finding all joins required to programmatically join a table

texts:
Given a SourceTable and a TargetTable, I would like to programmatically create a string with all joins required.

In short, I am trying to find a way to create a string like this:

FROM SourceTable t
JOIN IntermediateTable t1 on t1.keycolumn = t.keycolumn
JOIN TargetTable t2 on t2.keycolumn = t1.keycolumn

I have a query that returns all foreign keys for a given table, but am running into limitations in trying to run through all this recursively to find the optimal join path and make the string.

SELECT
    p.name AS ParentTable
    ,pc.name AS ParentColumn
    ,r.name AS ChildTable
    ,rc.name AS ChildColumn
FROM sys.foreign_key_columns fk
JOIN sys.columns pc ON pc.object_id = fk.parent_object_id AND pc.column_id = fk.parent_column_id
JOIN sys.columns rc ON rc.object_id = fk.referenced_object_id AND rc.column_id = fk.referenced_column_id
JOIN sys.tables p ON p.object_id = fk.parent_object_id
JOIN sys.tables r ON r.object_id = fk.referenced_object_id
WHERE fk.parent_object_id = OBJECT_ID('aTable')
ORDER BY ChildTable, fk.referenced_column_id

I am sure this has been done before, but I can't seem to find an example.


texts: How To Grant A Specific Login Under Acitve Directory Group To View Server State In SQL Server

texts: I want to grant only a login who is under an active directory user group to view server state in sql server. How can i do it?


texts:
How can I go about exporting DDL of all my schema views in single .sql file ?
I've read there is a function DBMS_METADATA.GET_DDL   But I can't figure out how to use it for my case.

I know how to do this for one view (Oracle Entreprise Manager->MySchema->Views->Selecting my view->show object DDL->Save..)

I'm using SQL*Plus


texts:
On MySql, in order to truncate a table or delete rows when it's normally impossible because of foreign keys (InnoDB only), we use this command:

SET FOREIGN_KEY_CHECKS=0;

On MariaDB, while this command is accepted, it does nothing.

The documentation says I have to run these commands instead:

On a per-table basis:

ALTER TABLE `...` DISABLE KEYS;

or, globally:

SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;

So I tried to run this script:

SET FOREIGN_KEY_CHECKS=0;
SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;
ALTER TABLE `country` DISABLE KEYS;
DELETE FROM `country` WHERE 1;

And that leads me to:

  Cannot delete or update a parent row: a foreign key constraint fails (database.region, CONSTRAINT FK_F62F176F92F3E70 FOREIGN KEY (country_id) REFERENCES country (id))

Using TRUNCATE has the same effect.

If you have any idea on what I may have missed, thank you by advance. Because the documentation and the existing questions weren't much of help here.


texts: Is it better to have multiple tables with the same primary key or one big data table?

texts:
So there is one primary key so far used for IDs of locations.
Currently there are multiple tables (sheets) in Excel for the following

Events (Location ID, Date, Title, Description)
Animals (Location ID, Species, Name, Population)
Restrictions (Location ID, Permits, Zone)

Is the above bad practice? From what I have read online you should not use the same primary key across multiple tables.
How should the tables be structured to follow "best practices", have it be one big database table or is it fine to leave in multiple tables?


texts: Trigger to prevent update when Flag value is 1

texts:
I need help with a trigger to prevent update on any column except for column Flag (it can be updated) when Flag value is 1 on the below table:

CREATE TABLE Tmp (Id INT, Type INT, TranDate DATE, Flag INT)
INSERT INTO Tmp VALUES (1, 2, '2017-04-24', 0)
INSERT INTO Tmp VALUES (2, 4, '2017-04-27', 1)

I've tried with the below code but doesn't work correctly.

CREATE TRIGGER [dbo].[Prevent_Update_Trigger]
ON [dbo].[Tmp]
FOR UPDATE
AS
SET NOCOUNT ON
IF  EXISTS(SELECT NULL FROM dbo.Tmp t JOIN inserted i ON t.Id=i.Id AND t.Flag=1)
BEGIN
RAISERROR('You can not update when Flag value is 1', 16, 1)
ROLLBACK TRAN
SET NOCOUNT OFF
RETURN
END
SET NOCOUNT OFF


texts:
Possible Duplicate:
  Convert varchar column to datetime

Hi I am using the SQL server Import wizard to import a CSV file into what will be a new table in my database.

An example of the CSV file is:

66.29.193.125 ,-,  -,  07/May/2012:00:00:00, 0 , "GET / " , 200, 123, "-", "-"

Everything imports ok when the default varchar type is used for all columns. However, the 4th entry in is clearly a datetime and I want to import it as a datetime. anytime I try, I get:

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Column 3" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
 (SQL Server Import and Export Wizard)

I get these for the various datetime types. Any tips?
Many thanks.


texts: How does a Page in SQL Server distinguishes between the data of different columns of a table?

texts: I know that row offset array at the end of page stores information about the starting index of any row. Does it also store information about column data? And where does it store? If not, how does it identify the different column data?


texts:
I have a server that is running SQL Server 2005 Express.

It currently has 2 instances of SQL Server running.

Instance 1 runs some old legacy software that is not supported by me.
Instance 2 runs my software.

Is it possible to upgrade instance 2 to SQL Server 2008 R2 Express independently of instance 1, i.e. keeping instance 1 on 2005 while instance 2 is upgraded?


texts: Covering index used despite missing column

texts:
I have the following query, using MariaDB 10 / InnoDB:

SELECT id, sender_id, receiver_id, thread_id, date_created, content
FROM user_message
WHERE thread_id = 12345
  AND placeholder = FALSE
ORDER BY date_created DESC
LIMIT 20

This query fetches messages according to the given conditions and sorts by date created.

I have a covering index over (thread_id, date_created).

When running EXPLAIN, the correct index is used and I get the output "Using where", although the query is using a column in the middle of the statement that is not in the index. I can use any value for "placeholder = x" and the result is the same.

If I change the sorting to use another column, the EXPLAIN correctly indicates "Using where. Using filesort."

I'm having a head-scratching moment. Could anyone please shed light on this? What I would expect to see is that an additional filesort would be needed as the covering index could not be completely used due to the additional column.


texts: How else to troubleshoot LCK_M_U from SQL Server?

texts:
We have an INSERT statement coming from an Entity Framework application that has been causing pretty large blocking chains. The Wait Time and Wait Type in our monitoring tools for that process is empty, so it doesn't look like it's waiting on anything. This makes me believe that the application is opening a transaction, doing an insert and just not committing it.

This has been an intermittent issue so it's been hard to troubleshoot. Development team is looking into the code but insistent that nothing has changed. What else can I look at on the SQL Server side?

SQL Server 2012 SP2


texts: If A -> B is augmented with C, is AC -> B legal?

texts:
Given the augmentation axiom and the decomposition axioms of functional dependencies and a relation of R(A,B,C) with an FD of:
A -> B

I can augment A -> B with C to get AC -> BC.

Can I then decompose AC -> BC to get AC -> B?


texts: Warning message: invalid value for parameter "default_tablespace" in postgres

texts:
I create a new tablespace using the following script:
CREATE SCHEMA semiarido;

CREATE ROLE semiarido PASSWORD 'senha';
GRANT CREATE ON SCHEMA semiarido TO semiarido;
GRANT USAGE ON SCHEMA semiarido TO semiarido;
alter role semiarido set default_tablespace = semiarido;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA semiarido TO semiarido;
ALTER ROLE "semiarido" WITH LOGIN;

When I connect to the database using psql or a python script, I get the following warning:
WARNING:  invalid value for parameter "default_tablespace": "semiarido"
DETAIL:  Tablespace "semiarido" does not exist.
However, it works, that is, the user is connected to the correct database and see "semiarido" as the default database.
So what's the problem? Why I'm getting this warning?


texts: Is it possible to use SQLite as a client-server database?

texts: Are there any techniques or tools to work with SQLite on a medium size/traffic/concurrency DB environment?


texts: How do I resolve Postgresql error, 'no collation was derived for column "foo" with collatable type citext'?

texts:
Just recently I upgraded from Postgresql 8.4.11 to 9.1.3. I used pg_dump in the process. Now I'm getting an error:

  ERROR:  no collation was derived for column "vin" with collatable type citext
  HINT:  Use the COLLATE clause to set the collation explicitly.

This is a continuation of the problem that I had earlier. It seems the fix suggested in the Release Notes did not catch custom domains. It seems as if the CREATE DOMAIN statement in 8.4 didn't even support the COLLATE clause.

This is how I created the vin type,

CREATE DOMAIN inventory.valid_vin AS citext
  CHECK ( inventory.valid_vin( VALUE ) );


texts: Postgres initialization of pg_basebackup

texts:
Hi Im running on a centos 7 machine, Im in the process of migrating an old postgres database to the new server, it has like 10TB of data so Im trying it with pg_basebackup. The new server has an independent disk with enough storage to handle the operation, but it's not the primary disk. I would like to set the PGDATA into this big data disk.

I have followed this steps:

Install postgres-12 and postgres12-contrib
Mount the data-disk into the new machine
Create an empty pgdata directory in the data disk. All with user postgres.
Use /usr/bin/pg_basebackup -h IP_OF_OLD_MACHINE -D /mnt/disks/data-disk/pgdata -P -U USERNAME --wal-method=stream

I think pg_basebackup ended successfully, data it took several hours but finished without prompting any error at all.

total 132K
-rw-------. 1 postgres postgres  230 Mar 18 12:36 backup_label
drwx------. 7 postgres postgres 4.0K Mar 18 12:43 base
-rw-------. 1 postgres postgres   30 Mar 19 00:13 current_logfiles
drwx------. 2 postgres postgres 4.0K Mar 19 00:13 global
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 log
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_commit_ts
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_dynshmem
-rw-------. 1 postgres postgres 4.8K Mar 18 12:36 pg_hba.conf
-rw-------. 1 postgres postgres 4.7K Mar 18 12:36 pg_hba.conf~
-rw-------. 1 postgres postgres 1.6K Mar 19 00:13 pg_ident.conf
drwx------. 4 postgres postgres 4.0K Mar 18 12:36 pg_logical
drwx------. 4 postgres postgres 4.0K Mar 18 12:36 pg_multixact
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_notify
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_replslot
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_serial
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_snapshots
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_stat
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_stat_tmp
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_subtrans
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_tblspc
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_twophase
-rw-------. 1 postgres postgres    3 Mar 19 08:27 PG_VERSION
drwx------. 3 postgres postgres 4.0K Mar 18 12:36 pg_wal
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_xact
-rw-------. 1 postgres postgres   88 Mar 18 12:36 postgresql.auto.conf
-rw-------. 1 postgres postgres  24K Mar 19 08:39 postgresql.conf

Ok, now im trying to initialize a postgres server with this data, the one i've just brought.

This are my attempts, looking the documentation:

/usr/pgsql-12/bin/postgresql-12-setup initdb -D /mnt/disks/data-disk/pgdata
systemctl: invalid option -- 'D'
failed to find PGDATA setting in -D.service

/usr/pgsql-12/bin/postgresql-12-setup initdb --pgdata=/mnt/disks/data-disk/pgdata
systemctl: unrecognized option '--pgdata=/mnt/disks/data-disk/pgdata.service'
failed to find PGDATA setting in --pgdata=/mnt/disks/data-disk/pgdata.service

Is there some mistake in the process?

Update:
As @pifor told me i added the PATH to the postgres bash_profile and then started to recognize PG_DATA. The problem now is that initdb aims to find an empty folder, and its not empty due to the pg_basebackup


texts: Select from a table if conditions met in joined table

texts:
I have two tables

members

+-----------+
|id   | name|
+-----------+-
| 1   | john |
| 2   | adam |
| 3   | sara |
+-----------+-

keys

+-----------+
|id   | color|
+-----------+-
| 1   | blue |
| 2   | green|
| 3   | red  |
+-----------+-

and a third joined table

members_keys

+-----------+---------
| member_id | key_id |
+-----------+---------
| 1         | 1      |
| 1         | 3      |
| 2         | 1      |
+-----------+--------

both rows are assigned as primary key and foreign key

What I need is to get john's keys (keys 1 and 3 in this example)

This is what I am doing

select * from keys join members_keys on keys.id = members_keys.key_id where members_keys.member_id = 1;

from my limited testing I got the required rows, but I feel there is a better way to do it and this solution looks like wasting a lot of resources (comparing each row in keys with all rows in members_keys)


texts:
What useful information can I get from ASH report which I cannot get from AWR report?
What are the common scenarios in which its better to get information from ASH than from AWR?


texts:
I have a database architecture with:
=> 1 distributor running on a dedicated instance of SQL Server 2005
=> 2 publishers running each on a dedicated instance of SQL Server 2000 and SQL Server 2005
=> A dozen subscriber running SQL Server Express / Sql Server 2000

I need to move the distributor instance to an other server with an other IP address, and I would like to have to re-publish-synchronise the whole architecture.
How can I can Backup / Restore the distributor on a new server and have the publishers and subscribers connect to its new ip address?


texts: GUID vs Auto Increment Insert performance

texts:
I'm testing the performance of inserts between Auto increment integers vs GUIDs (v4) in tables with a lot of data. Following this blog post, i was expecting seeing a difference. But right now with more than 6 millions rows, i don't see any difference. These are the definitions of my tables:

Auto Increment

CREATE TABLE `auto` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`))

GUID

CREATE TABLE `guid` (
`id` CHAR(32) NOT NULL,
PRIMARY KEY (`id`))

The code is the same as the referenced blog post, insert 100K rows in one transaction several times. As I said before with more than 6 millions rows, there is no difference in performance, it is practically the same. I am trying to figure out why. The script is in a C# y and the GUID is being generating in the application (not in MySql). To be more explicit what i'm doing is a for loop from 1 to 1M, send inserts to the DB, and each time i reach 100K rows commit the transaction and measure the elapsed time on committing.

My hardware: Mac mini i5 2.3 GHz, 16GB Ram, 960GB SSD, but the script is running on a Fusion virtual machine with 4GB of Ram with Windows 10 x64, MySql server it is installed on the virtual machine.

MySql version: 5.7

So, Am I missing something?. Do I need more data?.

Thanks in advance.


texts: Troubles inserting new records in linked SQL-server table in Access

texts:
Years ago, I upsized the back-end database from Access to SQL-server using the build in tool in Access.

It worked fine but in the recent two weeks an error began to appear.
This problem occurs for several tables.

A table is opened in Access to add a new record. Then at the new empty record at the bottom of the table, a value is entered to a field. If the record above is selected the content of the row of the new record immediately change to the content of an old record.

See a screen capture here: http://idea2action.dk/images/Access_trouble.mp4
First column is an identity autonumbering field. In the screen capture you will see Access fetching an old record.

This goes for Access 2010 as well as Access 2016 as front-end application. I also switched the ODBC driver to another version without doing any difference.

The SQL server where this problem appeared is version 12. I could recreate this problem, by upsizing the original Access-backend to another version 14 SQL server. I then created a new version of the table using script table tool in SQL server management studio. I removed all extended properties from the script before creating the new version of the table. Then I inserted all records from the old table into the new table. I swapped the old and new table and the problem was gone in the Access front-end.

In the script below I treat a table named T_Proeve_ImpMill:

CREATE TABLE [dbo].[T_Proeve_ImpMill1](
    [Prøvenr] [int] IDENTITY(1,1) NOT NULL,
    [Tidspunkt] [datetime] NULL,
    [Sign] [nvarchar](50) NULL,
    [RKV] [real] NULL,
 CONSTRAINT [T_Proeve_ImpMill_PK] PRIMARY KEY NONCLUSTERED
(
    [Prøvenr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

set IDENTITY_INSERT [T_Proeve_ImpMill1] on;

insert into [T_Proeve_ImpMill1]
(
[Prøvenr]
      ,[Tidspunkt]
      ,[Sign]
      ,[RKV]
)
SELECT [Prøvenr]
      ,[Tidspunkt]
      ,[Sign]
      ,[RKV]
  FROM [dbo].[T_Proeve_ImpMill]
GO

exec sp_rename [T_Proeve_ImpMill],[T_Proeve_ImpMillOld]
exec sp_rename [T_Proeve_ImpMill1],[T_Proeve_ImpMill]

set IDENTITY_INSERT [T_Proeve_ImpMill] off;

Happily, I implemented this solution on my customers server. The day after the customer reported, that the problem still exists.

Has anybody observed similar problem? Are there any explanations for this behavior? How should I fix it?


texts: Finding distinct rows across two tables: Full Outer Join more efficient than Union?

texts:
When finding distinct rows across two tables where we can't necessarily ensure are pre-sorted, is it a good idea to use a FULL OUTER JOIN rather than a UNION?  Are there any downsides to this approach?  If it is consistently faster, why does the query optimizer not choose the same plan for UNION that the FULL OUTER JOIN would use?

We were able to bring a specific production query from ~10 minutes to ~3 minutes by re-writing a UNION as a FULL OUTER JOIN.  A UNION seems like the more intuitive way to write the logic, but upon exploring both options, I have observed that the FULL OUTER JOIN is more efficient in terms of both memory and CPU usage.

See the following scripts if you'd like to run a simplified and anonymized version of our production query:

Setup script

-- Create a 500K row table
SELECT TOP 500000 ROW_NUMBER() OVER (ORDER BY NEWID()) AS id, v1.number % 5 AS val
INTO #t1
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

-- Create a 5MM row table that will match some, but not all, of the 500K row table
SELECT TOP 5000000 ROW_NUMBER() OVER (ORDER BY NEWID()) AS id, v1.number % 5 AS val
INTO #t2
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

-- Optionally, key both tables to see the impact it has on query plans and performance
-- Both queries end up with essentially the same plan and performance in this case
-- So that means that at least there is not a downside to using the FULL OUTER JOIN when the data is sorted
--ALTER TABLE #t1
--ADD UNIQUE CLUSTERED (id)
--ALTER TABLE #t2
--ADD UNIQUE CLUSTERED (id)

FULL OUTER JOIN

The FULL OUTER JOIN chooses the small of the two tables as the build side of a hash join, meaning that the memory usage is proportional to the size of the smaller table (500K rows).

-- CPU time = 3058 ms,  elapsed time = 783 ms.
-- MaxUsedMemory: 29016 KB
-- Table '#t1'. Scan count 5, logical reads 1301, physical reads 0
-- Table '#t2'. Scan count 5, logical reads 12989, physical reads 0
SELECT COUNT(*), AVG(id), AVG(val)
FROM (
    SELECT COALESCE(t1.id, t2.id) AS id, COALESCE(t1.val, t2.val) AS val
    FROM #t1 t1
    FULL OUTER JOIN #t2 t2
        ON t2.id = t1.id
        AND t2.val = t1.val
) x
GO

UNION

The UNION builds a hash table for a hash aggregate on the overall data set, meaning that the memory usage is proportional to the total number of distinct rows (5.4MM rows in this case; generally, at least the number of rows in the larger of the two tables).  The memory usage is over 10x greater than the FULL OUTER JOIN, and both CPU time and elapsed time are slower as well.  If I were to scale this up to the point that hash aggregate can't fit within a single query's memory grant, the performance difference would become huge (as it was in our large production query).

-- CPU time = 4651 ms,  elapsed time = 1188 ms.
-- MaxUsedMemory: 301600 KB
-- Table '#t1'. Scan count 5, logical reads 1301, physical reads 0
-- Table '#t2'. Scan count 5, logical reads 12989, physical reads 0
SELECT COUNT(*), AVG(id), AVG(val)
FROM (
    SELECT t1.id, t1.val
    FROM #t1 t1
    UNION
    SELECT t2.id, t2.val
    FROM #t2 t2
) x


texts: Mysql could not be started because unknown variable 'log-syslog=1'

texts:
Platform System： WSL（ubuntu 18.04）.

when installed mysql-server 8.0 sucess， i try to start mysql. But it's fail to start.Then checked the error.log.

2019-02-26T03:13:51.609895Z 0 [ERROR] [MY-000067] [Server] unknown variable 'log-syslog=1'.

how to fix it ?


texts: creating a trigger after update exclude a column?

texts:
I want to create a trigger that set a column to to_sync true if the other columns has been changed.

CREATE TRIGGER [dbo].[product_trig]
  ON dbo.product
  AFTER UPDATE
    SET NOCOUNT ON;
    IF NOT UPDATE (to_sync)
      BEGIN
          UPDATE dbo.product
              SET  to_sync  = 'TRUE'
          FROM dbo.product INNER JOIN INSERTED i ON dbo.product.NAME = i.NAME
      END
END

the above code  raise an error:

Msg 156, Level 15, State 1, Procedure mat_trig, Line 4
Incorrect syntax near the keyword 'SET'.
Msg 140, Level 15, State 1, Line 1
Can only use IF UPDATE within a CREATE TRIGGER statement.
Time: 0.504s


texts: SQL Data types and SmallInt/TinyInt vs Regular Integer

texts:
What are the SQL Server benefits of using proper data types?

Example:

SmallInt/TinyInt vs Regular Integer
Decimal(2) vs float
Varchar(100) vs char(100)

Will it really matter these days, with modern computing?

Thanks,


texts:
I'm developing a software solution that requires storing raw data points originating from many PLCs (programmable logic controller, used to control industrial machines such as conveyor belts and robots) into a SQL Server database. My main concern is how to properly store large amounts of raw numeric data.

Each data point has the following properties:

Datetime stamped
May be a: boolean, integer, floating point, or a string
A data point may be stored for each second all year (~31,536,000 data points per year)

Each PLC I'm collecting data from may have as many as 500 data points, and I want to plan on supporting thousands of PLCs. Meaning, I'll have at least 15,768,000,000,000 data points per year (31,536,000 points per year * 500 points * 1000 plcs). I currently have a table for each data type (bool, int, float, string). In an effort to reduce the number of records and storage, I am storing 4 data points per record [ID, DateTime, DataGroupID, Value1, Value2, Value3, Value4].

I have this implemented, and it's working well (fast queries with my largest table at ~72 million records) though it is isn't very flexible (Changing data types requires moving data from table to table, regrouping data requires table/column changes, etc.)

So, my thought to improve flexibility is to keep the data type specific tables, but to simply store each data point as its own record. Some brief math of the max size of a bigint (the data type of record ID), showed that I have plenty of IDs available. Though the storage requirements, and query performance is still a concern. Should I be concerned about anything using this method? Is there a better option?

The root of my concern is that I simply have not experienced gigantic databases/tables, so while I know that a few million records is small, I still don't know what an over-the-top table would look like. In addition, the storage of the data is the foundation for my software, so updating requires a lot of work to make it happen.


texts: How I create backup with backup by dump and WAL files in PostgreSQL?

texts:
I have a backup and WAL files, I like update my backup with WAL files. Anyone know how to do it?

Thanks


texts:
I know that the default of the RMAN is to add the date in the following format: TAGYYYYMMDDTHHMMSS, But if I wanted something like this: BKP_FULL_11112019. How I do this? I tried using %T but it does't work.

I want to do something like this:

backup as compressed backupset incremental level 0 database tag = 'BKP_FULL_current_date_here'

Which current_date_here must be the current date.


texts: Replication Setup what's the value of MySQL auto_increment and offset in my.ini

texts:
I have setup master master MySQL replication to sync changes in both servers.

Now I want to setup 6 servers, is that possible? I'm making 6 because when an area lost it connection over WAN it will continue it's service and then sync it when the connection is back.

Now I'm having problems with the auto increment increment and auto increment offset to avoid data collision.

I'm confused with this and I've read an article and made it worse.

So now I wonder what should be the proper value of my auto increment and offset in each server.


texts: Use MDX to select Dimension-Members instead of Measure Values

texts:
Is it possible, with MDX, to select Dimension Members instead of Measure Values?

Here is what I currently do (simplified):

SELECT
    {[Time]...} ON 0,
    {[Gender]...} ON 1
FROM [Cube];

Result:

  2008 2009
M  1    -
W  -    2

This is my desired result (in my dataset the returned cell amount would be much lower than in the above result):

Gender Time
M      2008
W      2009
W      2009

Or alternatively:

Gender Time Measure
M      2008    1
W      2009    2

In my project I need to select from 5 different dimension axes, some of them containing several hundred members, leading to a cellset of about 8,000,000 cells, which executes very slow (about 30 seconds in my setup). I already tried to improve the performance with the NONEMPTY function but it is still too slow. My desired results would only return about 400,000 cells (querying the SQL source table requires only 2 seconds, so this is the speed at which the desired data can be retrieved in theory).

Obviously, in this special situation querying the relational source table would be much faster, however, the OLAP server should be single-point-of-truth so I cannot just query the source database.

Are there any MDX functions that allow this kind of query or do you have any other tipps on how to improve the performance of aforementioned query?


texts: How do I find out whenever a value changes in a certain column?

texts:
Need help if anyone can offer!
I currently have my select statement returning the TOP 1000 * results from a table. They are CC numbers that are currently active. I'd like to see how I can go about writing a query that could show me what has CC numbers have changed.
If something has changed, it will send a report to the listed people. If nothing, then it does nothing. It only actions once something has happened. We have an inhouse system that will do the automated part... I just need a select statement that will return what has changed. If I can get some help on this, it would be amazing!
Thank you!


texts: How to fix high memory usage of MariaDB?

texts:
I am using MariaDB(10.1.21) with following storage engines:

InnoDB
TokuDB
Spider Engine

System Configuration is :

3GB RAM
Dual Core Processor

I have tried pt-mysql-summary tool to identify the memory usage and it shows 90% of innodb buffer pool size is filled,when no process is running on server.

I have also tried restarting the MariaDB server and this reduces memory for about 1-2 hours only and after that it eats up memory.

Any clue why it is consuming too much memory?

Sharing my configurations below:

My.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#performance_schema=ON
tmp_table_size=16M
max_heap_table_size=16M
skip-log-bin
#table_open_cache                =16384
#table_definition_cache          =16384

#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages     = en_US
skip-external-locking

#bind-address           = 127.0.0.1
#skip-networking

# SAFETY #
max_allowed_packet= 16M
max-connect-errors = 1000000
skip-name-resolve
#sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# LOGGING #
log_warnings            = 2
slow_query_log          =1
log-output              = TABLE
long_query_time = 5
log_slow_verbosity      = query_plan
log-error               =/var/log/mysql/system_error.err

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 1720M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

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

[isamchk]
key_buffer              = 16M

TokuDB.cnf

plugin-load-add=ha_tokudb.so
tokudb_data_dir=/var/lib/mysql/toku_db_data
tokudb_read_block_size=64k
tokudb_row_format=tokudb_zlib
tokudb_directio=on

Spider.cnf

[mariadb]
#spider_internal_limit          =1
spider_direct_order_limit       =1
spider_skip_default_condition   =1
spider_casual_read              =1
spider_bgs_mode                 =2
spider_direct_dup_insert        =1
spider_auto_increment_mode      =2
#optimizer_switch='engine_condition_pushdown=on'
#optimizer_switch='mrr=on,mrr_sort_keys=off'


texts: Why does my SQL Server Express (2014) connection break after restarting my computer?

texts:
For some reason when I install SQL Server Express (2014), it works fine for a while (usually a day or two), and then quits allowing me to sign in.

I'm not just talking about remotely here, either. Even through the Management Studio, I get:

I sign in using Windows Authentication, and as I said, it DOES WORK fine for a little bit, and then, even though I haven't made any changes, of any kind to the system, the program, or even the database it's linked to, it just decides to quit working until I uninstall/reinstall SQL Server Express (2014), and then it works fine again for a day or two.

I've done this 3 times now, and this is the third time it's decided to spontaneously quit working.

I've tried all the tricks on this website (accepts remote connections, firewall, connection string, starting SQL Browser, ): http://www.sswug.org/articlesection/default.aspx?TargetID=44331 but everything seems in order, and again, it works for a while after install each time.

What could possibly cause this to happen? I don't know where else to look. Should I just go with the (seemingly) more stable SQL Server CE (despite its limitations). Should I go with SQL Server Express 2012, instead? Is 2014 just buggy?


texts:
I have a trigger defined as follow:

USE `veeva_bi`;
DELIMITER $$
DROP TRIGGER IF EXISTS veeva_bi.account_ai$$
USE `veeva_bi`$$
CREATE DEFINER = CURRENT_USER TRIGGER `veeva_bi`.`account_ai` AFTER INSERT ON `account` FOR EACH ROW
BEGIN
    // do trigger stuff here
END$$
DELIMITER ;

When I run that query I got the trigger created as root@localhost:

CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_bi`.`account_ai` AFTER INSERT ON `account` FOR EACH ROW
BEGIN
    // do trigger stuff here
END

But any time I try to run a PHP script, that connects to MariaDB as root user I got this error:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1449 The user specified as a definer ('root'@'%') does not exist' in /var/www/html/veeva_replicator/DB.php:158

Why is that? I have others triggers created on the same way and them did works but this one doesn't, any advice or help regarding this?


texts: Checking existence and syncing data with millions of rows

texts:
I am developing sms based service platform. In India, we have two different types of routes to send sms - promo & trans. Sms sent via promo route has to be checked against nationally maintained database (DND), which currently holds 231450000 mobile numbers.

My question is

What is the efficient way to store this data (DND).
What is the efficient way to check, whether the number exists within these numbers or not.
Twice in a week, a delta dump of these numbers (DND) is provided by telecom authority. How to sync the dump with existing db.
Should I use mysql to store this data, which will consist of mobile numbers, or any other in-memory storage?

EDIT:

Each row of data consists of mobile number alongwith few preferences like area code, user preference etc
The data dump provided twice a week contains only additions / deletions and not the whole data.
Lookup frequency can be very large. Actually every SMS sent from the platform has to first make a lookup against the data (DND). So if the platform is handling 100 sms per second, it has to make 100 lookups per second against this data. The lookup simply checks existence of a particular mobile number against the data(DND) in database.
The new delta data dump (additions / deletions), has to be synced in few hours only, as the dump is provided by telecom authority in the day and the same has to go live by midnight 00:00 hours. So the total time to sync data will be around 3 hours.

Sample DND dump

Sample of DND dump provided. Here A under Opstype means record has to be added, while as D means record has to be deleted.


texts:
My config file is:

systemLog:
    destination: file
    logAppend: true
    path: c:\data\log\mongod.log
storage:
    dbPath: c:\data\db
    journal:
        enabled: true
replication:
   replSetName: "rs0"
net:
   bindIp: 127.0.0.1
   port: 27017
security:
     authorization: enabled

So, I'm successfully connecting via mongod process:

Then connecting to mongo and trying to open databases and collections:

And getting an error:

2018-07-07T15:40:25.092+0300 E QUERY    [thread1] Error: error: {
        "operationTime" : Timestamp(0, 0),
        "ok" : 0,
        "errmsg" : "node is not in primary or recovering state",
        "code" : 13436,
        "codeName" : "NotMasterOrSecondary",
        "$clusterTime" : {
                "clusterTime" : Timestamp(0, 0),
                "signature" : {
                        "hash" : BinData(0,"FshG5mLBvAQUizPHXGfCITV4ZKA="),
                        "keyId" : NumberLong("6573732769795407873")
                }
        }
}

To avoid this error, I'm using command rs.initiate() like:

But what does this error mean? Thanks.


texts: Disable or Remove Edit top 200 Rows option from SSMS

texts:
I have already done some R&D on the same but don't have the real solution. Is there a way to obsolete the right click option "Edit Top 200 Rows" from SSMS?

The goal is to prevent developers from changing data using that option. We can't just restrict their permissions. Developers are required to have the edit rights but they are lazy and we can't track the changes if they edit records with that option. (We are tracking the changes using a paid SSMS extension.)


texts:
I want to partition some of our tables in a SQL Server 2008 database.  The partition key will be date based.

I have several questions:

Is there a rule of thumb regarding the recommended number of rows per partition?
Is there any special problem creating a partition function with different intervals?
I mean for example, that the data from 2008, 2009, 2010 will be based on yearly partitions, and from 2011 and on it will be on a monthly based partitions.
Is it recommended to create a different partition function/scheme for each of the tables (they will be identical except their name) or can I create one monthly and one yearly partition function/scheme and assign the tables to them? What are the consequences for each option?
We have around 15 tables that we are considering partitioning. We want to keep all the data, hence only a split mechanism is needed. I thought to create a management table that will contain all the information on the different partition tables and to create one procedure that will run on all the partition functions and will split them. Another option is to create a different procedure for each of the partitioned tables. I prefer the first option because of its manageability, but I don't know if it will cause issues/contentions.


texts: general design pattern for smushing rows into one column

texts:
I have a table of people :
id, person_name
I have a table of tags:
id, tag_name
there is a junction table for the tags that connects people and tags:
id, person_id, tag_id
I have a query to get a group of people based on different search criteria so it's being built up dynamically and what I would like to do is within that query - for each person - I'd like to be able to grab all of the tag names associated w/ that person. I'd like to grab all the associated tags and stick them into one column for each person row (preferably as an array or object). What is the general pattern to be able to return a group of stuff as one column when cycling through rows like this.  I'm having trouble figuring out what to search for to find a generalized answer online.  I'm using postgres.


texts: Inexplicable differences between 2 almost identical databases on the same server

texts:
Our situation is as follows:

We've got 1 MySQL (5.5.44) database server.
The server hosts 2 databases.
1 database is the production database.
The other is a copy database that gets copied from production every night.

Whenever we apply changes to the database, we first run it on the copy database and then on the production database.

But something has gone very wrong earlier this week. We applied this script:

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE users_copy LIKE users;
ALTER TABLE users_copy ADD COLUMN sample INT(11);
INSERT INTO users_copy (id, name) (SELECT id, name FROM users);
DROP TABLE users;
RENAME TABLE users_copy TO users;

SET FOREIGN_KEY_CHECKS=1;

This was a mistake. CREATE TABLE ... LIKE does not copy foreign keys, so our new users table lacked all foreign keys. We then did basically the same trick:

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE users_copy LIKE users;
ALTER TABLE users_copy ADD FOREIGN KEY company_id etc;
INSERT INTO users_copy (SELECT * FROM users);
DROP TABLE users;
RENAME TABLE users_copy TO users;

SET FOREIGN_KEY_CHECKS=1;

The table looked perfect. All indexes and foreign keys were there. We ran both scripts on both databases.

However: the production database doesn't seem to be using the foreign keys.

When we run, for example, this query ...

SELECT * FROM users u INNER JOIN companies c ON c.id = u.company_id;

... the query plans on both databases look completely different. Both tables have around 2 million records, and on the production database it joins on all those 2 million records, on the copy database it just takes 1 row as it should do.

At that point we realised that setting FOREIGN_KEY_CHECKS to 0 while inserting may have been (yet another) mistake, so we ran this script on both databases:

CREATE TABLE users_copy LIKE users;
ALTER TABLE users_copy ADD FOREIGN KEY company_id etc;
INSERT INTO users_copy (SELECT * FROM users);

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE users;
SET FOREIGN_KEY_CHECKS=1;

RENAME TABLE users_copy TO users;

Still no relief. The copy database runs smoothly, the production database doesn't. What makes it slightly more strange, is:

Yesterday's production database did not perform, but today's copy database does. Those databases should be identical!

To sum it up:
- 2 databases on the same server.
- Exact same schema.
- Exact same keys and indexes.
- 2 completely different query plans for the same queries.

We ran ANALYZE TABLE on all applicable tables, but still nothing. Our service provider tells us that MySQL distrusts the indexes on the production database but trusts them on the copy database. They don't know how we can get the production database to 'retrust' the indexes.

What can we do to resolve this issue? Where do we look for internal differences? How do we rebuild the indexes?


texts: Make index work on join to table variable

texts:
I a table with several columns, but the most importants are: DocumentId(bigint), StatusId(int) and ReceiveDateTime(datetimeoffset(3)).

CREATE TABLE [DocumentInfo](
    [DocumentId] [bigint] NOT NULL,
    [StatusId] [int] NOT NULL,
    [ReceiveDateTime] [datetimeoffset](3) NOT NULL,
    [IsDeleting] [bit] NOT NULL,
    [StorageId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_DocumentInfo] PRIMARY KEY CLUSTERED
(
    [DocumentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I also have a table variable which contains several rows (4 for now):

DECLARE @Tresholds TABLE
(
    DocumentStatusId int NOT NULL,
    Treshold datetime2 NOT NULL
);

I used indices on DocumentStatusId (is was a primary clustered key), and unique constraint (Treshold, DocumentStatusId), but query didn't use it, so I removed them.

My query looks like:

WITH TopOutdatedDocuments AS
(
    SELECT TOP (100000) di.DocumentId
    FROM [DocumentInfo] di WITH (NOLOCK)
    JOIN @Tresholds t ON di.StatusId = t.DocumentStatusId
    WHERE (ReceiveDateTime < @DocOldestTreshold OR (ReceiveDateTime < @DocNewestTreshold AND ReceiveDateTime < t.Treshold))
    AND NOT EXISTS (SELECT * FROM [doc].[document] d (NOLOCK) WHERE d.ParentId = di.DocumentId)
)

The problem that the query plan always use index scan:

I tried to create an index for every permutation of these 3 columns, but it didn't use it anyway. DocOldestTreshold and @DocNewestTreshold are used to speedup a query, becuase they can be computed without a join. But it seems that engine doesn't use this opportunity.

How can I speedup a query? Its logical sense is that we have some tresholds for every StatusId, and if document is older than its treshold, it should be selected by this query.

Any help will be appreciated. I am now on SQL Server 2014. Order of [DocumentInfo]'s size is hundred millions of rows, typical size of @Tresholds is < 10.


texts:
I am trying to capture all statements/SPs and RPCs (either they finish or fail) on a SQL 2005 server using SQL trace.
Are the following events enough?

Exceptions
Stored Procedure
RPC:Completed
RPC:Starting
SP:Recompile
TSQL
SQL:StmtCompleted
SQL:StmtStarting

thanks!


texts:
I have a database design issue, one that has rattled my brain.

What is the project?

I am designing a platform where different people register a group, each group is allowed to register their own members, it is important to keep these groups away from each other, as each are to their own, each group would have access to 65 or more tables with possibilities of 1000 of rows of data in each table, in their own database, each database is a copy of an empty master database.

Where i am stuck.

I do not know if this is wise cause i could end up with a 1,000 - 30,000 databases. I need to either create a database with a unique name (scenario above) or create a database with a unique environment variable, where each group has it's own environment variable.

Where things get messy.

Assigning each group to a unique database allows me to keep things simple, allows me to access all the data within the group relatively easy (vs searching through 1000 of groups data in one database). a simple but not too important, but still relevant eg. Each group can create things like their own invoices, so if all groups have unique databases then the invoice numbers would follow logically (users perspective), but if all groups are within the same database invoice numbers would not follow logically (users perspective).

The Question Then

Should I, or would it be wise, to create multiple database to keep my system efficient, bear in mind that there is no need to cross over from one group to another.


texts: ADD or modify column in one call (upsert) - avoid DROP calls

texts:
I have this call:
ALTER TABLE mbk_user ADD login_attempts int;

I want to update it to:
ALTER TABLE mbk_user ADD login_attempts int NOT NULL;

is there a way to do "ADD or MODIFY" in one sql statement (an upsert), or do I need to drop the column first and then ADD it again? I want to avoid drop statements ideally.


texts:
I have a table with 220 rows. It has about 20 columns. One of the non-clustered indexes has a datetime as the key value and includes a unique identifier and a varchar(128). The clustered PK is a bigint.
The datetime for every row is updated very frequently (40 times a second).
If I rebuild the index it is about 200KB but after a couple of hours, it will have grown to about 50MB. (And it appears to keep growing). Given how often it is accessed, scanning 50MB compared with 200KB is hurting a lot. SQL 2016 SP2 13.0.5201.2
Can anyone explain, or point me to something that explains, how an index with only 3 columns, on a 200 row table can get to be 50MB? And even better anything I can do about this? (The 40 updates a second is not something happy with but I haven't been able to convince anyone that this is excessive yet).
Unfortunately, in-memory is not an option. Nor is changing the code.
Edit: Added output from sys.dm_db_index_physical_stats function as suggested by Andrew Sayer

Possibly related info - Server is not in an availability group. Allow Snapshot Isolation is false but is_read_committed_snapshot_on is true.


texts:
I am trying to clone a 9i database (C) from a target 9i database (T).  On running the rman script, i get the rman-20207 error : UNTIL TIME is before RESETLOGS time.  When i do a list backup of database, no backup was display.
Just yesterday, the target 9i database was refreshed from production (P), using rman.
Does the refresh of T using rman, removed all previous backups of T?


texts:
I want to create a simple mongodb cluster but while I am trying to add another shard it is throwing an error.

rs0:PRIMARY> rs.add("node75:27017")

{
"ok" : 0,

"errmsg" : "Quorum check failed because not enough voting nodes responded; required 2 but only the following 1 voting nodes responded: node73:27017; the following nodes did not respond affirmatively: node75:27017 failed with Received heartbeat from member with the same member ID as ourself: 0",

"code" : 74,

"codeName" : "NodeNotFound"
}


texts:
I apologize if this question has (undoubtedly) been asked before, however I've been hearing conflicting advice from colleagues and the internet.

I have a table of "groups" and each group has a list of members.  My initial implementation plan was to have a table that mapped user IDs to group IDs.  However, a colleague suggested using a different method, where members would be stored in a blob of information related to a "group" entry.  I cannot remember why this was suggested, but he seemed to think it would be more efficient because a member-group mapping table could grow very large, very quickly.

I can't see why it would be a better approach, but I suppose I'm just asking if there is a better alternative to simply having a user ID - group ID map table?

Thanks for your time!


texts: use different odbc conector to connect mysql server

texts:
We found out Mysql odbc connector is released under gplv2 license. We have a commercial application (in c#) that connects to mysql (using Mysql.data) so we are supposed not to use mysql odbc connector because we would have to release our source code.

How can I connect to MySQL usign a method that does not involve using a gplv2 connector in order we dont have to publish our source code under the same license ?


texts: can i backup multiple db2 databases at the same time?

texts:
in db2 9.5 LUW i have one db instance to which i just added a second database. we don't have TSM or multiple partitions which is what all the documentation seems to be about when i search the web for this.

can i run an online backup db for the first database and immediately start an online backup db for the second database and let them run simultaneously? or would i have to let the first finish before starting the second? thanks.


texts:
I am getting this error

{ "ok" : 0, "errmsg" : "server is not running with --replSet" }

when I run the command on windows cmd

rs.initiate( { _id : "repl1", members: [ { _id : 0, host : "mongodb0.example.net:27017" } ] })

I changed it to

rs.initiate( { _id : "repl1", members: [ { _id : 0, host : "localhost:27017" } ] })

but still have same error

how can I fix this issue?


texts: Which bitness of SQL Server Express will be compatible?

texts:
I need to install SQL Server Express on a Win 7/64-bit system, but I must have MS Office 2010/32 bit installed.

Would I be correct in assuming that I must use the 32 bit version of SQL Server Express because I must keep the MS Office 2010/32 bit?

I'm surprised I have not yet found the answer to this myself.

thanks!


texts: How to verify replication data integrity in SQL Server when using external replication engine

texts:
I am looking for a way to verify that data I am replicating between SQL Server databases is making it to the destination OK.

Data is being replicated using an external replication engine, no built-in SQL Server replication.

Looked into using SQL Server's CHECKSUM and CHECKSUM_AGG but those functions do not work well with tables with millions of records.

I am thinking that a scalable solution would divide tables into chunks and compare those.


texts: What is the best way to mirror an ASPState database?

texts:
I'm not having issues with the actual mirroring part, but because of the use of temporary tables, IIS is throwing a bit of a wobbler if a failover occurs (I'm still testing a few things, so it's me causing the failover).

Is it possible (or even a good idea) to mirror tempdb? Or should I just accept that some people might get kicked out if the principal database is not accessible for any reason.

I'm using ASPState so that the two web servers (behind a load balancer that enables sticky sessions) can both get to the same session data.


texts: Manage max table storage space in case of excess data (size in GB)

texts:
I am using SQL Server 2008 R2. I have created a database named testDB. I have a lot of tables with some log tables in. Some log tables contain over 100k records.

I want to fix the size of those log tables, and want to move records into another database table in another location, so my database has no problem.

Is there any way to make such above steps which I want for my database?

Is there already built any such functionality in SQL Server?


texts: Setup [master] - [master-slave] - [slave] replication

texts:
I am trying to set up a MySQL replication architecture between 3 MySQL servers by following a tutorial.

Server A as a master to Server B
Server B as a slave of server A and a master for server C
Server C as a slave of server B.

Using this architecture, the goal is if any data will be changed on Server A, both the server B and C must be updated.

After doing some tests, I got the following results:

Replication between server B and A is working well.
Any change on server A is mirrored on B but not in server C.
Testing if replication is working between server B and C (by manually editing some information in server B data), I was surprised that everything is working well, data edited in server B was edited in server C.

I can't understand why if Server A is edited and B is following it server C in that case is not following?


texts: Uncommitable T-SQL transaction after trigger applied to table

texts:
Good morning,
I have written a Trigger on a table that should evaluate the entered values (Length, width and Height) and based on those entered values, should update a column (Type) in the same record with a corresponding value. After applying the trigger, everything works perfectly, but at a certain time, I get the following error:

  Unexpected error:The current transaction cannot be committed and
  cannot support operations that write to the log file. Roll back the
  transaction. The statement has been terminated

I found a thread to handle errors using Try/Catch blocks with XACT_ABORT() and XACT_STATE(), but still the same issue is happening. Again, the issue is not occuring all the time, but sometimes only.
Here is my Trigger:

CREATE TRIGGER [dbo].[UTRAfterUpdate]
   ON  [dbo].[TBL_STOCK]
   AFTER UPDATE
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @CBM FLOAT, @T NVARCHAR(1);
    DECLARE C CURSOR FOR SELECT LEG_PRODUCT_ID, PR_LENGTH1,
                         PR_WIDTH1, PR_HEIGHT1, PR_TYPE FROM INSERTED
    DECLARE @ID BIGINT, @L INT, @W INT, @H INT, @TYPE NVARCHAR(1)

    SET XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        OPEN C
        FETCH NEXT FROM C INTO @ID, @L, @W, @H, @TYPE
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF(UPDATE(PR_LENGTH1) OR UPDATE (PR_WIDTH1) OR UPDATE(PR_HEIGHT1))
            BEGIN
                SET @CBM = (cast(@l AS DECIMAL(18,2))/100
                                       *cast(@w AS DECIMAL(18,2))/100
                                       *cast(@h AS DECIMAL(18,2))/100)
                SET @T = CASE
                    WHEN (@CBM > 0.00  AND @CBM <= 13.00)
                          AND (@h <= 170) THEN 'C'
                    WHEN (@CBM > 0.00  AND @CBM <= 13.00)
                          AND (@h >  170 AND @h <= 220) THEN 'V'
                    WHEN (@CBM > 13.00 AND @CBM <= 20.00)
                          AND (@h <= 220) THEN 'V'
                    WHEN (@CBM > 0.00  AND @CBM <= 20.00)
                          AND (@h >  220 AND @h <= 250) THEN 'B'
                    WHEN (@CBM > 20.00 AND @CBM <= 30.00)
                          AND (@h <= 250) THEN 'B'
                    WHEN (@CBM > 0.00  AND @CBM <= 30.00)
                          AND (@h >  250) THEN 'T'
                    WHEN (@CBM > 30.00) THEN 'T'
                    ELSE @type
                END
                IF(@type <> @T)
                    UPDATE dbo.TBL_STOCK
                                    SET PR_TYPE = @t,
                                    LAST_ACTION = 'UTR',
                                    LAST_USER = 'System'
                    FROM TBL_STOCK STOCK INNER JOIN INSERTED INS
                            ON STOCK.LEG_PRODUCT_ID = INS.LEG_PRODUCT_ID
                    WHERE STOCK.LEG_PRODUCT_ID = @id
            END
        FETCH NEXT FROM C INTO @id, @l, @w, @h, @type
        END
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF (XACT_STATE()) = -1
        BEGIN
            PRINT 'The transaction is in an uncommittable state.'
            + ' Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;
        IF (XACT_STATE()) = 1
        BEGIN
            PRINT 'The transaction is committable.'
            + ' Committing transaction.'
            COMMIT TRANSACTION;
        END;
    END CATCH

CLOSE C
DEALLOCATE C
END

Can you please assist me n solving the issue?

Thanks in advance!


texts: How can I ensure that only one user at a time can call a stored procedure? Can I lock an SP?

texts: I have a procedure that generates reports basis user requests. The request is such that at a time only one  user can call the procedure. Is there any way I can handle this using DB2? Like place a lock on a procedure similar to how we lock tables?


texts: How to query the mongoDB admin database?

texts:
I'm trying to execute the below commands taken from the mongoDB docs

use admin
db.system.version.find( { _id: "authSchema" })

But running those in Robo 3t gives the following output

switched to db admin

Error: error: {
"$err" : "not authorized for query on admin.system.version", "code" : 13
}

The user I'm logged in with has the dbOwner role.

Some people suggest granting the root role to the user, but this fails, saying 'not authorized'

db.grantRolesToUser(
    "fooUser",
    [ "readWrite" , { role: "root", db: "admin" } ]
)

It seems like a bad idea to grant root access just for this one query anyways, but I don't see any better solution.

So, how does one query the admin database in mongoDB?

(Current mongoDB version is 2.6.11)


texts: SQL Server: update trigger fires before insert trigger

texts:
I have an application with a SQL Server-hosted database. I do not have access to the application code, but I do have full access to the database. I have added my own custom auditing to a table to assist in debugging.

I'm using after triggers. Below are simplified versions of my triggers.

Question: I am seeing update audit records that precede the corresponding insert audit records. How is this possible? The difference is only a few miliseconds and doesn't matter for my current purposes, but I can imagine much worse scenarios where program logic depends on the correct chronology.

I know about the ways to control trigger execution order among triggers of the same kind (all insert or all update). What assumptions can I make about heterogeneous trigger execution order?

create trigger dbo.MyTrigger_i on dbo.theTable
after insert
as
begin
    set nocount on
    declare @Date datetime, @User sysname
    set @Date = GETDATE()
    set @User = SUSER_SNAME()
    insert into MyAudit (RowID, [Date], UserName, Comment)
            select i.ID, @Date, @User, 'Insert'
            from
                inserted as i
end
go

create trigger dbo.MyTrigger_u on dbo.theTable
after update
as
begin
    set nocount on
    declare @Date datetime, @User sysname
    set @Date = GETDATE()
    set @User = SUSER_SNAME()
    insert into MyAudit (RowID, [Date], UserName, Comment)
            select
                i.ID, @Date, @User, 'Update'
            from
                inserted as i
                inner join deleted as d
                    on i.ID = d.ID
end
go


texts:
Here's the create statement pulled out of the database itself for a trigger:

CREATE DEFINER=`walkdot_logan`@`67.53.251.66` TRIGGER `wd_artists_bi_trg` BEFORE INSERT ON `wd_artists` FOR EACH ROW BEGIN
    IF NEW.modified_on is null THEN

        INSERT INTO wd_modified_records
        (modified_date, table_name, record_identifiers)
        SELECT SYSDATE(), 'artists', CONCAT('code:', NEW.code, ';');

        SET NEW.modified_on = SYSDATE();

    END IF;
END;

Both HeidiSQL and MySQL Workbench are complaining about syntax errors; saying that the semi-colon is missing from the SELECT statement line. MySQL Workbench is even underscoring the closing parenthesis to tell me I'm missing the semi-colon.

EDIT: I should point out that I've tried several insert types, and even simple select statements and the semi-colon error persists. Also, I was able to make this trigger using the trigger creator form in HeidiSQL without incident.


texts: Using a foreign key on multiple fields in one table

texts:
Simple question probably.

For two tables. A one to many relation is created from A -> B, TeamID -> Fk_Team, however there are multiple fields that need to reference this one relationship how does that work.

so

A                          B
TeamID                     EventID
teamName                   datetime
teamocation                homeTeam
etc                        awayTeam
                           Weather
                           Fk_team

So how do I define that homeTeam and awayTeam both utilise the Fk_Team relationship and that the homeTeam & awayTeam entry must exist in the TeamID.teamName field?


texts: Update Column from Recursive CTE (MySQL/MariaDB)

texts:
I need to update the same table I'm using to create a recursive cte. The cte works as a select statement, but I can't get it to actually insert those values back into the table.
My code is below- basically I want to:
UPDATE tbl_1min
SET tbl_1min.ema9 = x.NewEma
WHERE x.id = tbl_1min.id

Can anyone help? I'd really appreciate it!
SET @NDays := 9;

WITH RECURSIVE anchor AS
    (
        SELECT
            a1.id,
            a1.close,
            a1.ema9
        FROM
            tbl_1min a1
),
x AS
    (
        SELECT
            anchor.id,
            anchor.close,
            x2.ema9 AS PrevEma,
            anchor.ema9,
            anchor.ema9 AS NewEma
        FROM
            anchor
        INNER JOIN
            anchor x2
        ON
            anchor.id - 1 = x2.id
        WHERE anchor.id = @NDays -- FIRST SEED ROW
UNION
   SELECT
    anchor.id,
    anchor.close,
    x.PrevEma,
    anchor.ema9,
    (((2 / (1 + @NDays)) * (anchor.close - x.PrevEma)) + x.PrevEma) AS NewEma
    FROM
        x
    JOIN anchor
    ON anchor.id = x.id + 1   -- `X` WALKS ITSELF
    )
SELECT
    id,
    NewEma
FROM
    x
ORDER BY x.id
;


texts: MariaDB error on alter table - table removed - constraint name is blocked

texts:
Today, we experienced a strange behavior in 5.5.41-MariaDB (InnoDB):
An ALTER TABLE (add constraint) statement failed with error (mysql server has gone away after that). Then the database table disappeared (but nobody dropped it) and the table create statement afterwards failed because the constraint name already exists although there was none in information_schema. Drop table with restart of MariaDB (as I read somewhere else to solve the problem) didn't help, too, so in the end we added the constraint with another name.

So my question is, what happened and is it a problem (besides of the very little problem that the constraint name is blocked)? Is there a method to detect and repair it (mysqlcheck told me that everything is OK)?

Thanks in advance

P.S. a rename of the constraint (to the "blocked" constraint name) failed with the result that the constraint was removed, too.


texts: How to do WHERE x IN (val1, val2,...) in plpgsql

texts:
I would like to update a selection of rows in a table; this works...

UPDATE t1 SET col1 = 'newvalue' WHERE col0 in (2, 4, 5);

But how can I do the same in a plpgsql function? The following gives a syntax error...

CREATE OR REPLACE FUNCTION foo(intarray int[])
RETURNS VOID AS
$BODY$

BEGIN
  UPDATE t1 SET col1 = 'newvalue'
  WHERE col0 in intarray;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

The error...

ERROR:  syntax error at or near "intarray"
LINE 7:       WHERE col0 in intarray;

Edit:

If we replace in intarray; with in (intarray); the function is recorded but when running SELECT * FROM foo(ARRAY[1,3,5]) the error becomes...

ERROR:  operator does not exist: integer = integer[]
LINE 2:       WHERE col0 in (intarray)


texts: Grand read privilege to the user but is not allowed to grant the same privilege to another user?

texts:
i am a noob and this is how far i got so far, thanks and also how would you do it if you want to allow user to grant the same privilege to another user.

GRANT SELECT ON DATABASE_NAME.* TO USER;


texts: Oracle 11g setting for backwards compatibility?

texts:
Edit: Please close this topic.

Unfortunately, this issue can't be resolved as it turned out the question is very off-base to begin with. The actual issue was completely unrelated to the question I asked.

We are experiencing a data inconsistency issue after importing an exported Oracle 9i database into Oracle 11g.

Is there a setting that might facilitate this process better? A "compatibility-mode" settings or group of settings of sorts?

I'm looking for an extra lead that might help our DBAs, who are a little new to these sorts of issues I think, as this import falls outside of their normal process.

Thanks in advance for any help! Happy to provide more information as needed.


texts: Efficiently write SELECT queries for tables with lots of columns (not SELECT *)

texts:
Due to limitations in my GIS software, I need to write select queries for each of my 200+ tables in my database. The queries need to select all columns, except for one, the SHAPE column.

Is there an efficient way to get all the field names for each table - for the purpose of writing select queries?

What I've tried:

I think this is possible with SQL Developer(free). But unfortunately, I don't have SQL Developer, due to I.T. challenges.
I'm aware that when I create a SELECT * view on a table in Oracle, the query is translated to explicitly select individual fields. So I could a) create a SELECT * view for each table, b) get the view definition, and c) write the query for each table. This is probably quicker than manually typing each field name, but not by a lot.

How can I efficiently write select queries for tables with lots of columns?


texts: How can I Get Value of Max(col) in a greatest n per grouped by into Declared variable. MS SQL sever

texts:
I am looking for how to set the value of  @MaxItemSeqNum
to the returned result of the column MAX(dbo.[Table1].[Item_Seq_number]) in my select statement.
I need this in order to insert the next record with the next sequence for that grouped item.
I already have a surrogate primary key that auto-increments and is used to identify all items in the Table. Batch_ID can have duplicates and I group by these and then select the max of itemSeqNum to insert the next sequence itemSeqNum + 1. So if I have 5 records with the same batch_Id I am expecting to get 5 as a result and increment it by 1.

DECLARE @MaxItemSeqNum Int

SELECT Batch_Id,MAX(dbo.[Table1].[Item_Seq_number])
FROM Table1
WHERE Batch_Id = 1
Group By Batch_Id


texts: What exactly is contained in the SQL Server Shared Feature Directoy vs. Instance Root Directory?

texts:
What exactly are contained within the SQL Server Shared Feature Directoy vs. Instance Root Directory? Any benefit in putting these onto different drives other than the default C:\?

Note: I do understand the advantages of changing the Data Directories for user databases, tempdb etc... like below:


texts: What are the minimal permissions required for the "Users and Privileges" tab in MySQL Workbench to function in MySQL 5.6

texts:
My ultimate goal is to allow administration for the purposes of password resets with minimal possible permissions given.  It seems to me that with the UserAdmin role, or even UserAdmin with SecurityAdmin, the "Users and Privileges part of MySQL Workbench should work; but it doesn't.  Does anyone know what custom role privileges and/or GRANT permissions are required to allow the use of Users and Privileges?

UPDATE:
It seems the minimum I need to get it to "work" is CREATE USER AND  SELECT; but that doesn't help me do what I want to do, which is be able to assign some privileges (SELECT/EXECUTE) on different schemas.  This does not seem to work as intended because even when I have the GRANT OPTION, RELOAD, or even SUPER and/or SHOW DATABASES in addition to SELECT and EXECUTE, I still get an error when trying to add or delete SELECT privileges on a specific schema that says: `(u'Error revoking privileges for @ in schema , 'You must have the GRANT OPTION privilege, and you must have the privileges that  you are reovking').

Consider I have followed what the error implies (unless I am missing something), it should work, but it doesn't.

So to clarify my question: I would like to know the least amount of administration privileges required for an administrative user to be able to:
1. create a user and/or modify passwords
2. assign SELECT and EXECUTE privileges to that user on specific schemas (without Workbench throwing errors that aren't necessarily true -- as even with the error, it creates the privileges, but it won't remove them [i.e. I can't delete it after I make it under that user]).

UPDATE2:
As far as I can tell, after much trial and error and process of elimination: the titles under "Administrative Roles" with associated descriptions, such as UserAdmin and SecurityAdmin, do not work as their descriptions describe.  The only thing that accomplishes the above is to grant DBManager, which is what I was trying to avoid.


texts: Will removing a 2nd log file break log shipping?

texts:
We have a large database 200GB+ to which we added a second log file to cater for growth during an upgrade. This is now no longer needed so we want to remove it. Normally this would be a simple ALTER DATABASE <blah> REMOVE FILE <filename> statement.

However our database is log shipped to a secondary for reporting, so the question is - will removing this file break log shipping?

Server is SQL Server 2005 SP3 Standard Edition if it has any bearing.


texts:
I recently joined a company, and I just noticed that many of the stored procedures have the same section of code repeated throughout. I noticed because I was tasked with changing a small section of that code in every SP that it occurred :)

It is a fairly hefty chunk of code, about 30 lines. The code is part of an insert statement, and it basically joins 4 tables together with WHERE/AND conditions that do not really change from SP to SP. It looks similar to below:

...
...
FROM <TableOne>
  INNER JOIN <TableTwo> ON ...
    AND .....
    AND .....
  LEFT JOIN <TableThree> ON ...
    AND .....
    AND .....
WHERE .....
  AND .....
  AND .....
  AND MedicalPlanCode IN ('abc', 'def', 'ghi')

The only part that changes from SP to SP are the values ('abc', 'def', 'ghi')

There also can be different amounts of those values, so some SP's will have 2 values, others will have 5, etc...

Everything I think of changes that section of code to dynamic SQL, and I am not sure if that is worth it. However, the programmer in me hates this situation.

Should I try to implement some form of code reuse? Would it have a ROI? What are my options? I had to go through ~100 stored procedures, which took about an hour.

The 100 SP's are spread out over 20 or so different databases. I do have permissions to create a view.


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

texts:
I'm running SQL Server 2008 R2 Developer on Windows 7 x64, as the default instance. For some reason, SQL Server's performance counters seem to have disappeared. SELECT * FROM sys.dm_os_performance_counters returns zero rows.

I tried running lodctr /T:perf-MSSQLSERVERsqlctr.ini. Although it completed without errors, it didn't fix anything, even after restarting the Remote Registry service. In fact, the counters are now missing from perfmon. unlodctr MSSQLSERVER still says that the counters are not installed, but lodctr /Q:MSSQLSERVER says they're present and enabled. Regular Windows and .NET counters are working fine.

In the Windows Error log, I'm seeing Error 8317:

  Cannot query value 'First Counter' associated with registry key
  'HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance'. SQL
  Server performance counters are disabled.

I suspect the problem may have been triggered by an installation of SQL 2012 RC0 that failed. Any suggestions for how to recover, short of reinstalling SQL Server 2008 R2?


texts: MYSQL - number of rows returned equals number of connections?

texts:
Does my query get sent to the database once and I get a list of all the results in one shot which I then loop through, or do I have to request the next row from the DB each time?

Essentially, does reducing the number of rows I expect to return mean less connections/calls to the DB meaning my DB will be able to handle more connections at once, or is the number of database connections not dependent on the number of returned rows?


texts: Data Migration from Oracle to SQL Server

texts:
If you need to do a data migration from an Oracle database to SQL server, what approaches and technical solutions are the best practice?

Our database has about 100 million rows and a total of 52 tables.


texts: How to create a multiple mysql replication

texts:
My question is about to create a mysql replication server.

Here i have multiple slaves and i want replicate all respective database into multiple master server .

Que is that how could i set multiple master in a single server.

details: i have 4 clients  ,and I want to setup a master-server machine where I want database replication from my all 4 clients(slave), but here it require 4 master-server. and i want to do do it ina single hardware machine.


texts: How do I query only for committed data while avoid a deadlock

texts:
I have a system writing batches of queries as transactions (to maintain coherency), these can be sets of 30-100 queries per transaction. These are done on multiple tables

At the same time there are read queries on the same tables using inner selectes - SELECT FROM table1... IN (SELECT from table 2...)

To clarity - there is one writer thread writing the transactions (each transaction writing to all tables) and many reader threads.

I am getting deadlock errors.
1. could the deadlock be related to the IN selection?
2. how can I make the read queries provided only commited data, but not necessarily wait for current transactions to commit (meaning it can return data previously committed)
3. could the deadlock be a result of a transaction that is simply taking too long to complete?

Thanks!


texts: Copy scheduling information form local system to remote system  SSRS

texts:
I    am scheduling some reports locally in my system.
How can I copy this schedule information to remote system to avoid re-doing of scheduling at remote system?


texts:
We are creating a new application using microservices, and for that we would like to be able to raise database instances just like we raise new instances for the application's microservices. We do not need it to be necessarily a cluster; Microservices know that information is not always complete or up to date, but we do need to be able to raise at least slaves so that they can attend momentary high traffic.

We have thought of doing a periodic backup (every hour for example) and then raise a new docker with that backup and rerun all transactions until it is up to date. That will give us another node in the cluster or a new slave to query.

This process is likely not too fast but is it feasible? Does it make sense? Isn't there a better solution?

What I would like to know is if there an easy or standard way to upgrade our application's database CPU & IO capacity by adding new VMs or if there is a much simpler and easier way to achieve this.

We are currently using PostreSQL and we are open to add plugins if necessary.

Thank you all for your replies and comments.


texts:
I have a Linux server & MariaDB v10.0.33, where tmpdir is tmpfs (ram), for fast temporary table access; tmpfs is obviously limited in size.

MariaDB seems to use the same tmpdir location for alter table operations (table copy), that require a table copy;  these can require large amounts of space if tables are many Gb.

Old MySQL versions configure 'table copy' and 'temp table' separately;
Table copy written to tablespace & temporary tables written to tmpdir.

I am aware I can set 'innodb_tmpdir' per command.
Can MariaDB configured so that table copy & temporary tables use different locations?


texts: Maximum Attachment Size in database mail

texts:
Good Afternoon,

I am trying to send an attachment via database mail.

declare     @mail_recipients  VARCHAR(4000)  = 'abc@123.com'
          , @mail_subject     VARCHAR(500)   = 'subject'
          , @mail_body        VARCHAR(4000)  = ''
          , @mail_attachment  VARCHAR(500)   = ''
          , @sql_query        VARCHAR(4000)  = ''

..... Code to populate the query

EXEC msdb..sp_send_dbmail   @profile_name                 = {our profile name}
                          , @recipients                   = @mail_recipients
                          , @subject                      = @mail_subject
                          , @body                         = @mail_body
                          , @body_format                  = 'HTML'
                          , @query_result_header          = 1
                          , @query                        = @sql_query
                          , @attach_query_result_as_file  = 1
                          , @query_attachment_filename    = @mail_attachment
                          , @query_result_separator       = ','

I am getting the following error.

  Msg 22050, Level 16, State 1, Line 0
  File attachment or query results size exceeds allowable value of 10000000 bytes.

When I run this in SSMS, and SAVE-AS csv file, the file is only 9077 KB.  What would database mail be doing to make it think the file is so much bigger?


texts: Linked server created but failed connection test

texts:
When creating the linked server I am choosing the "Be made using this security context" on the security tab and I am trying with a domain account which is failing. I could of sworn in the past I was able to do this.
If I use a local SQL account then everything works and the connection test is successful.
The domain account I am trying to use is a sysadmin on both SQL servers and I've even tested to make sure it can login to the SQL server I'm trying to create the linked server to.


texts: How to separate out individual sql statements from Oracle SQL script for execution from java code

texts:
I have Oracle DB 11g Enterprise Edition and I want to execute sql script from my java code.
I am reading this sql script from a .sql file and the script contains statements such as create table, create type, alter table, drop type, drop procedure, insert into as well as pl/sql blocks with begin and end and all such constructs.

My question is, how to separate out individual sql statements for execution from java code? In MS SQL I could simply separate these sql statements by GO keyword, whereas in Oracle db there is no such separator. So normally what is used as separator in Oracle db scripts?

As an exmple say I have following create trigger block in my sql script along with other sql statements. Here I have semicolons which I can not consider as the SQL Statement terminating charter (PL/SQL block can itself contain multiple semicolon-separated statements). Which means I can not use ; similar to GO (in MS SQL).

CREATE OR REPLACE TRIGGER SQLVersionHistory_SeqNum_TRG
   BEFORE INSERT
   ON SQLVersionHistory
   FOR EACH ROW
   BEGIN
    :NEW.SequenceNumber := SQLVersionHistory_SeqNum.NEXTVAL;
   END;


texts: Should SQL Server statistics be included in versioning?

texts:
Recently I ran the SQL Server Tuning Advisor. It suggested to add some statistics. Currently we have no statistics checked in our database versioning.

My question is: Should these suggested statistics be added to the versioning (like SVN or git)?

I am not sure this is a good idea, because they were created based on the usage of this particular database server. The alternative I currently like better is to run the Tuning Advisor on each server separatly, add the statistics and don't include them in schema compares.

What are the best practices? What are your negative/positive experiences?


texts: Timestamp with no time zone - if I'm ok with UTC at all times, is there any downside to using it?

texts:
Timestamp with time zone vs timestamp with no time zone.

I'm aware that timestamptz is the same as timestamp no time-zone + (time-zone difference) that gets added upon saving a value in a database. That is, in a db, when stored, they both are indistinguishable.

If I decide to use timestamps with no time-zone only, even in the cases when normally timestamps with time-zone would be recommended to be used instead, but if I'm and users of my website are ok with keeping in mind that I and they would be operating with UTC only all the time, even if I or them happen to be physically in different time zones, would timestamps with no time-zone work out well and with no flaws?


texts: Recovery time effects of Availability Groups

texts:
We did different kinds of basic Tests and AlwaysOn passed in many tests. We finally did a heavy write test for AlwaysOn and it gave surprising results.

The actual Test details are here, the goal is to see if AlwaysOn availability group can accommodate a high write load.

I have two VMs each running on 8 cores and 17GB of RAM allocated to SQL Server.
We wrote a script to generate reasonably good write I/O (in 20 threads).
Each thread basically inserts 24 MB of data into a table and deletes in an endless loop.

Within 15 minutes of the test run, the estimate for recovery time on automatic failover reached 12 minutes which is pretty bad. We tried a failover to confirm if it really takes 12 minutes, it took around 5 minutes which is still too high.  Also if we continue the test for three hours recovery ETA is almost 3 hours and it is taking hours to recover on failover (clearly this should not have been the case if it was a cluster failover, because all transactions are committed transactions).

So couple of things..

It is very clear that the synchronous secondary replica is not able to keep-up with the load primary is generating (even though both machines are of same configuration). And the side effect of this is the log on primary will keep on growing (even we take log backups it can't truncate the log).

We know that the secondary uses one thread per every 4 CPU-cores for doing redo, which looks like a clear limitation. If the primary is running 100 threads to generate load, the secondary can't use that many threads anyway.

Additionally, the primary does all its transactions in-memory and leaves the actual data file writes to checkpoints. However, it seems that secondary has to read all transactions from the physical log drive and redo. The log pool on secondary which is supposed to make this process faster? But it is not doing a good job in this scenario.

Finally questions to AlwaysOn experts:

Does anyone know how the redo process exactly happens?

is it cached ?
is the buffer pool involved at all ?

Does the secondary use log pool to cache the log entries for redo?
What is the size of log pool? Can it grow up to the max memory available?
When redo happens, the redo thread reads the pages to buffer pool and maintains them as if it is a normal transaction?
If secondary is not able to keep-up how come AlwaysOn articles say recovery time is a few seconds?

This makes the high availability part of Availability Groups questionable, since these recovery times are unsustainable.

[Edit by asker] Clarifications, since people seem to think this is answered, the transactions at primary are indeed acknowledged (i.e. the log is hardened), since the state of the secondary is always "synchronized". So it is not a problem with the hardening of the log. So it is the redo process that is taking forever upon failover. This means that AlwaysOn will always take longer to recover than without it for any load that generates log > redo threads capacity.


texts: Why query cache plan kept dynamic queries but not sp?

texts:
My company has some legacy system that uses quite a lot of dynamic queries (no parameters). In the newer systems we use Stored Procedures and parameterised SQL. However what we found is that our Stored Procedures frequently got performance spikes.

I looked into it and seems the plan cache periodically clears stored procedure plans, but keeps lots of dynamic SQL statements in the plan cache.

I am bit confused on why SQL Server (SQL Server 2017) is doing this. How does SQL Server decide which one to remove?


texts: Why database replication fails when database mirroring is broken?

texts:
I'm trying to understand what effect database mirroring can have on replication in SQL Server 2008.

Here is current environment: (don't ask me why it was done this way or to change it)

Server A: SQL Server 2008 (Publisher)

Server B: SQL Server 2008 R2 (Distributor)

Server C: SQL Server 2008 (Asynchronous Mirror)

Server A is setup for SQL Replication and for Database Mirroring. Mirror is going to DR site and replication is used for different purpose at local site. When database mirroring fails (Server C goes off-line or network issue or anything else) and I can see state on Server A (principal, disconnected). At that time replication failed until mirroring was turned off. My understanding was that High performance (asynchronous) database mirroring, should not prevent transaction from being committed on source. The only time transaction should not be committed to the source is when High Safety (synchronous) mode is on. Which would explain that replication not being able to pick up the changes as nothing is being committed on the source.

Is there any scenarios in which High-Performance (asynchronous) database mirroring can break database replication?


texts: What is the difference between (Superclass/Subclass) Total and Partial Specialization?

texts: What is the difference between total and partial specialization of superclass/subclass (or supertype-subtype) structures in enhanced entity-relationship modeling?


texts: high cached_page_count - when to start to worry and what can I do about it?

texts:
Below are my top 10 cached page counts.

We're having query performance issues with the top 2 tables; the queries never change but the run times are very volatile.

I cant seem to work out were the issues are and suspect through a bit of online digging its got something to do with available space within SQL Server itself but really have no clue where to best start the investigation.

We have seen a pattern in that the database is growing as expected daily and appears to be hitting its limit routinely forcing an extra GB to be added.

Yesterday we gave it a bit of extra overhead and it's 2 hours ahead of schedule which leads us to believe maybe when it's at its limit and is trying to pinch resources from elsewhere to re-allocate thus the reason for volatile build times rather than consistent build times.

We also noticed there is a Sunday job that runs to re-index fragmented indexes and frees up 40GB but within a day this is swallowed back up, so maybe something there to look into also.

╔════════════════════╦═══════════════════════════════════════╦══════════╗
║ cached_pages_count ║                 name                  ║ index_id ║
╠════════════════════╬═══════════════════════════════════════╬══════════╣
║            3507035 ║ CDI_OP_APPOINTMENT                    ║        1 ║
║            2588843 ║ CDO_OP_APPOINTMENT                    ║        1 ║
║            2035499 ║ __tblDataServicesEventDetail          ║        1 ║
║            1552008 ║ CDI_RTT_PATIENT_PATHWAY_EVENT         ║        1 ║
║            1369916 ║ CDO_RTT_PATIENT_PATHWAY_EVENT         ║        1 ║
║            1136325 ║ WRK_CDI_OP_APPOINTMENT_CODES_CHECKSUM ║        1 ║
║            1120941 ║ LZO_SCHEDULE                          ║        1 ║
║             941882 ║ LZO_CNSPATIENTCLINICALNOTE            ║        1 ║
║             890873 ║ CDO_CLINIC_SESSION_SLOT               ║        1 ║
║             848078 ║ CDI_CLINIC_SESSION_SLOT               ║        1 ║
╚════════════════════╩═══════════════════════════════════════╩══════════╝


texts: Why db2data folder changes its size (increasing and decreasing )?

texts:
we have DB2 9.7. It has almost 30 GB.
Data are on this /opt/IBM/db2data/ctginst1/NODE0000/DB_NAME/Txxxxxx

I noticed that every day during the day (suppose because of the workload) DB change its size (partition /opt/IBM/db2data)
For example used 26 GB, 27 GB, 28 GB, then it returns to 26GB.

I noticed that on Linux when using
df -h.
Available space grows and then returns to old value.
How is this possible? Is this regular behavior? Also only one TABLESPACE has over 24 GB. Others are smaller. Shouldn't they be splatted equally or not?

Data are not deleted from database so that is not the reason why DB increase/decrease!


texts:
I have a large XML file in the following format and I need to convert this file into a MySQL table. Please let me know how can I accomplish this?

~~~~~~~~~~

<host starttime="1392325468" endtime="1392325486"><status state="up" reason="user-set"/>
<address addr="192.168.0.2" addrtype="ipv4"/>
<hostnames>
</hostnames>
<ports><extraports state="filtered" count="2">
<extrareasons reason="no-responses" count="2"/>
</extraports>
<port protocol="udp" portid="22"><state state="open|filtered" reason="no-response" reason_ttl="0"/><service name="ssh" method="table" conf="3"/></port>
<port protocol="udp" portid="123"><state state="open|filtered" reason="no-response" reason_ttl="0"/><service name="ntp" method="table" conf="3"/></port>
</ports>
</host>

~~~~~~~~~~


texts: Does using Retry attempt option SQL Agent job create performance issue

texts: I want to use Retry attempt option of sql agent job, want to know does it hold the resource and will create performance issue, if i keep it every 60 minutes


texts: Trying to find a MIN() workaround for an indexed view

texts:
I'm trying to speed up a Azure V12 SQL Server query using indexed views. I have one slow element, which I think is due to the use of GUIDs for the particular comparison column. The GUID can be mapped to an ID, but it's one to many and I'd want the MIN(ID) and obviously can't put MIN in an indexed view.

I'm not a huge SQL expert and only just learnt about indexed views so wondering if there is a clever way to achieve what I want, changing the underlying database structure isn't an option at the moment.

I'm trying to convert the result of the following into an indexed view:

SELECT MIN(id), COUNT(*), guid
FROM table
GROUP BY guid

I tried a sub-select in the query but also hit the self-reference restriction for indexed views.

Rows per GUID can vary anywhere from 1 to 1000, give or take.

Appreciate any suggestions even if they are "this isn't possible", at least then I can stop trying and think about a future DB structure change!

I stuck the result of the query above into a table of its own with a suitable index. Changing my large query to join the tables on a BIGINT rather than a GUID dropped the query time from 8s to 6s, excluding the join altogether the query takes 4s.

This may not be huge but I'm thinking longer term as the data grows. Currently the query above generates approx 70K rows which are then joined with a table containing approx 500k rows.

For reference a simplified example of my original query which shows the issue is:

SELECT
    A.PageID,
    (SELECT COUNT(*) FROM tblPage B WHERE B.PageGUID = A.PageGUID) AS PageCount
FROM
    tblPage A
WHERE
    A.PageNumber = 1

I actually created a clustered index on a view which has the precomputed page counts on which the above query cleverly uses but with the GUID check the query takes 20+s and taking that line out it takes 4s. I would need to do a reasonable re-architect to change the GUID to an ID which would obviously speed things up and I'm out of creative ideas to speed up the GUID lookup which it's currently doing with a Hash Match join.

At this point I think I'm going to have to think longer term about changing the way the system does things so it doesn't need this GUID based lookup.

PageID is an identity column and for a given file all the pages would share a common GUID and currently on import we don't know the ID of the first page hence why the pages go in linked by the GUID, I used a GUID as there could be multiple apps inserting content concurrently so wanted to make sure things didn't clash.


texts: I was wondering if there was a way to see who created a table in SQL Server 2017.


texts: Impact of partition scan on large and small data

texts:
I have a table SampleTable which has a clustered index on Date column, and is also partitioned on Date column. However the Date column is of type DateTime.

While querying from this table i.e.

Select * from SampleTable where Convert(Date,Date) >= Convert(Date,GETDATE())-1

... I convert Date column to Date datatype. This makes my search predicate seek on all partitions.

However, changing the query to:

Select * from SampleTable where Date >= Convert(Date,GETDATE())-1

... causes the search predicated to seek only on partitions which qualify.

I wanted to understand the impact of the seek operations. Will the impact be linear or exponential when my data/date range increases?

The index is always being used.


texts: Monitoring blocking queries in SQL Server 2012

texts: have to monitor blocking queries in SQL Server 2012. How do we figure out which queries are the locked ones? Do I use the Activity Monitor? I want actual SQL statements.


texts:
I am working on a project in which the Admin does the following tasks
Admin creates an event template
Admin adds products to the template.
Admin adds customers
Admin can edit and customize each item added in step 2 as per the needs of the customer, that means new records will be added for each customers. Admin might also add a new item for a specific customer if needed.
I have added the following tables
    customers
    events
    products
    event_customers[FK to events and customers]
    eventtemplate_products [FK to event and product table]
    event_customer_products [FK to event, customers. It will also have all the fields of products table to keep customized values]


texts: Curious About SQL Server Registry Entries

texts:
I'm working on building some documentation across my systems and planned on simply using Powershell to script out the legwork of what I wanted to do. While that's going all well and good, I ran into an issue with my SQL Server registry entries. The problem being that some of my SQL servers have all of their registry values while some don't.

For example, I'm pulling down the SQL Shared Features entry so I know where that directory is on all servers. Now some of them are pulling data back (E:\yada\yada) while others aren't pulling anything back. After further investigation, I have noticed that several of my SQL servers don't have the required registration data saved within the registry. This happens with several different registry entries that should be there.

Any reason it's like this?