texts: How to write marker messages into the MySQL general_log?

texts:
I'm build a performance testing harness for our distributed web app. I'm using JMeter to drive it by simulating poorly performing parts of the app from the web side. I've set up MySQL to log to the general_log table. Now I would like to have JMeter send marker messages into the general_log so that, after identifying a poorly performing segment, I can fetch all of the queries run between markers surrounding that segment for further analysis.

I've tried sending an INSERT query on the table, but it always returns Error : You can't use locks with log tables. and my row is not created (but the INSERT query itself shows up).  I was hoping to send a customized message, but this doesn't work.

So, how do I insert a test run specific message into the general_log? Alternately, is there another solution? I was thinking of adding a table similar to the general_log table for my use, but we have a variety of deployments and being able to run this test on any of them without modification would be ideal.


texts:
The code I am using is here
CREATE TABLE timecard (
   id INTEGER PRIMARY KEY autoincrement,
   clock_in REAL not null,
   clock_out REAL,
   lunch integer,
   pay_type TEXT
);
INSERT INTO timecard VALUES(1,julianday("2021-02-15 08:00"),julianday("2021-02-15 16:00"),0,"HOLIDAY");
INSERT INTO timecard VALUES(2,julianday("2021-02-16 07:23"),julianday("2021-02-16 16:49"),1,"WORK");
INSERT INTO timecard VALUES(3,julianday("2021-02-17 07:16"),julianday("2021-02-17 16:05"),1,"WORK");
INSERT INTO timecard VALUES(4,julianday("2021-02-18 07:54"),julianday("2021-02-18 15:48"),1,"WORK");

.header on
.mode column

SELECT strftime('%W',clock_in) AS 'weekofyear',
       time(clock_in) as "IN",
       time(clock_out) as "OUT",
       CASE when lunch = 1 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 - .5 As REAL),2)
            when lunch = 0 then round(Cast (( JulianDay(clock_out) - JulianDay(clock_in)) * 24 As REAL),2)
       END as "total_time",
       sum("total_time")
FROM timecard
GROUP BY "weekofyear";

This is the output of the command.
weekofyear  IN          OUT         total_time  sum("total_time")
----------  ----------  ----------  ----------  -----------------
07          08:00:00    16:00:00    8.0         0.0

I am trying to get the total hours for the week of year.  Not sure as to what I am doing wrong.  I am new to the case statement in SQL and SQL in general.
Thanks for your time


texts: Do I need SQL_NO_CACHE for write intensive query?

texts:
When I establish a connection to DB, I have to write about 30,000 records with an INSERT statement. I've read in a book that I should do it !

Questions

Do I need to do SQL_NO_CACHE ?
Is it gonna increase performance and insert rate?


texts: When can a runtime exception occur during commit?

texts: I've been asked this question but neither I seem to be able to answer it on my own, nor can I find anything related on the web. So what are the cases that might cause a runtime exception when committing a transaction in Oracle?The only thing that I can think of is the low disk space. Are there any other?


texts: Maintenance plan: Confirmed server as primary but switched to secondary during backups?

texts:
While investigating a failed job that occurred over night I stumbled on to a novel situation that I thought was pretty interesting.

A server that was part of an AOA group was performing nightly backups. The first step is to determine whether the server was the primary or secondary. At the time the job began the server was the primary so it moved ahead to begin backing up the databases. While this was happening the server was bumped down to secondary and the backup jobs began failing as SQL won't allow full backups (without COPYONLY). This then lead to an alert from our monitoring solution.

Is it possible to continually check if the server is primary while performing backups?


texts: Is it better to have large tables or many tables (MySQL)

texts: In terms of performance, is it advisable to have only a few tables with a lot of rows or a lot of tables with only a few rows? In my instance, I have groups that I am keeping data separately for. Is it better to create a new table for every group or keep all the information for the groups in one table (that will get very, very large) and have a column that identifies which group it belongs to? Which version would be better in terms of scalability? I am using PHP to access the data and I only need to get data for one or two groups per page load. I am mainly concerned with the speed of the system when there is a lot of data being stored. Thanks.


texts: Execution plan using index that no longer exist

texts:
I have some odd behavior with an execution plan using an index that no longer exists. let me give you the background.

We had some high CPU going on so I had a look what was happening and found a function that was responsible. this function was doing a RID lookup and a table with 200,000,000 records in.  Because of space, we can not just create a clustered index on this table, so I wanted to create a nonclustered covering index for the query.

the query was using a nonclustered index already. So I dropped this index and created my covering index and the function started using it (which is great) CPU came down and all was good.

BUT after a few minutes (and the function fire pretty much every few seconds in working hours) I found that the function was using the old plan (with the index that no longer exists) and CPU went back up.

So I flushed the plan using DBCC FREEPROCCACHE (My Plan Handle);
which worked the function was then using my covering index and the new plan. But again after a few minutes, it started using the old one.

So I mark the function to be recompiled with EXEC sp_recompile N'My function';
again this worked the function was then using my covering index and the new plan. But again after a few minutes, it started using the old one.

Now it's using both and I am stuck with was to try and I'm not sure what is happening?

any advice or pointers would be great.

I'm using SQL Server 2014 Enterprise (12.0.5546.0)


texts: Secondary Clustered DTC will not work when Primary goes offline - SQL Server Issues

texts:
I've been working on setting up clustered environment with a two new DTC roles, DTC1 and DTC2. I set up DTC1 which allows both clustered VM's and SQL Server Instances to function correctly. However, when I disable DTC1, DTC2 will remain online but no DTC transactions will work. I'm actually going through this issue with a Microsoft tech right now (without much luck). Both DTC services are set up with similar configurations. Anyone here have an idea as to what might be going on?

Here's my setup,

One clustered environment Two DTC Roles Two Separate ISCSI disks, one for each role Two VM's (same configuration) Two SQL Server Instances, on on each VM

Networking configuration is allowed, everything looks to be set up properly.


texts: What databases or styles of database can use git (or another dvcs architecture) to manage cross-site replication?

texts:
I'm looking for a database software package for a dictionary (like the hunk(s) of dead tree that you might have somewhere at home).  I would like the database to

scale well to a million key-value pairs.  The pairs would normally be fewer than 20 characters, and the normally fewer than 1000 characters.
have perfect UTF-8 support.
deal with distributed version control.  I would like it to deal well with having lots of branches and doing merges of branches.  This could be built-in or provided independently by something like git.
have a well-supported interop with Ruby.  I plan to write a desktop program in Ruby that talks to the database.  (Ruby would also work for writing the web part if it ever happens.)
be fairly responsive (though I would like it to be fast).
deal well with changes both while just on a desktop and while providing data for something on the web.  (Hopefully this project will get to the web part.)
be free software (as defined by RMS) and to be available at no charge.

All of this data would be entered by hand.

It wouldn't hurt if there were something like github for this database.  I don't know if there is a database for which someone provides something like that, though.

No one's job, career, or company rides on this decision.

Any suggestions?


texts: Running a DELETE stored procedure but finding degradation in performance in WHILE loop as iterations increase

texts:
Have a quick general question. I have a table I'm trying to purge a table. I'm deleting using a WHILE loop with WAITFOR DELAY time of 50ms between batches and 2000 records per batch. The real issue is that as time progresses the number of records deleted drops over time. Refer to the following:

Minute Number | Number of Records Deleted:

            1 | 162,000
            2 | 116,000
            3 |  80,000
            4 |  72,000
            5 |  62,000
            6 |  38,000
            7 |  38,000
            8 |  34,000
            9 |  20,000

Wondering if there is something fundamental about using WHILE loop batches for deleting records that causes the performance to degrade with each iteration of the loop. we have been manually watching the performance and then stopping the proc as the performance begins to drop drastically around the 5th to 6th minute and then restarting the deployment again. We don't think its a locking issue directly because as we play with the batch size the performance is always dropping at around the 5 to 6 minute mark.


texts:
I have two tables, shown below, with the following columns:

[table #Completed]
Client_ID, ClientName, FirstDeliveryStatus, FirstDeliveryDate

[table #Planned]
Client_ID, ClientName, NextDeliveryStatus, NextDeliveryDate

As you can see clients have some completed & planned deliveries. I'm trying to merge both tables to show for each patient :

Client_ID, ClientName, FirstDeliveryStatus, FirstDeliveryDate, NextDeliveryStatus, NextDeliveryDate

I have tried to select data from table Completed and outer/inner join table Planned but it does not realy work for me. Main issue is row 6[Ashton] who have never had any completed deliveries & once I join details about his planned delivery - i'm getting nulls on Client_ID & ClientName

I would expect to see output as per below visualization

Any help would be appreciated


texts: Update value in table based on condition in other table with trigger

texts:
I have 2 tables, first stores the details of all the races 1:  and second the users who have joined which races 2:

I want to update the race_status to Full and record_status to 2 in race table, if race_id count in second table is 5 per race_id. I have created a MySQL trigger for it here is the trigger code for it:

CREATE TRIGGER `user_race_count` BEFORE INSERT ON `tbl_map_race`
    FOR EACH ROW BEGIN
    DECLARE a1,a2 INT;
    SELECT tbl_map_race.race_id, COUNT(tbl_map_race.race_id) INTO a1,a2
    FROM `tbl_map_race`
    GROUP BY tbl_map_race.race_id;
    IF ((a1 = NEW.race_id) AND (a2>=5))
    THEN
        UPDATE tbl_race
        SET tbl_race.race_status = 'Full', tbl_race.record_status = '2'
        WHERE tbl_race.race_id = @r_id;
    END IF;
END

In the trigger tbl_race is the first table and tbl_map_race is the second table.
Trigger is successfully getting created but when I run query to join the race with trigger working behind I'm not able to update values in first table, but if I drop the trigger and run the query I'm able to insert a record in second table. Any help to this would be appreciated. Thank You.


texts: Simple scenario needs to go from Unnormalized Data to 3NF - Review Please

texts:
NOTE: The Question has been updated. View Edit Revisions to view original question.

The scenario. An animal charity that at the moment, track their establishment's costs via a spreadsheet like so:

[broken image link removed]

1NF:

The above table is already in 1NF because it abides to the definition of 1NF.

  First Normal Form: A relation in which the intersection of each row and column contains one and only one value.

After reading Catcall's answer I can see that it is in determining my dependencies that I messed up. All I did originally was exactly like Catcall said ! added a primary key column, and said to myself "there, that fixes that!"

So obviously that was wrong. I have now read more carefully into functional dependencies and in this book I have, it defines them as:

  Functional Dependency: Describes the relationship between attributes in a relation. For example if A and B are attributes of relation R, and B is functionally dependent on A (denoted by A --> B), if each value of A is associated with exactly one value of B. There exists a functional dependency.

Keeping the above definition in mind, and examining my now expanded (more data) sample table, I can see that PetHouse Ltd is not always going to be associated with Dog food, and this breaks the part of definition about A being associated with EXACTLY one value of B.

So I guess in reality I am stuck here in identifying my functional dependencies. Here I am a little bit in the dark as I've never worked with a composite key that was made up of three different columns, but I'll go ahead anyway and see where I get to.

Catcall suggested using 3 columns [Name, BoughtFrom, TimeBought] as identifiers of data (i.e. a composite key for the table that is made up of 3 columns) and that makes sense as that would uniquely identify each row of data in the table. So the notation for the relation becomes:

IncuredCosts (Name, BoughtFrom, TimeBought, Cost)

The book in my possession also has this interesting part about the functional dependencies we need for normalization. It states that the functional dependencies we are after must have the following characteristics:

There is a one-to-one relationship between the attribute(s) on the left-hand side (determinant) and those on the right-hand side of a functional dependency. (Note that the relationship in the opposite direction can be a one-to-one or one-to-many relationship.)
They hold for all time.
The determinant has the minimal number of attributes necessary to maintain the dependency with the attribute(s) on the right-hand side. In other words, there must be a full functional dependency between the attribute(s) on the left-hand and right-hand sides of the dependency.

I believe all the above characteristics hold true for this functional dependency:

  (Name, BoughtFrom, TimeBought) --> Cost

Now then, Transitive Dependencies, which in the book (could be wrong like Catcall said) is defined as:

  Transitive Dependency: A condition where A, B and C are attributes of a relation such that if A --> B and B --> C, then C is said to be transitively dependent on A via B (provided that A is not functionally dependent on B or C).

Am I right in thinking that my relationship as defined earlier does not have any transitive dependencies? But then again, if that the case, it would imply that I have reached 3NF! cause apparently the major step to 3NF is the removal of Transitive Dependencies into their own relation.

Have I really reached 3NF?

Thank you by the way. I learnt a lot through this post.


texts: How to sync the data between two identical DB

texts: The thing is I've two identical databases, one is in my localhostserver at my laptop and the other one is on the office main server. Is there any automatic way or software that can synchronize the data between these 2 identical databases? I'm working with SQL Server 2008 R2.


texts: TFS Installation Fails over to mirroring secondary despite not having a Fail-over partner specified

texts:
We have TFS Version 15.105.27412.0 installed on a server (Server A) and have it configured to point at our database server (Server B).

We have the two TFS databases (tfs_defaultcollection and tfs_configuration) on ServerB and they are mirrored with ServerC, however the connection string in the configuration manager for TFS is not "mirroring aware" - see screenshot below from TFS Administration Console

Furthermore, if I interrogate the TFS configuration database as follows

SELECT * FROM Tfs_configuration.dbo.tbl_Database

This confirms the connection strings for both databases do not have failover partners specified.

We had a planned failover this morning from ServerB to ServerC which included both of the TFS databases and much to everyone's surprise, TFS continued to function with the databases failover and running on ServerC.

I stopped the SQL Server service on ServerB and ran an ExtendedEvents session on ServerC (looking for queries that accessed the TFS databases) and can confirm that the TFS application was indeed querying ServerC.

How did this happen? The TFS installation has no idea that ServerC exists, is something happening at the SQL Server level?


texts: Oracle DBMS Scheduler - what if database fails?

texts:
We are moving some Oracle overnight jobs off a cron server and running them via Oracle DBMS Scheduler instead. It is my understanding that if the database fails overnight then the job email notification will also fail.

Has anyone got a method of emailing users to let them know their specific overnight jobs failed in this instance (without letting them know the entire database dropped)?  It seems a slight limitation of using dbms scheduler over a cron server.

I was thinking if there was a way to monitor a gmail mailbox and if a job success email from the scheduler is not received to then send a job fail message to the users?  Couldn't see a way to do this either unfortunately.

Does anyone have any ideas for this scenario?  Thanks very much.


texts: SQL Server 2012, Network for: AlwaysOn Availability Group with AlwaysOn AlwaysOn Failover Cluster Instances

texts:
Setup: I have two data centers. In site1, my primary site, I'd like to deploy an AlwaysOn Failover Cluster Instance (FCI). In site2, my data recovery site, I'd like to deploy a single-node instance of SQL. I'd then like to use AlwaysOn Availability Groups (AG) to replicate data between site1 and site2.

I've read a lot of blog posts and TechNet articles. It's clear to me that automatic fail over is not supported for AGs with FCI members [1]. It's also clear that FCIs support nodes on multiple subnets [2]. Apparently this is achieved by updating the Virtual Network Name (VNN) in DNS.

Question: Do AGs support multiple subnets, or do I need a spanned subnet between site1 and site2? I haven't been able to find any sources that explicitly make that clear.

References:

[1] Failover Clustering and AlwaysOn Availability Groups (SQL Server)
http://msdn.microsoft.com/en-us/library/ff929171.aspx

[2] AlwaysOn Failover Cluster Instances (SQL Server)
http://msdn.microsoft.com/en-us/library/ms189134.aspx


texts: Any option for mysqldump to ignore databases for backup?

texts:
We have 40 databases in our server.

We want to take 36 databases backup using mysqldump. How can i ignore remaining 4 databases in mysqldump command? Is there any option for mysqldump to ignore databases for backup in MySQL?

I know the general mysqldump command but it is very lengthy. I want to ignore only 4 databases and need to take remaining dbs backup.


texts:
My Oracle SYSAUS Space usage, how do I clean it up? Please Help

Server Manageability - Advisor Framework      29,093.44 SYS
Server Manageability - Optimizer Statistics History    1,738.50 SYS
AUDSYS schema objects      1,111.88 AUDSYS
Unified Job Scheduler        135.88 SYS
Server Manageability - Other Components       62.38 SYS
XDB       62.25 XDB
Server Manageability - Automatic Workload Repository          27.75 SYS


texts: NTFS filesystem fragmentation & other influence vs database files

texts:
As probably everybody knows, NTFS has a flaw in design which causes fragmentation during filesystem usage. On the other side, MSSQL uses files as database storage method, and what is more, typical practice is to put several, big, databases on the same filesystem.  Database grow gives perfect example of fragmentation, and it definitely has some influence on SQL server performance.

Question: is there any information about fragmentation of filesystem influence on database/MS SQL Server performance? Or there are  hidden proprietary layers of optimisation of NTFS for MS SQL Server database storage? Are SQL server databases really ordinary files on NTFS or OS treated it as special objects?
What if disk checkdisk working on NTFS changes some filesystem structure? In typical situation it may change content of the filesystem data ( once I lost Oracle database during such, automatic after BSOD reboot check. Yes, it was on physical raid. Yes it was automatic checkdisk filesystem reparation which destroyed rollback logs). Is there any know safety mechanism preventing such accident for MS SQL to occur?
There are know technologies of raw partition storage, but MSSQL don't have such technology build in.

I do not want to start flame war on obvious statements, question is about technical details and about relationships between two addopted by MS products: NTFS and MS SQL Server.


texts:
I have a table called country:

CountryName | Sales
A             200
B             100
C             300
D             500
E             400

Now I in my report I have hardcoded the parameter of region that the user will select, such as RegionEast, RegionWest etc.

My requirement is that if I select RegionWest then I should aggregate countries (A, C). If (B, D) then RegionEast. There is no explicit relationship of a region with a country in the database.

I was trying to write my query like this:

Case When Parameter ='RegionWest' Then Country in (A,C)
     when Parameter ='RegionEast' Then Country in (B,D)
 Else Country

But that is not working as expected. Can somebody help?


texts: How to pass environment variables to a sql file from bash shell (PostgreSQL)

texts:
I need to be able to pass environment variables, from a bash shell executing a .sql file using psql.
The psql command I am running is:
su postgres -c "psql -v ON_ERROR_STOP=1 -v dbname=example -v dbuser=example -e" < ./create-db.sql

Where I have put example is usually set to $DATABASE_NAME and DATABASE_USER respectively which is set in the bash script before executing the psql command.
The create-db.sql file is:
\connect postgres
DROP DATABASE IF EXISTS :dbname;
DO $$BEGIN
    CREATE USER :dbuser;
EXCEPTION WHEN duplicate_object THEN
    RAISE NOTICE 'user already exists';
END$$;
ALTER ROLE :"dbuser" SET search_path TO :"dbname",public;
CREATE DATABASE :"dbname"
    OWNER=:dbuser
    ENCODING=UTF8
    LC_COLLATE='en_US.UTF-8'
    LC_CTYPE='en_US.UTF-8'
    TEMPLATE=template0;
\connect :"dbname"
CREATE SCHEMA zulip AUTHORIZATION :"dbuser";

The variable substitution in the line DROP DATABASE IF EXISTS :dbname; works, but everything after fails at the CREATE USER :dbuser; line fails and I have tried it as each of the following ways:
CREATE USER :dbuser;
CREATE USER :'dbuser';
CREATE USER :"dbuser";
And it always errors with:
You are now connected to database "postgres" as user "postgres".
DROP DATABASE IF EXISTS example;
NOTICE:  database "example" does not exist, skipping
DROP DATABASE
DO $$BEGIN
    CREATE USER :dbuser;
EXCEPTION WHEN duplicate_object THEN
    RAISE NOTICE 'user already exists';
END$$;
ERROR:  syntax error at or near ":"
LINE 2:     CREATE USER :dbuser;
                        ^

I'm really not sure where I'm going wrong with the above. Output of psql -V is psql (PostgreSQL) 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)


texts: Stored Procedure produce data for a range of dates

texts:
I have this table:

PRJ_ID | START      | FINISH
xxxx   | 2015-01-01 | 2016-01-01

I have two separates date start_range and end_range.

I would like to produce the following output using a stored procedure in SQL Server 2008 for example with this parameters start_range=2015-02-01 and end_range=2016-08-01:

PRJ_ID |   DATE     | STATUS
xxxx   | 2015-02-01 | ACTIVE
xxxx   | 2015-03-01 | ACTIVE
xxxx   | 2015-04-01 | ACTIVE
xxxx   | 2015-05-01 | ACTIVE
.
.
.
xxxx   | 2016-08-01 | NOT ACTIVE

Where the DATE field should be the first day of the month for each month from the start_range to the end_range


texts: Third Normal Form: Composite PRIMARY KEY vs System-Generated Surrogate (IDENTITY)

texts:
I’m working on a data modeling project and I’m trying to figure out the best data modeling approach for a history table, which has only four columns:

CREATE TABLE FooHistory
(
    SecurityID  INT (FK),     -- Part of the natural PK.
    FieldID     INT (FK),     -- Part of the natural PK.
    DateCreated DATETIME2(0), -- Part of the natural PK.
    Value       VARCHAR(50)
);

The natural composite KEY in this table would be (DateCreated, SecurityId, FieldID), and every 30 minutes the ETL process will add ~ 2K rows to this table.

Questions

Pros and cons of declaring a composite PRIMARY KEY (PK) (DateCreated, SecurityId, FieldID) vs. adding a new IDENTITY column (that is, a system-generated surrogate) and use it as PK?
I believe that, if I add an IDENTITY column and use it as PK, then this table won’t be in Third Normal Form (3NF), because there will be a functional dependency between non-PK columns, that is, (DateCreated, SecurityId, FieldID) and Value.
Since this table keeps historical data I don’t expect to JOIN this table to other external tables, and applications will mainly interact with it using SELECT statements. Based on these assumptions is it worth it to keep the table in 3NF and declare a composite PK, or I should add the IDENTITY column to this table?


texts: How do I assign an entire Active Directory group security access in SQL Server 2008?

texts: I would like to use integrated security with my internal application which is all on a domain.  Unfortunately, I've never been able to get this to work well.  I would like to assign an entire Exchange (Active Directory) Group a role in SQL Server for read/write access to certain tables.  That way I wouldn't have to create an operator whenever someone is hired or delete an operator whenever someone is fired.  Is this possible?  What steps would I take to do this?


texts:
Im trying to do some general script that i can use to run the same INSERT/UPDATE or DELETE statements on different site databases.
This was my first attempt which works just fine, but i have to copy and paste everything.

:CONNECT czasql-001
USE [Lps_FinishPack_Cz]
GO
SELECT * FROM [config].[LpsPlant]

GO

:CONNECT LS_LPS_DK
use [LPS_FINISHPACK_NY]
GO
SELECT * FROM [config].[LpsPlant]

use [LPS_FINISHPACK_DK]
GO
SELECT * FROM [config].[LpsPlant]

use [LPS_FINISHPACK_SUPPLIER]
GO
SELECT * FROM [config].[LpsPlant]

GO

:CONNECT LS_LPS_372
use [LPS_FINISHPACK_MO]
GO
SELECT * FROM [config].[LpsPlant]

GO

:CONNECT LS_LPS_678
use [LPS_FINISHPACK_678]
GO
SELECT * FROM [config].[LpsPlant]
GO

But i like to have something where it's more general, were i don't have to copy and paste. I have tried something like this:

 DECLARE @tbl TABLE (Id int IDENTITY (1, 1),ServerName sysname, DbName sysname, IsDone BIT DEFAULT(0))
 INSERT INTO @tbl (ServerName,DbName) VALUES (N'CZASQL-001', N'[Lps_FinishPack_CZ]')
 INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK',  N'[Lps_FinishPack_DK]')
 INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK',  N'[Lps_FinishPack_NY]')
 INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK',  N'[Lps_FinishPack_Supplier]')
 INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_372', N'[Lps_FinishPack_MO]')
 INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_678', N'[Lps_FinishPack_678]')

  WHILE (SELECT COUNT(*) FROM @tbl WHERE IsDone = 0) > 0
    BEGIN

    DECLARE @selectedRow INT = (SELECT TOP 1 Id FROM @tbl WHERE IsDone = 0)
    --DECLARE @ServerName NVARCHAR(50)= (SELECT ServerName FROM @tbl WHERE Id = @selectedRow)
    --DECLARE @DatabaseName NVARCHAR(50) = (SELECT DbName FROM @tbl WHERE Id = @selectedRow)
     DECLARE @ServerName sysname= (SELECT ServerName FROM @tbl WHERE Id = @selectedRow)
    DECLARE @DatabaseName sysname = (SELECT DbName FROM @tbl WHERE Id = @selectedRow)

    --:SETVAR DatabaseName @DatabaseName
    --:SETVAR ServerName @ServerName
    print CONVERT(NVARCHAR(100),@selectedRow)
    :CONNECT @ServerName

       USE @DatabaseName
       GO
      SELECT * FROM [config].[LpsPlant]
     GO

     UPDATE @tbl SET IsDone = 1 WHERE Id = @selectedRow
END;

I have also tried where ServerName and


texts:
I have the following tables:

create table dbo.Places (
  Id int identity not null primary key clustered (Id),
  Name nvarchar (100) not null
)

create table dbo.Posts (
  Id int identity not null primary key clustered (Id),
  Name nvarchar (100) not null
)

create table dbo.Users (
  Id int identity not null primary key clustered (Id),
  Name nvarchar (100) not null
)

I would like to register Votes made by Users in Places and Posts.

Each Vote has one Value and the CreatedDate so I can track the votes by time.

Which scheme would you advice for this case? A two table scheme:

create table dbo.PlacesVotes (
  PlaceId int not null,
  UserId int not null,
  CreatedDate datetime not null,
  Value int not null
)

create table dbo.PostsVotes (
  PostId int not null,
  UserId int not null,
  CreatedDate datetime not null,
  Value int not null
)

Or three tables:

create table dbo.PlacesVotes (
  PlaceId int not null,
  UserId int not null,
  VoteId int not null
)

create table dbo.PostsVotes (
  PostId int not null,
  UserId int not null,
  VoteId int not null
)

create table dbo.Votes (
  Id int not null,
  CreatedDate datetime not null,
  Value int not null
)

Where PostsVotes and PlacesVotes work only as a junction table.

In this moment I am voting only Places and Posts but I will need to add votes to other tables in the future.

A user can vote for multiple places and multiple posts. However can only vote once for each place or each post.


texts: Is this Normal SSIS Foreach Loop Container behavior

texts:
I have a Foreach Loop Container that loops through files in a folder. I thought the Loop Container would only look at the files 1 time but apparently it will start back at the top and pick up a file that it had already seen again.

I have a script in my loop container that will process the file if it meets certain criteria. If it does not then it will simply skip the file.

The files that get processed are moved to another folder. The problem I am having is that once it goes through all the files it starts back up at the top of the list of files so it will just indefinitely continue to loop if there was a file that was skipped and not moved out to a different folder.

I know I can make it so that instead of skipping the file my process moves the file to another folder so that the foreach loop container can finish looping and the package can complete but I just thought I would double check first to see if there was a way of just having the loop container loop thru the files once so that if a file was already identified it would not be detected again by the loop container.


texts: Querying sys.server_principals does not return results for specific user

texts:
I'm trying to figure out why this query:

SELECT * FROM sys.server_principals WHERE sid = SUSER_SID('Domain\User') OR name = 'Domain\User'

Does not return results for one specific user? At the same time if I remove/comment out WHERE clause I can see that SELECT Domain\User... I've tried to remove and re-add this user under SQL Server > Security > Logins - but it does not help. Can someone indicate what can be wrong here? User name contains non-Latin characters, but no special characters.


texts: Using SQL Server Reporting Services for monitoring database performance

texts:
I'd like to know whether it is a good idea to use SSRS for db monitoring or not? I want to check different parameters of a db such as Deadlock, page splits, and page reads/sec to improve db performance.
Any comments would be appreciated.


texts: How to make a Root to get access to the MariaDB server, trough a client app like MysqlWorkbench?

texts:
I am running a brand new Ubuntu 16.04 virtual machine to perform some testes with MariaDB AND Nginx AND PHP7.

When I was installing MariaDB (trough apt-get install mariadb-server) I was not asked to set a password for root user, like it is when we do install MySql-server.

Then I installed MySql Workbench as a client to MariaDB server.

When I try to connect to the db server as a root with no password, I have an error like the picture below:

As you can see in the picture below, the root user has no password, and I have created another user with no password using this syntax: CREATE USER 'ig2'@'localhost' IDENTIFIED BY ''.

So now, when I try to to connect the just created user ig2 with no password I have a warning and a successful connection.

So, I sit a password to the root user with this syntax: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123'); and now the root has a encrypted password.

If I try to connect again with the root and his password

And I still have the same connection errror for the root user:

If I set the same password for the user ig2, using the same syntax used to sit root's password, I can have a successful connection.

And it is weird when I try to connect trough the terminal:

The root user can connect with or without password.

In resume, I don't know what is happening, and I don't know how to make the root to get access to the server, trough a client application like Workbench.

Does someone can explain me?


texts: SSDT Publish Window: What does checkbox ENABLE mean?

texts:
In the Visual Studio 2015 SSDT "Advanced Publish Settings" Window, Tab General, "Advanced Deployment Options" there are several checkboxes in the column "ENABLED" we can check or uncheck.

Most of the descriptions in there (that are also documented here) say something like this:

  "Specifies whether differences in the database collation should be
  ignored or updated when you publish to a database."

Now, it's completely undefined what exact consequences checking this box has: ignored OR updated? Does it ENABLE IGNORE or ENABLE UPDATE?

Does anyone know?

It is so annoying that people create software and never ever notice that their wording is misleading and unclear...


texts: How can I correct MySQL replication while using federated tables?

texts:
This is an overview of what I have going
I have 2 server A (10.0.4.22) AND B (10.0.4.29)
A is a replication of B and B is a replication of A (yes it is a ring replication)

ON A I have 2 table uat and ge.
uat have all Innodb engine base tables NO federated tables.
And a database called ge have many InnoDB table and some FEDERATED tables. These federated tables are pointing to the database uat on the same server (A via a connection called accordion)

Now, I have created a connection on A like so

CREATE SERVER accordion
FOREIGN DATA WRAPPER uat
OPTIONS (USER 'fed_user', HOST '10.0.4.22', PORT 3306, DATABASE 'uat', password 'myPass');

Since the statement CREATE SERVER is not replicated due to FEDERATED table limitation I had to create it manually on B as well like so

CREATE SERVER accordion
FOREIGN DATA WRAPPER uat
OPTIONS (USER 'fed_user', HOST '10.0.4.29', PORT 3306, DATABASE 'uat', password 'myPass');

Please notice that both connections have different IP addresses. each server points to itself.

The issue is that the replication breaks. I tried to execute

1) start the server using SKIP-SLAVE-START
2) SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
3) START SLAVE;
4) 5 seconds later MySQL service stop working.

When I look at the .err file I don't see any sine of crash.

I also tried turning on core-file but there was no core file created. It is a silent crash I am not sure how to figure out what is causing this issue? What Can I do to fix the problem and bring the replication back up?

Here are my theory.
When I issue an update statement on a federated table located on A then the server will make the update to the table after connecting to the remote table using the first accordion string I created.

But when the the replication tries to make the update on the SLAVE "B" it sees duplicate records so the replication breaks. But the only thing that is not adding up is ROW REPLICATION. since I am using ROW base replication then the update query itself is not replicated but the row changes are applied to the slave. However, if I don't have accordion string on the slave. The slave will give an error. which tells me that the query itself may be replicated and not the ROW changes.


texts:
I have a simple stored procedure as an example, which reads a table with 3 rows.

CREATE PROCEDURE demo_procedure
AS
    BEGIN
        SELECT * FROM my_table
    END

When I run it with time statistics on:

SET STATISTICS TIME ON
GO
exec demo_procedure
GO

I get:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 2 ms.

(3 rows affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.

How should I read this data? Does the stored procedure takes 2 ms, 3 ms or 5 ms?


texts: how to do select more then 3 table add in select IN() function

texts:
SELECT M.post_id, M.uid_fk, M.message, M.image1,M.roles, M.created,U.uname, U.f_name, U.l_name
 FROM posts M, person U
 WHERE M.uid_fk=U.p_id
    AND M.uid_fk in (
      (select send_id from friend_request where p_id = '7' AND status=1)
        or
     (select p_id from friend_request where send_id = '7' AND status=1)
        AND
     (select send_id from follower_request where p_id = '7' AND status=1 AND M.uid_fk = '7')
        AND
     M.uid_fk = 7
  )
 order by M.post_id DESC

and made by i can't got current data.

how to i get like this query

SELECT M.post_id, M.uid_fk, M.message, M.image1,M.roles, M.created,U.uname, U.f_name, U.l_name
 FROM posts M, person U
 WHERE M.uid_fk=U.p_id
  AND M.uid_fk in (1,2,3,4,7,8,9)
 order by M.post_id DESC


texts:
I would want to tag many queries and mostly materialised view refreshing tasks as low priority in my RDBMS, in the fashion I run the web browser on the local machine as low priority (because JS, CSS, animated GIF do not deserve CPU time slices), the same way I do for C/C++ software builds, using a nice on Darwin and Linux supervisors.

Can I do that?

Can I do that in vanilla PostgreSQL?

Could I even couple initial job prioritisation with increasing to normal priority after a given timeout? Highways as real world analogy: in a normal situation there are slow traffic lanes and fast traffic lanes; but in a dense traffic situation all lanes get relatively slow, then unless for a very specific exceptional emergency services job that appropriates the lane.

Is this an oversight for the need of another family of DBMS (o-o, columnar, dwh...)?


texts: How Create a directory dynamically and output a json file to the directory with PL/pgsql?

texts:
I want to export updated records to json files. For this purpose I defined a trigger as follow:

CREATE OR REPLACE FUNCTION test() RETURNS trigger AS
$BODY$

DECLARE
    directory text := 'dblogs/me.json';
    jsonText text;
    copy record;
BEGIN
    directory := getNextId() || '.json';
    copy := NEW;
    jsonText := row_to_json(copy, true)::text;

    PERFORM pg_catalog.pg_file_write(directory, jsonText, true);

    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql
SECURITY DEFINER;

CREATE TRIGGER last_name_changes
  AFTER UPDATE
  ON fava_config
  FOR EACH ROW
  EXECUTE PROCEDURE test();

getNextId method, get directory, If directory don't exist, it create directory and the get directory. Its as follow:

CREATE OR REPLACE FUNCTION getNextId() RETURNS VARCHAR(200) AS
$BODY$

DECLARE
    mfId integer;
    rootDir varchar(50) := 'mfdata/';
    createDir text := 'mkdir --mode=777 -p mfdata';
    dir varchar(100);
BEGIN
    INSERT INTO history(time) values(now()) RETURNING id INTO mfId;
    EXECUTE('COPY (SELECT 1) TO PROGRAM ' || quote_literal(createDir));
    RETURN rootDir || mfId;
END;
$BODY$
LANGUAGE plpgsql;

When I update a record in fava_config, It occur an error:

How Can I Fix the Problem?


texts:
I currently run a Windows Server with SQL Server Express. I want to follow best practice and separate the application from the database, so I've created an RDS instance and exported my database.

But... it's painfully slow. I'm running a test query from my local machine that selects 12000 rows - it takes less than 1 second against my original setup but 16 seconds against RDS. Surely it can't be question of specification; my small RDS instance actually has a better spec. than my Windows Server.

What could be going on here?


texts: MySQL Daemon failed to start - Can't find file: './mysql/plugin.frm'

texts:
Fresh install of CentOS 6 on a test box, ran following as root:

yum install mysql-server    #success
service mysqld start        #success
service mysqld stop         #success
cd /var/lib/
cp -rp mysql mysql.bak
rm -rf mysql
cp -rp mysql.bak mysql
service mysqld start

MySQL Daemon failed to start.

cat mysqld.log

/usr/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)

ls -l
drwxr-xr-x. 4 mysql   mysql   4096 May  1 13:26 mysql
drwxr-xr-x. 4 mysql   mysql   4096 May  1 13:23 mysql.bak

Directories are the same

diff -r mysql mysql.bak

No differences

ls -lR mysql > out1
ls -lR mysql.bak > out2
diff out1 out2

No differences

I also looked manually and every file in mysql vs mysql.bak has the same ownership mysql:mysql & same permissions.

Ive seen the previous SO issue like this, but that didn't help.

Any ideas?


texts: SQL Calculated Field in both SELECT and GROUP BY clause

texts:
Often in querying my MS SQL Server databases, I need to create a calculated field, such as this

(CASE WHEN A.type = 'Workover' THEN 'Workover'
      ELSE (CASE WHEN substring(C.category, 2, 1) = 'D' THEN 'Drilling'
                 WHEN substring(C.category, 2, 1) = 'C' THEN 'Completion'
                 WHEN substring(C.category, 2, 1) = 'W' THEN 'Workover'
                 ELSE 'Other'
            END)
END)

and then I need to group my results by this calculated field (among others).  Hence, I have the same calculation in both the SELECT and GROUP BY clauses.  Is SQL server actually performing these calculations twice, or is it smart enough to only do it once?


texts: How can I load 'utf8' into plperl by default?

texts:
I am attempting to solve a problem explained in another question here.  On Debian 7.4 with Postgres 9.1.12, I am able to use the Perl qr operator just fine.  On Solaris 5.11 with Postgres 9.2.4, however, I am not able to.

As detailed in the question above, the following stored procedure fails on the Solaris installation:

REATE FUNCTION foo(VARCHAR) RETURNS VARCHAR AS $$
    my ( $re ) = @_;
    $re = ''.qr/\b($re)\b/i;
    return $re;
$$ LANGUAGE plperl;

With the following error:

ERROR:  Unable to load utf8.pm into plperl at line 3.
BEGIN failed--compilation aborted.
CONTEXT:  PL/Perl function "foo"

It is apparent that in the Debian installation of Postgres, 'utf8' is already loaded by default. I ran the following stored procedure on both systems:

CREATE FUNCTION perl_modules() RETURNS VOID AS $$
    warn join(', ',sort keys %INC);
$$ LANGUAGE plperl;

On the Debian system:

  WARNING:  Carp.pm, Carp/Heavy.pm, Exporter.pm, feature.pm, overload.pm, strict.pm, unicore/Heavy.pl, unicore/To/Fold.pl, utf8.pm, utf8_heavy.pl, vars.pm, warnings.pm, warnings/register.pm at line 2.

On the Solaris system:

  WARNING:  Carp.pm, Carp/Heavy.pm, Exporter.pm, feature.pm, overload.pm, overloading.pm, strict.pm, vars.pm, warnings.pm, warnings/register.pm at line 2.

I tried to load the utf8 module on the Solaris system by adding this to the postgres config file:

plperl.on_init = 'use utf8; use re;'

And that successfully loads the utf8 and re modules, as shown here:

  WARNING:  Carp.pm, Carp/Heavy.pm, Exporter.pm, XSLoader.pm, feature.pm, overload.pm, overloading.pm, re.pm, strict.pm, utf8.pm, vars.pm, warnings.pm, warnings/register.pm at line 2.

However the foo() stored procedure still fails:

dc=# select foo('foo');
ERROR:  Attempt to reload utf8_heavy.pl aborted.
Compilation failed in require at /opt/perl-5.18.0/lib/utf8.pm line 17.
CONTEXT:  PL/Perl function "foo"

What is the secret to successfully loading utf8, and its dependencies, in this Solaris environment?


texts:
The purpose of this question is to document my answer to what seemed to be a strange issue when changing the target SQL Server version of an SSIS package created in some older versions of SSIS prior to 2012 that use script tasks.

An SSIS package originally created in BIDS 2005 or 2008 had no issues when upgraded through the various versions of BIDS/SSDT over the years. Even now when the package was upgraded to SSDT and targeting SQL Server 2016 everything still worked as it always has in addition to being able to take advantage of the latest SSIS features (package parts, project parameters, etc.) and deploying the package through SSDT/Visual Studio to the same target SQL Server. SQL Servers with SSIS installed that were upgraded to 2016 or 2017 seemed to have no issues with packages that were in MSDB or the package store since the jobs continued to run as expected post-upgrade.

So as part of normal upgrade routine, decided to open an SSIS package and upgrade it like before with the previous SSDT changes. In previous SSDT changes, one release worked for a particular version of SSIS. Once they introduced the ability to target some previous versions of SSIS, that feature seemed to work as expected. I figured things would be the same with the latest SSIS and SSDT versions.

However, once changing the target version to SQL Server 2017 within SSDT, there are some things that showed up that SSDT did not catch/fix or could not handle for some reason. Some of the issues I ran into were:

Cannot change the target .Net Framework version as it always reverts
back to original settings in the script task properties
Errors would show up such as "'Dts' is not a member of 'Microsoft.SQLServer'", "Type 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase' is not defined", "Failed to migrate scripts contained in the package to the VSTA 14.0 format. Move the scripts to a new Script task.", and "Type 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute' is not defined."
Building the script task would fail and one of the errors would be "The primary reference "Microsoft.SqlServer.ScriptTask, Version=14.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91" could not be resolved because it was built against the ".NETFramework,Version=v4.5" framework. This is a higher version than the currently targeted framework ".NETFramework,Version=v4.0" and the other error I would see was "Namespace or type specified in the Imports 'Microsoft.SqlServer.Dts.Runtime' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases."

The good thing is changing the target back to SQL Server 2016 seemed to work and I could continue editing the packages as before without errors and build would work as expected but the bad news is deploying the 2016 package to an 2017 SSIS server did not get converted like it did during the SQL Server upgrade. So how would I be able to update the packages to target SQL Server 2017 and not have these issues that prevent that?


texts: Which oracle (10g) template files (DBCA) can be be copied to different server for cloning?

texts:
I am trying to clone a schema from one Oracle 10g server to a different Oracle 10g server.

I have created a database template (DBCA) from an existing database (structure and data).

I have copied the three template files .dbc, .DBF and .CTL to the other oracle 10g server. Do I need to copy any other files?

I am looking to achieve this by using DBCA utility not through the import/export data pump.

I can clone a database on the same machine using DBCA utility but am not able to clone it on the different machine using the first machine's template.


texts:
PostgreSQL 9.4.8 Ubuntu 16.04 64 bit

I just want an explanation of what is happening here. Why is this command not found? Nothing else.

malikarumi@Tetuoan2:/usr/lib/postgresql/9.4/bin$ pg_ctl stop -m fast
pg_ctl: command not found
malikarumi@Tetuoan2:/usr/lib/postgresql/9.4/bin$ sudo pg_ctl stop -m fast
[sudo] password for malikarumi:
sudo: pg_ctl: command not found
malikarumi@Tetuoan2:/usr/lib/postgresql/9.4/bin$ ls
clusterdb   initdb             pg_ctl          pg_restore      postmaster
createdb    oid2name           pg_dump         pg_standby      psql
createlang  pg_archivecleanup  pg_dumpall      pg_test_fsync   reindexdb
createuser  pg_basebackup      pg_isready      pg_test_timing  vacuumdb
dropdb      pgbench            pg_receivexlog  pg_upgrade      vacuumlo
droplang    pg_config          pg_recvlogical  pg_xlogdump
dropuser    pg_controldata     pg_resetxlog    postgres
malikarumi@Tetuoan2:/usr/lib/postgresql/9.4/bin$


texts: How to make stored procedures more readable without harming performance

texts:
I come from OOP background, and I need to write and maintain huge stored procedures.

Naturally, the sprocs become quite unreadable very quickly, no matter how much I try formatting, giving readable names and aliases, and so on.

Andrew Novic brings up the option of breaking the procedure into subroutines (rule #1 they teach you in school), but points out that this leads to poor performance.

In my case, performance overrules all other considerations, so it looks like I'm stuck with run-on stored procedures. (ORM is out of the question.)

So... is there any way to make my stored procedures more readable,
or is "damage minimilization" (i.e. nice formatting) my only option?

UPDATE - Clarification:

I deliberately left out details, because I reasoned answers would be useful for others as well, and not just my specific case.
However, I'm adding the details to give an idea of what I'm facing.

Here's what makes the SPs unreadable:

Length (hundreds of lines of code each)
Dynamic SQL
Control flow
Nested queries (sometimes two or even three levels)
Combinations of all of the above :(

UPDATE 2:

I'm using Sql Server 2k5/2k8


texts: Clustered Index - Query Performing Index Scan

texts:
Why does the below query perform an Index Scan rather than an Index Seek?

I would have thought the Index would act as a covering index?

CREATE NONCLUSTERED INDEX [IX_CustomerFileDate]
ON [dbo].[UserData]
(
    [CustomerID] ASC,
    [FileDate] ASC
)
INCLUDE ([UserID],[User])

-- query
SELECT c.User, c.UserID
FROM ClientData c
WHERE CustomerID = @CustomerID
AND FileDate >= @StartDate
AND FileDate <= @EndDate

Execution Plan posted to dropbox

Thanks!


texts: Syntax Error after adding FOREIGN KEY REFERENCE

texts: I'm getting the shown error in the code below, having copied the foreign key line stuff from the tutorial shown in the second image. Even after looking at this and other tutorials throughout the day I'm still not seeing where the problem is. Could someone please point out what's wrong?


texts: How to get the definition of a database trigger?

texts:
when I run the following script in one of my databases:

SELECT * FROM SYS.triggers

when I try to find out about sys.triggers

  Contains a row for each object that is a trigger, with a type of TR or
  TA. DML trigger names are schema-scoped and, therefore, are visible in
  sys.objects. DDL trigger names are scoped by the parent entity and are
  only visible in this view.

How then, do I get the database triggers definition?


texts: Query for master in Postgres replication

texts:
I'm trying to find out if there is a way to query for the master, from a slave PostgreSQL server that has been set up with server replication.

From a slave, I can:

SELECT pg_is_in_recovery()

And this will give me a 't' result if I'm on a slave and an 'f' result on the master, which is step one.

Next, I'd like to run a query that gives me some information about the master that it's replicating.  Preferably an IP address or hostname.

For the record, I can query the master with:

SELECT * from pg_stat_replication

And this will give me information about slaves.  I am hoping that there is a reciprocal method for querying a slave.

Is this possible?  If so, how?


texts:
Running two PostgreSQL 9.2.3 instances.  One is master and other is slave.

All was working for awhile, I could make changes on master and see them on the slave but now my slave is not getting changes any more.

1000     25069  0.2  0.1 131616 11280 pts/0    S    01:02   0:00 /usr/local/pgsql/bin/postmaster -D /mnt/pgdb/v9.2.3 -i
1000     25074  0.0  0.0 131724  1440 ?        Ss   01:02   0:00 postgres: startup process   waiting for 000000040000000700000083

I've tried countless things including completely blowing away the slave, running initdb and rsyncing the master back to the slave, launching the slave only to find it still stuck in this same dam state.

What the hell am I doing wrong here?

Slave's recovery.conf looks as follows:

standby_mode = 'on'
primary_conninfo = 'host=172.16.0.14'
trigger_file = '/tmp/pgfailover'
restore_command = 'cd .'

I've also tried the following on the master to no avail:

$PGDIR/psql -U $UNAME -d postgres -c "select pg_start_backup('clone',true);"
rsync -av --exclude pg_xlog --exclude postgresql.conf /mnt/pgdb/current/* 172.16.0.8:/mnt/pgdb/current/
$PGDIR/psql -U $UNAME -d postgres -c "select pg_stop_backup();"

UPDATE - Including relevant information pertaining to replicate problem.

postgresql.conf on the Master node

wal_level = hot_standby
archive_mode = on
archive_command = 'cd .'

max_wal_senders = 3
wal_keep_segments = 5000
hot_standby = on
hot_standby_feedback = on

The Slave Node's postgresql.conf has these settings:

wal_level = hot_standby
archive_mode = on
archive_command = 'cd .'

max_wal_senders = 3
hot_standby = on
hot_standby_feedback = on


texts: I cannot restart mysql after mysql_upgrade

texts:
I cannot restart MySQL.

I executed mysql_upgrade -u root -p,it did not work, and the mysqld.log contains the two errors:

  181130  8:21:21 [ERROR] Incorrect definition of table mysql.proc: expected column 'sql_mode' at position 14 to have type
  set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERRO

  181130  8:21:21 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type
  set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'),
  found type
  set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A

and my.cnf:


texts: PostgreSQL + Citus - multi master - replication of data

texts:
I have a question, I have found a Citus extension which is recommended for a replication of data between multi-master nodes (I was using Galera Cluster prior to PostgreSQL).

However I cannot find any example of how to replicate tables without creating any additional tables (distributed) as replication has to work without any modification of C# code that is using PostgreSQL as a database.

It has to work in a same way as Galera Cluster (multi-master replication between nodes).

Perhaps I've tried wrong extension, but I've tried to review the options for 3 days and seemed like a good idea.

Perhaps someone could give the recommendation for a good extension that can offer multi-master replication without any additional change in existing queries that are used by our software.


texts: Delete the second record where a user has two records


texts: Creating a View with results from join of scalar tables

texts:
I am really bad at SQL Queries and seem to be stuck at a very odd problem. Maybe there's an alternative of doing this, but I simply can't figure it out. Maybe you guys can help!

So, here's my objective : I need to show the trending graph for the last 12 months and I need to generate a view out of my existing table having two columns : Month+Year and Count. So it will be like "Jul-2014 97" ish records.

I tried the following approach where I am first creating a scalar table with last 12 months Month+Year combination like "Jul-2014", etc. Second I am creating another scalar table with "Month+Year" and "Count" combination like "Jul-2014 97". The thing is the second table will not have data for all the last 12 months. So I am doing a LEFT OUTER JOIN to get all Month+Year data.

My query is as follows:

DECLARE @MonthYears TABLE (LNo INT IDENTITY(1,1), Name Varchar(50))
DECLARE @MonthYearsData TABLE (Yr INT, Mon INT, MonYr varchar(50), CrCount INT)
SET LANGUAGE English;
WITH R(N) AS
(
SELECT 0
UNION ALL
SELECT N+1
FROM R
WHERE N < 12
)
INSERT INTO @MonthYears (Name)
SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) + '-' +
       CAST(DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) as varchar(10)) AS Last12Mon
FROM R

INSERT INTO @MonthYearsData(Yr, Mon, MonYr, CrCount)
SELECT
YEAR(RequestedOn),
MONTH(RequestedOn),
SUBSTRING(DateName( month , DateAdd( month , MONTH(RequestedOn) , 0 ) - 1 ),1,3) + '-' + CAST(YEAR(RequestedOn) as varchar(10)) as MonYr,
count(*)
FROM TempTableTest
WHERE RequestedOn >= DATEADD(MONTH, -13, GETDATE())
GROUP BY YEAR(RequestedOn), MONTH(RequestedOn)

SELECT M.Name, ISNULL(N.CrCount,0) as CRCount
FROM @MonthYears M left outer join @MonthYearsData N ON M.Name = N.MonYr
GROUP BY M.Name, N.CrCount, M.LNo
ORDER BY M.LNo DESC

So, I am getting response as follows:

Name      CRCount
Dec-2013    0
Jan-2014    0
Feb-2014    0
Mar-2014    0
Apr-2014    0
May-2014    0
Jun-2014    0
Jul-2014    0
Aug-2014    1
Sep-2014    0
Oct-2014    0
Nov-2014    0
Dec-2014    2

Now I need this in a View, so I can use it against my reporting tool. However, I am unable to create one using this scalar table approach. Even temporary table fails. I am really in a deadlock. Can someone please point out an approach?

P.S. : I tried dynamically creating the scalar tables inside the last SELECT query, but haven't had any luck.

Thanks in advance!


texts:
What is the surface area in SQL Server? or what is surface area in SQL Server consists of?

Does Oracle has similar concepts? If yes what is it called?


texts: Insert or update value on table where values can be repeated multiple times

texts:
Previously I was using the following table:

CREATE TABLE Users(user BIGINT PRIMARY KEY,
                   item_count INT);

which allowed me to do a simple upsert:

INSERT INTO Users(user, item_count)
VALUES($1, $2)
ON CONFLICT(user) DO
UPDATE SET item_cout = user.item_count + 1;

However I want to switch my table to have more columns so each user have a different item_count per server so I did:

CREATE TABLE Users(id SERIAL PRIMARY KEY,
                   server BIGINT NOT NULL,
                   user BIGINT NOT NULL,
                   item_count INT);

However I'm not allowed to do an ON CONFLICT anymore in this case because neither server nor user are unique, and they can be repeated throughout the table for example:

id  server  user  item_count
1        1    1           95
2        2    1          345
3        1    2          594
4        3    1            0

Is there any way to do an upsert with the second table instead of separating the query into two statements? (one for insert if not exists and one for updating)


texts:
I have a fairly complicated query. I have written two different query to accomplish my goal.
First one is using WITH, other one is using global temporary tables.

First One:

WITH A
(
 KNO
 ..
 )
 , B
 (
 KNO
 ...
 )
 , C
 (
 KNO
 ...
 )
 SELECT * from
 A INNER JOIN B
 on A.KNO = B.KNO
 INNER JOIN C
 on B.KNO = C.KNO

Second One:

Truncate Table tempA;
Truncate Table tempB;
Truncate Table tempC;

INSERT INTO tempA SELECT -- Same select which constructs WITH A
INSERT INTO tempB SELECT -- Same select which constructs WITH B
INSERT INTO tempC SELECT -- Same select which constructs WITH C

 SELECT * from
 tempA A INNER JOIN tempB B
 on A.KNO = B.KNO
 INNER JOIN tempC C
 on B.KNO = C.KNO

They are on par. What I mean is I observe same query time with both of them. But When I add KNO index to tempA,tempB,tempC temporary tables. Second one skyrockets.
What I mean is : It gets faster, a lot. I query for 1 year of values. using WITH: about 58 minutes. using Temp Table with INDEX: about 30 minutes.

Query times for 4 months of values are given below.

Using WITH

DENEME@DENEME   Finished    15:37:02    15:40:38    03:36 mins  Select  500 WITH A

Total:216000 msecs

Using Temporary Tables + Index

DENEME@DENEME   Finished    15:41:54    15:41:55    1 sec   Select  500 WITH D
DENEME@DENEME   Finished    15:41:38    15:41:38    665 msecs   Insert  34660   INSERT INTO C
DENEME@DENEME   Finished    15:41:21    15:41:35    14 secs Insert  34660   INSERT INTO B
DENEME@DENEME   Finished    15:41:17    15:41:17    109 msecs   Insert  1804    INSERT INTO  A
DENEME@DENEME   Finished    15:41:12    15:41:12    33 msecs    Unknown 0   TRUNCATE TABLE C
DENEME@DENEME   Finished    15:41:12    15:41:12    39 msecs    Unknown 0   TRUNCATE TABLE B
DENEME@DENEME   Finished    15:41:11    15:41:11    203 msecs   Unknown 0   TRUNCATE TABLE A

Total: 16049 msecs

My question is :

Can I give hint to oracle to add index when it is constructing WITH temporary tables?


texts: Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT?

texts:
This is a followup question from: https://stackoverflow.com/questions/7684477/is-it-possible-to-set-transaction-isolation-level-snapshot-automatically

I'm still having deadlock/timeout situations in the ASP.NET application when running large reports simultaneously although READ_COMMITTED_SNAPSHOT ON.

So I have two questions:

How can i check if the Transaction Isolation Level Snapshot is working as expected/at all?
I'm assuming that the foreign keys (in the tables of the Web-Application to the report-tables) are responsible for deadlocks. I found this interesting article:

  Note SQL Server acquires shared locks when validating foreign keys,
  even if the transaction is using read committed snapshot (read
  committed using row versioning) or snapshot isolation level. Be
  mindful of this when examining deadlock graphs from transactions when
  these transaction isolation levels are used. If you see shared locks,
  check to see whether the locks are taken on an object that is
  referenced by a foreign key.

How can I check if the FK are really responsible for the Deadlock/Timeout situations, does that mean i could delete those foreign keys to prevent deadlocks(what would be an acceptable effort)?

Note: I'm only reading from the tables which cause deadlocks.

Any thoughts on this topic are greatly appreciated.

Edit Here is a Deadlock-Graph. Maybe somebody could help me to understand what causes the deadlock. It seems that it occured without any reports running only caused by the web-application, when two transactions want to write the same table(one update and one insert, the insert is as Stored-Procedure). Why does it aquire page locks and how to enable row locks only? The Insert-SP uses already TRANSACTION ISOLATION LEVEL REPEATABLE READ.

I have a strong suspicion that two triggers(one update and one insert) are responsible for the deadlocks. Here is the insert-trigger:

CREATE TRIGGER [dbo].[CreateRMAFiDates]
   ON  [dbo].[RMA]
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE RMA
    SET [fiCreationDate]=(SELECT idDate FROM tdefDate
        WHERE CONVERT(VARCHAR, INSERTED.Creation_Date, 112) = tdefDate.Text),
        [fiPopDate]=(SELECT idDate FROM tdefDate
        WHERE CONVERT(VARCHAR, INSERTED.POP_Date, 112) = tdefDate.Text),
        [fiManufactureDate]=(SELECT idDate FROM tdefDate
        WHERE CONVERT(VARCHAR, INSERTED.Manufacture_Date, 112) = tdefDate.Text)
    FROM INSERTED;
END

So this trigger updates the RMA-Table what causes the update-trigger to fire(what does similar). Does the deadlock-graph confirms my assumption? I think i'll delete those triggers and create a SP that is running once a day what would be perfectly sufficient, because these column are for a SSAS-Cube(Molap) only.

Edit: By the way, there was no deadlock anymore since i deleted these triggers :)


texts: SQL Server 2008 R2 sp_replmonitorrefreshjob Issues

texts:
My team just moved our production SQL Server VM from one machine to another. During the move we also applied server updates. After the move was completed, everything tested as a successful move.

Today I found the job "Replication monitoring refresher for distribution" running for over 24 hours. The procedure that it is hanging on is sp_replmonitorrefreshjob. This slows all the user and developer queries.

How can I troubleshoot this? It's not producing an error, it's just sits in a permanent suspended state with query waitfor delay @strdelaytime


texts:
I use simple mysqldump with only parameters are login and db details. No option of anykind is set. Now, when I recieve the dump file, it export the view in steps (I am sure you all know it, but just for my clarification)

Placeholder table in top of file with other tables.
then at end, it drop this table and create View with definer.

This works good if I have to upload this database back to original server. But how I handle this when I have to import this data in development server where I do not get the same username as defined in production database? Is there any option I can use to elimmiate the "definer" in view. Why doesn't it simply use "Create View as..." syntax?

I try to read about it, but doesn't found any good information, Please enlight me on the topic thanks.


texts:
I am having an issue that I cannot see to find a solution to.

We have a small server application that pings a GPS Server every 1o seconds and writes the values to two tables. Firstly GPSLive, secondly GPSLog. The server application reports the last time the data was updated for each table.

The client application reads GPSLive to draw a GPS map of the vehicles. The client checks the timestamp of the GPSLive table and compares it against the current time and warns the user that the data may not be current.

At the moment 18:12:22 the server has updated both rows at 18:11:59. However, the client is showing that the data is 16 minutes old.

The GPSLive table has a single row and is overwritten at every update. This happens regularly and will then catch up. The system runs on our local LAN only and at most has a handful of users. When I look at GPSLive via workbench I can see the timestamp hasn't updated.

Is there anything I can do to fix this behaviour?

Edit: Added Code

    Public Sub AddtoGPSLive(JSONString As String)
    Dim sql As String = "Update GPSLive Set GPSJSON=@J Where ID=1"
    Using con As New MySqlConnection(My.Settings.BMConnString)
        Using cmd As New MySqlCommand(sql, con)
            con.Open()
            cmd.Parameters.AddWithValue("@J", JSONString)
            Dim i As Integer = 0
            i = cmd.ExecuteNonQuery
            If i > 0 Then
                txtLastLive.Text = Now
            End If
        End Using
    End Using
End Sub


texts: How to efficiently create a new PRIMARY KEY CLUSTERED definition online

texts:
Given a table with a PRIMARY KEY definition (acting as the clustered index), I want to efficiently change the definition of the PRIMARY KEY (or at a minimum, just the clustered index) online.

I would love to simply do the following:

CREATE UNIQUE CLUSTERED INDEX PK_MyTable ON dbo.MyTable
(
    [Col2] ASC
) WITH (DROP_EXISTING = ON, ONLINE = ON);

However this results in an error: Cannot recreate index 'PK_MyTable'. The new index definition does not match the constraint being enforced by the existing index.. And I understand why.

Is there another way to accomplish this?

For background: we have a table that is only queried via a nonclustered index. The characteristics of the that index are quite good to be the clustered index of the table (i.e. unique and unchanging narrow key, inserts are primarily at the "end"). The current PRIMARY KEY (the clustered index) is simply a surrogate key. The table is not the target of any FK relationships, and rows are never queried by the current PRIMARY KEY. We would like to efficiently change the clustered index online. We are running Enterprise edition.

If terms of "online" operations; at a minimum we want to continue inserting new rows, but we can temporarily suspend queries until the operations complete.

If it's not possible to do this with the existing table online, is there an approach (maybe involving a new table and copying the data) that could work with as little interruption as possible?


texts: openssl s_client does not indicate that SQL Server is using TLS, however the connection is secure. How is that possible?

texts:
I am connecting to SQL Server 2017 using ODBC and MSSMS.  The connection is not using TLS; I checked it using openssl s_client.  However, the DBA assures me that the connection is secure.

What other common or typical methods/protocols could be used in making such a connection secure if not TLS?  The connection is not being made through a SSH tunnel or VPN.


texts: Learning COMPUTED BY - what right parenthesis does it need?

texts:
I am reading this document on Automatic columns.
The first snippet in it creates a COMPUTED BY column:
create table PERSON (
(employee_id integer,
employee_id_disp computed by
SUBSTRING (CAST(employee_id + 100000 as VARCHAR (6)) from 2)
);

When I run this snippet, I get this error:
Error starting at line : 1 in command -
create table PERSON (
(employee_id integer,
employee_id_disp computed by
SUBSTRING (CAST(employee_id + 100000 as VARCHAR (6)) from 2)
)
Error report -
SQL Error: ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:
*Action:

And whey I try to create my own table as follows:
create table A (
  a1 varchar(4000) not null,
  a1Raw computed by UTL_RAW.cast_to_raw(a1)
)

I get a different error:
Error starting at line : 11 in command -
create table A (
  a1 varchar(4000) not null,
  a1Raw computed by UTL_RAW.cast_to_raw(a1)
)
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:
*Action:

I am not overly concerned about the snippet from the Oracle's PDF not working: it sometimes happens that documentation contains bugs. But why is my script not working? I cannot see where a right parenthesis may be possibly missing because all of them match.


texts: Archiving Huge table with Foreign Key dependencies

texts:
We have a huge table of ~ 1TB (data+Indexes) which has ~7 child tables with Foreign key relation ship

We have decided to archive the huge table but not the child tables(since they are not that big )

Reason : To improve the performance and to decrease the time taken to  backup.

Can’t make it as partition and make the old files as read only.

Reason: no backup for the old data in case of any disaster/data loss

The approach we have tested is :

Copy the column(it’s an identity column) which has dependency on the child table to another newly created FKtable

Drop the FKeys on the huge  table

Create and Divert  the Fkeys on the newly created table FKtable (since we are not archiving the child tables, also we want to schedule the regular archival in future )

Start archiving the huge table in batches to another database.

Also update the SPs where ever  insert to huge table is happening should also insert in FKTable(since we moved the FKeys to this table)

Also update the SPs where ever  update  to huge table is happening should also check  in FKTable in case they are touching the old data.

Also update the SPs where ever  select  to huge table is happening should also select  in FKTable in case they are touching the old data.

I'd be very grateful for any feedback. Am I on the right track or should I be considering a different approach?


texts: Killing a process without login to MySQL

texts:
Here is the situation:

I was running a very complex query on a database which was expected to execute within a few hours over night(server configuration is not good and data set is large). This query is using table tblA
while the query is being executed, application A (running on the Server) executes  DROP TABLE tblA.
This DDL Statement locks the metadata for tblA since table is in use by another transaction (my query).
Application B comes in and wants to perform an update on tblA.
Update statement does not execute since the DROP TABLE tblA holds the metadata lock.
Application B is multithread and executes another update on tblA
Application B keep doing that until max_connections limit is met and I receive Too Many Connection error every time I try to connect to server.
Right at this moment the application I was using to execute the query (HeidiSQL) stop working and crashes.
I just noticed all the connection to server is through Root user from local host so there is no way to log in to MySQL to kill the DDL statement.

Question is:

Is there anyway to end this without having to kill or stop MySQL Server?

I already tried killing Application A and B but the process still remains in MySQL queue. It also sounds like my query is not willing to end any time soon.


texts: Builtin\Administrators doesn't permit access

texts:
I have created a SQL 2016 (Developer) instance on a new build machine. For ease of use, when we do this, we grant Builtin\Administrators sysadmin rights on the instance. This allows all developers on the machine to be able to have full control over their local instance.

In previous versions of SQL (e.g. 2014). This works fine. However, when I try it on SQL 2016, this does not appear to work.

Whenever I attempt to log into the machine with Windows Authentication, I receive the following error:

  Login failed for user 'xxxx'. (.Net SqlClient Data Provider)

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

  Server Name: xxxx

  Error Number: 18456

  Severity: 14

  State: 1

  Line Number: 65536

Below are shots of the Builtin\Administrators group within SQL:

As you can see, it's set up and enabled.

The server is configured for Mixed Mode authentication. If I add a Login for the user directly, Windows Authentication will work successfully.

Below is the error log from SQL, which includes a bit more information:

For clarity, the user is in a group which is a member of the Administrators group on the machine. Running whoami /groups on the commandline confirms that the local Administrators group is listed.

What do I need to do to get Builtin\Administrators to allow successful logins?


texts: how to output 3 column into 3 element in xml format with the same name?

texts:
I have this code and I can't figure out how to get the expected result?

declare @table table (TAG int, PARENT int,col1 varchar(100)
                                         ,col2 varchar(100)
                                         ,col3 varchar(100))

insert into @table select 1, NULL, 'Value1'
                                 , 'Value2'
                                 , 'Value3'

select TAG,PARENT, col1 as [MyElement!1!!element],
                   col2 as [MyElement!1!!element],
                   col3 as [MyElement!1!!element] from @table for xml explicit

result :

<MyElement>Value1Value2Value3</MyElement>

expected :

<MyElement>Value1</MyElement>
<MyElement>Value2</MyElement>
<MyElement>Value3</MyElement>


texts:
*strong text*I am putting together a small inventory management system and have some questions concerning the database design. What I was planning on doing was to have 3 tables, items, bins and shelves. Items would be created and added to the table. When we have stock of an item it will be in a bin on a shelf. If we don't have stock the item will still be in the items table but it won't have a location or the location will be set to a value that indicates  no stock.

I started the items table but I wasn't quite sure how to create the relationship between all the tables. I believe a foreign key is what I would be looking for but I'm not sure on the proper way to implement it. I was thinking the following lay out for the tables but as I started coding it I realized this wouldn't work.

UPDATE: In response to the answer I received this is the query I came up with. It's wrapped in Python so ignore that.

db = 'inventory.sqlite'
(cur, con) = open_db(db)
cur.execute("CREATE TABLE Items (item_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, \
            sku TEXT UNIQUE NOT NULL, \
            title TEXT UNIQUE NOT NULL
            )")
close_db(con)

(cur, con) = open_db(db)
cur.execute("CREATE TABLE Bins (bin_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, \
            name TEXT UNIQUE NOT NULL)")
close_db(con)

(cur, con) = open_db(db)
cur.execute("CREATE TABLE Shelves (shelf_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, \
            name TEXT UNIQUE NOT NULL)")
close_db(con)

(cur, con) = open_db(db)
cur.execute("CREATE TABLE Bin_Contents (bin_id INTEGER NOT NULL, \
            item_id INTEGER NOT NULL, \
            qty INTEGER NOT NULL, \
            CONSTRAINT PK_Bin_Contents PRIMARY KEY (bin_id, item_id), \
            FOREIGN KEY (bin_id) REFERENCES Bins (bin_id), \
            FOREIGN KEY (item_id) REFERENCES Items (item_id) \
            )")
close_db(con)

(cur, con) = open_db(db)
cur.execute("CREATE TABLE Shelf_Contents (shelf_id INTEGER NOT NULL, \
            bin_id INTEGER NOT NULL, \
            CONSTRAINT PK_Shelf_Contents PRIMARY KEY (shelf_id, bin_id), \
            FOREIGN KEY (shelf_id) REFERENCES Shelves (shelf_id), \
            FOREIGN KEY (bin_id) REFERENCES Bins (bin_id) \
            )")
close_db(con)

After writing that and looking at it I realized that wouldn't function the way I wanted it to. As I mentioned earlier I believe a foreign key is what I need but I'm not sure about the best way to implement it. I would like to be able to:

UPDATE: Will the above query create the DB/tables i need to do the following:

Use the app to search for items and display where they are located
and how many are in each location.
Use a shelf/bin number to pull up the contents of that shelf/bin. For
example the user selects shelf_a from a menu. All the bins in
shelf_a are displayed. The user can select a bin and see it's
contents.
Perform basic management tasks. Add an item to a shelf/bin, remove
some/all the quantity of an item from a shelf/bin, add new
shelves/bins etc with out anything disappearing. Shelves are still
there even they are empty same for bins, they are still there even
when they are empty.

I don't need help with the queries themselves I just need to make sure the proper relationships are in place so when I write the queries the right info comes back.


texts: Which permissions are required to view Windows Event Logs in SSMS?

texts:
I'm trying to figure out which permissions are required to be able to view the Windows Event Logs via SSMS.

The SSMS Log File Viewer, where you typically view the SQL Server Errorlog, also has the ability to show the Windows Event Logs via a "Windows NT" checkbox & subtree (pictured below). Being a SQL Server sysadmin and Windows Administrator this shows up just fine for me.

Is there a way to give other users the ability to view the Windows Event Logs within SSMS, without giving them SQL SysAdmin and Windows Admin level permissions?

I've tried the Event Log Readers local security group in Windows, but that doesn't appear to work.


texts: What table should I host data linking two objects from separate tables?

texts:
I've tried searching around, but I'm not too sure how to correctly formulate this question, and found nothing. Could be a duplicate.

What is the best way (most efficient way) to host data that will link two objects together from separate tables? Let me explain with an example; there is a table named Users and another named Groups, a user can be part of a group. How should I hold the information on the groups the users are in.

Should I:

Leave the Users table empty, and put an array of user ids under the group in the Groups table.
Leave the Groups table empty, and put an array of group ids under the user in the Users table.
Put an array of both the user ids, and group ids under both the user and the group in both tables.

We'll say for the example that both bits of information (users in group X, and groups in user X) will be polled an equal ammount, both are accessed in an equal manner. Also, there are more users than groups.

The design part of me tells me I should use solution 2, it seems like the prettier way to go. But I'm thinking solution 1 would be faster (between 1 and 2) as there are more users than groups. And of course, solution 3 would be the fastest, but it will take more memory, and it is harder to manage (one table could have different information than the other if the program has bugs).

I know these kind of decisions will only gain tiny ammounts of time, or memory, but I like micro-optimisation, so what would you recommend? Maybe there is another solution I am not aware of?


texts: Display one row over another if there are two

texts:
Out of about 6300 employees and 53 sites there are about around 200 that have addresses in multiple locations.

If I sort the list by ID and Site.ID the first matching result is the value we want returned, and ignore any other for that employee.

Any way to do that?

SELECT DISTINCT
  Employees.ID,
  Employees.FName,
  Employees.LName,
  Address.City,
  Address.State,
  Site.ID
from Employees
  Left Join Address on Employees.ID = Address.ID
  Left Join Site on Address.ADID = Site.ID
Where Site.ID in (
  SELECT Site.ID From Site Where Site.Type = 'Primary'
)
Order by Employees.ID, Site.ID

Example result set:

1234   John   Williams   Sacramento   CA   1
1234   John   Williams   Portland     OR   2
1111   Mary   Johnson    Sacramento   CA   1
1112   James  Stoller    Carson City  NV   2

Would like this back:

1234   John   Williams   Sacramento   CA   1
1111   Mary   Johnson    Sacramento   CA   1
1112   James  Stoller    Carson City  NV   2


texts: Best way to get time zone key name from SQL Server

texts:
Below is what I pieced together but I wanted to see what other ways are available.

SET NOCOUNT ON;
GO
DECLARE @tz VARCHAR(50)
EXEC [master].[dbo].[xp_regread]
    'HKEY_LOCAL_MACHINE'
    ,'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
    ,'TimeZoneKeyName'
    ,@tz OUT;

SELECT
    GETDATE()
    ,'(' + LEFT(PARSENAME(REPLACE(@tz, ' ','.'),3),1)
    + '' + LEFT(PARSENAME(REPLACE(@tz, ' ','.'),2),1)
    + '' + LEFT(PARSENAME(REPLACE(@tz, ' ','.'),1),1) +')'

Output: 2014-10-14 16:22:21.037 (CST)


texts: SQL SP: How to return one inactive record along with active records

texts:
I am working on this peculiar sql stored proc where the business case is as follows:

Business Case:
Table Specialties contains all the Specialties and there is a bit field for each record telling if it's Active or Inactive. We always display only the active records from that table in form of dropdown. Users may select a Specialty which can later on be deactivated. New requirement is to be able to pull that Inactive record along with all the active records in the result set.

Here's how I thought I should do this:
If no specialty is assigned to the person I am pulling up then the dropdown is going to be populated by all active records.
If there is a inactive specialty associated with the person I am pulling up then I send that specialtyID in stored proc as a parameter and return that inactive records along with active records to populate the dropdown.

Below is what I got so far:
So far if I dont pass in any specialtyId then I am returning active specialty records which is working. When I send in a specialtyId  parameter then it just returns that one inactive record but not rest of the other active records. I need the rest of the active records too along with that one inactive record.

   DECLARE  @specialtyId  INT = null;
  BEGIN
    IF isnull(@specialtyId,'')=''
      BEGIN
        SELECT SpecialtyID AS Id, Specialty AS Name
          FROM dbo.Specialties
         WHERE IsActive = 1
         ORDER BY Specialty;
      END
    ELSE
      BEGIN
        SET @specialtyId = @specialtyId ;

        SELECT s.SpecialtyID AS Id, s.Specialty AS Name
          FROM dbo.Specialties s
         WHERE specialtyId = @specialtyId
         GROUP BY s.Specialty, s.SpecialtyID
         HAVING (Specialty IS NOT NULL)
            AND (max(SpecialtyID) IS NOT NULL)
         ORDER BY Name;
      END
  END


texts: how to find password for mySQL database server

texts: How can I find the password for my database on phpMyAdmin? I want to connect mySQL database just by using mysqli_connect and understand that three strings are needed, but I can't seem to find where the password could be. If anyone could help that would be great. Thanks


texts: Is there a good way to select a row where the key represents the lower bound of a range?

texts:
I am designing some tables and a number of the tables have as the key a lower and upper bound which never overlap. I want to query by a value that can fall withing any of those ranges. If I keep both columns I can query it as a range (i.e. WHERE lower_bound <= val AND val < upper_bound) and it is fairly simple.

However, if I can reduce the lower and upper bound columns into a single column then the table will be simplified in terms of the amount of data we need to store.

Here is an example of the data to be stored:

    lower_bound | upper_bound | stuff1_lower | stuff2_lower | stuff1_upper | stuff2_upper
    ------------+-------------+--------------+--------------+--------------+--------------
              0 |          10 |        0.222 |        0.333 |        0.444 |        0.555
             10 |          15 |        0.444 |        0.555 |        0.666 |        0.777

The stuff1_lower and stuff1_upper represent the same data, but the value is at the ends of the range. Anything in between I need to interpolate given the bounds.

For example, I query with 10 and I need the values 0.444 and 0.555 for my application. If I query with a 5 I need the values 0.333 and 0.444, which are the interpolated values.

The data I'm storing though is duplicated both in the key (the bounds) and in the data. By simplifying the table I know that the complexity of the query would increase, but I'm okay with that so long as the speed efficiency isn't significantly impacted.

Is this a good idea? And is there a way to do this (such as through a view) that wont significantly impact efficiency?

UPDATE
Since my data is small I decided to reduce the number of columns as I had been thinking and go with the following query to get the data:


texts: How are foreign keys resolved when creating tables in batch?

texts:
I am creating a database version management tool with the goal of being able to recreate a database from scratch using text/sql files contained in source control. As part of establishing a baseline version of the database I have exported the CREATE TABLE statement for each table into it's own sql file.

I plan to combine the contents of each file into a single batch when initializing the database.

However, each file contains foreign key constraints within the CREATE TABLE statement.

I am wondering if the RDMS (in this case MSSQL and Sybase) is going to be able to handle the batch? Will it be able to create the tables and then create the constraints?

Or is it going to fail when it comes across the first table where the table containing the candidate key has not been created yet?

I've read about batch execution in MSSQL, and my understanding is the database will create a execution plan for the batch. Will the database be able to reorder my create table statements into the right order?

My database contains 366 tables and it would be difficult to order the CREATE TABLE statements in the correct order.


texts:
I need to delete about 900m rows from a table that has 6b rows. Since big deletes cause big problems for the transaction log, I'm deleting the data in small batches. What puzzles me is SQL Server does not like to commit each chunked delete.

Let's say I delete it in batches of 1m rows:

DECLARE @N int = 1000000

DELETE TOP (@N)
    FROM    my_table
    WHERE   ...

In a separate query, I monitor the status of the delete:

-- Before executing the first query
sp_spaceused 'my_table' -- row_count = 6b

-- During execution
sp_spaceused 'my_table' -- row_count = decreasing from 6b -> 5.999b

And then it just hung! I can tell that 1m rows have been removed from my_table by peeking at the row count, but the first query just keeps going on and on.

What could cause this behavior? Another big table has a Foreign Key relationship referencing my_table.ID. Could FK validation be the culprit here?

Database is SQL Server 2012.


texts: How do I impose a CONSTRAINT on a TEXT column in PostgreSQL 12?

texts:
I want a column with a single text character as a classifier so it's not worth the overhead creating another whole table and relating to it.

How do I constrain the column to a list of acceptable characters?

E.g. how would I constrain column my_classifier CHAR(1) NOT NULL, to one of [abcd]?


texts: Simple Database Design - Many to many? - First database (newbie)

texts:
This is my first database I am designing.
However I am getting confused on the many to many relationships.
I have a products table, each product can have inventories in many different warehouses. So one product can have many inventories (in different warehouses)

Table 1 -
products
PK
code | name | cost_price
Table 2 - (Many to Many Bridging table)
product_inventory
PK                                   PK
prod_code (foreign key of products.code) | inv_code (foreign key of >products.code)
Table 3 -
inventory
PK
inv_code (auto increment) | warehouse_code (FK of warehouse.code) | >inventory_count
Table 4 -
warehouse
PK
code | warehouse_name

My big questions is: is this correct? and 2: does table 3 need product.code linked to it? or is it already linked through the bridging table? (product_inventory) If it is linked, what is an example of an SQL query to input an inventory for a product, so that Table 2 populates to link it all back so later I can do a join to get the inventory for the product again.
Any tips are well appreciated!!
Does every primary key need to have a foreign key? I.E should inventory.code be a foreign key to product_inventory.inv_code? or do only doubles (not the original need to be the foreign key) if so which should be the copy (foreign key) the bridging table or the inventory table inv_code.
Thanks in advance!


texts: Foreign key constraint is incorrectly formed (MySQL)

texts:
So, I have these two tables
CREATE TABLE IF NOT EXISTS `inart_ps_categories` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `inart_ps_category_id` int(11) UNSIGNED NOT NULL,
  `parent_id` int(11) UNSIGNED DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `path` varchar(255) DEFAULT NULL,
  `num_children` int(11) DEFAULT '0',
  `flag` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS `inart_ps_category_associations` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `inart_ps_category_id` int(11) UNSIGNED NOT NULL,
  `categoryid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
    FOREIGN KEY (categoryid) REFERENCES `categories` (categoryid) ON DELETE CASCADE,
    FOREIGN KEY (inart_ps_category_id) REFERENCES `inart_ps_categories` (inart_ps_category_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

It outputs "Foreign key constraint is incorrectly formed" for no reason. The first Foreign key is correct and if I erase the second one, the query is executed. Can you see something wrong here?


texts: tsql to know when a database was last shrunk

texts:
In SQL 2000, 2005 and 2008, how can  I know when databases were last shrunk on a MS SQL server?

I want to do this using TSQL.

Regards

Manjot


texts: How do I build a PostgreSQL ORDER BY expression based on a concatenation of columns?

texts:
I'm using Postgres 9.5.  I want to write an order by expression for a series of concatenated strings, but I'm not sure if that's possible or how to do it.  I have cobbled together the below

myproject=> SELECT  "my_objects".*
            FROM "my_objects"
              left join addresses on my_objects.address_id = addresses.id
            WHERE (my_objects.name ILIKE '%my_object%'
              AND EXISTS (SELECT *
                          FROM my_object_times
                          WHERE my_object_times.my_object_id = my_objects.id))
            order by lower(addresses.city) || "," || addresses.state_id;

ERROR:  column "," does not exist
LINE 1: ...id = my_objects.id)) order by lower(addresses.city) || "," || add...

But as you can see there is an error complaining about syntax.  Is what I'm tryihng to do possible and if so how do I write an order by clause based on a concatenation of columns?


texts: Model with variable number of "properties" of different types.

texts:
I would like to have a table of microscope slides, where on top of the basic id, name,date_created would each slide also have a number of editable "properties".

Imagine one slide having

tumor_type
dye_type
description
date_injected

And some other slide could have for example:

patient_name
treated_since
description

I can't store all these in the slides table because I would like users to be able to chose used properties or add new ones if they wish to. Each property should also have a type, since some are integers, some text and some for example date and time and I would love to render a widget for them to simplify editing these values.

I planned to use MySQL, then I was pointed to use use PostgreSQL and its hstore, but that feels like a borderline experimental feature to me. Any suggestion how should I go about it?

This is what I came up with but it doesn't feel right to me:

Also all this will end up in Django (Python) if it makes any difference.


texts:
I'm trying to formulate a rule from pg_hba.conf file. Thus far I have understood that pg_hba.conf file is used to give access to the specific user for a specific host. But I just want to take a second opinion hence this question.

Given that I defined a set of entry in pg_hba.conf:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

host    all            all             111.121.10.32/32         md5

Then does Postgres guarantee the only way anyone can connect to Postgres is either using localhost or from 111.121.10.32 and not other IP will be able to access the Postgres server.

Is my understanding correct?


texts:
As a beginner, I am not able to see any error logically. All I am doing here is saving data into a variable and comparing that in if statements. However, at some point it returns more than requested rows and causes error.

Thanks in advance for your help.

CREATE OR REPLACE PROCEDURE Late_Payment_Days(Inv_ID Varchar2)
AS
    Payment_Exist Number;
    Late_Payment Number;
BEGIN
    DBMS_OUTPUT.Enable();
    SELECT Payment_Total
    INTO Payment_Exist
    FROM Invoices Where Invoice_ID = Inv_ID;

    SELECT
        ( (SELECT PAYMENT_DATE FROM INVOICES WHERE INVOICE_ID = Inv_ID)
          - (SELECT INVOICE_DUE_DATE FROM INVOICES WHERE INVOICE_ID= Inv_ID)
        )
    INTO Late_Payment
    FROM Invoices;

    IF Payment_Exist=0 THEN
        DBMS_OUTPUT.PUT_LINE('No Payment!');

        IF Late_Payment > 0 THEN
            DBMS_OUTPUT.PUT_LINE(Late_Payment || 'Overdue Days');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Zero overdue days');
        END IF;
    END IF;
End;
/

Show Error;

EXEC Late_Payment_Days(5);


texts:
I am currently monitoring the expensive queries from the activity monitor for SQL server.

How do I see who is running the different queries?


texts: Reducing the duration of this COUNT() query

texts:
I have a table with 2,161,524 rows. I think the counting query is taking too long.

select count(mcon_codigo_pk) from tbMovimentoConta
-- count = 2,161,524
-- time = 9 seconds

select count(1) from tbMovimentoConta
where con_codigo_fk = 1
and mcon_data between '2015-01-05' and '2016-01-06'
-- count = 1,034,729
-- time = 13 seconds

Details:

The column con_codigo_fk is bigint and has a foreign key index (non-clustered)
The column mcon_data is datetime and has index (non-clustered)
The table has an auto-increment PK (clustered index)
There's more three foreign that has index (all indexes was created by Entity Framework)

Index creating script

CREATE NONCLUSTERED INDEX [ix_mcon_data] ON [dbo].[tbMovimentoConta]
(
    [mcon_data] ASC
)
WITH (
    PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
);
GO

My computer has 8GB RAM and a Core i7 running Windows 10.

Are my queries really taking a long time or they are on the average expected time? If they are taking a long time, what can I do to make them faster?


texts: How to debug huge binary log file increases in MySQL replication setup?

texts:
I'm running a single slave off a MySQL master. I'm using this version:

Ver 14.14 Distrib 5.7.13, for linux-glibc2.5 (x86_64) using  EditLine wrapper

The problem I have is that the system will replicate quite happily for a day or so and then something hits it and the log files on the master grow hugely.

The data directory for the database is about 9.5GB in size.

When the replication problem happens, the log files can take up 175GB of disk space in a very short amount of time (filling the disk).

How do I debug what is happening? Can anyone suggest what to look for in the database operations that may cause this huge blow-out?