texts: Best way to Store and Count available stock (PostgreSQL)

texts:
I am not sure how to proceed with counting the stock and storing the stock in generated invoices.

Live data: http://sqlfiddle.com/#!17/20ff4/4

Table articles there the static product info is stored:

create table articles
(
    prod_article_pvt_uuid uuid default uuid_generate_v1() not null
        constraint proxies_pk
            primary key,
    prod_article_pub_uuid uuid default uuid_generate_v1mc(),
    prod_article_pub_id serial not null,
    t timestamp with time zone default CURRENT_TIMESTAMP(6),
    archived boolean default false,
    cat integer,
    prod_brand_id integer,
    barcode bigint,
    supplier_code varchar
);

This table opens a document, and defines the type of document:

create table documents
(
    sale_doc_pvt_uuid uuid default uuid_generate_v1() not null
        constraint documents_pk
            primary key,
    sale_doc_pub_uuid uuid default uuid_generate_v1mc(),
    sale_doc_alpha varchar,
    t timestamp with time zone default CURRENT_TIMESTAMP(6),
    archived boolean default false,
    sale_type_id integer not null,
    frozen boolean default false
);

create table types
(
    sale_type_id serial not null
        constraint types_pk
            primary key,
    name varchar,
    draft boolean default false
);

Where each artricle is added as an item of the document:

create table items
(
    sale_unit_pvt_uuid uuid default uuid_generate_v1() not null
        constraint units_pk
            primary key,
    sale_unit_pub_uuid uuid default uuid_generate_v1mc(),
    t timestamp with time zone default CURRENT_TIMESTAMP(6),
    archived boolean default false,
    sale_doc_pvt_uuid uuid not null,
    prod_article_pvt_uuid uuid not null,
    quantity numeric(16,6) not null
);

These are the available products in the warehouse:

create table products
(
    whse_prod_pvt_uuid uuid default uuid_generate_v1() not null,
    whse_prod_pub_uuid uuid default uuid_generate_v1mc(),
    t_created timestamp with time zone default CURRENT_TIMESTAMP(6),
    prod_article_pvt_uuid uuid not null,
    quantity numeric(16,6) default 1,
    global_ctry_id integer
);

I am trying to query this like so:

SELECT
     (sum(products.quantity) - COALESCE(_items.quantity, 0)) as quantity
     ,articles.prod_article_pub_id as article

 FROM products

 LEFT JOIN articles ON products.prod_article_pvt_uuid = articles.prod_article_pvt_uuid

 LEFT JOIN (
     SELECT DISTINCT ON (items.prod_article_pvt_uuid)
         items.prod_article_pvt_uuid
         ,items.sale_doc_pvt_uuid
         ,sum(items.quantity) as quantity
     FROM items
     LEFT JOIN documents ON items.sale_doc_pvt_uuid = documents.sale_doc_pvt_uuid
     LEFT JOIN types ON documents.sale_type_id = types.sale_type_id
     WHERE
         items.archived = false
     AND documents.archived = false
     AND types.draft = false
     GROUP BY
         items.prod_article_pvt_uuid
         ,items.sale_doc_pvt_uuid
 ) AS _items ON _items.prod_article_pvt_uuid = products.prod_article_pvt_uuid

 GROUP BY
     articles.prod_article_pub_id
     ,_items.quantity

 ORDER BY
     article ASC,
     quantity DESC

It does give me the desired result. But I am wondering if this is the correct way at going about it, cause it looks like it might struggle with millions of rows.

Basically each time I need to display the quantity available in stock I have to count every single document ever generated.

Will it be a performance bottle neck with millions of rows?


texts:
I tried to use a DROP statement to delete a table but I could not.Instead I tried the following....I deleted the .frm and .ibd files from of the respective table from the directory.

Then I used a create statement from a backup file to recreate the table...and the weird thing is that I get message saying that the table already exists (despite having deleted the aforementioned files)...

After the create statement the directory has again the .ibd file.

I do not know what to do or assume.


texts: How to uniquely identify a row in a table where there are no unique columns in Ingres DB?

texts:
I am trying to find a way to uniquely identify a row in a table where there aren't any unique columns. I have seen that it is advised not to use tid as a unique row identifier so I was wondering what are the other possible ways?

I have tried to create a new column and populate it with an incrementing value in the following manner:

-- Create a column

ALTER TABLE [TABLENAME]
ADD COLUMN [ID] INTEGER NOT NULL WITH DEFAULT;

-- Reconstruct

MODIFY [TABLENAME] TO RECONSTRUCT;

-- Create

CREATE SEQUENCE [SEQUENCENAME]

-- Update the newly made column with sequence

UPDATE [TABLENAME]
SET [ID] = [SEQUENCENAME].nextval;


texts: What's the difference between betwwen 2 DB and 2 Schema

texts:
My question is : what is the difference between the two next mode in PostgreSQL, for an production application.

using two DB in the same SGBD (the same table-space and HDD)
Or using two schema in the same DB

What about response time & performance ?


texts: SQL Server Agent - Run Query at the end of each month with date variable

texts:
I am running SQL Server 2008 R2, and I need to create a new job that will basically run a query at the start of each month (at the 1st of each month at 01:00 AM).

Here is the query:

INSERT INTO [SupportTracker].[dbo].[DashboardRecords]
SELECT [bg_id]
      ,[bg_short_desc]
      ,[bg_reported_date]
      ,[bg_status_updated_date]
      ,[us_firstname]
      ,[us_lastname]
      ,[LastUpdatedUserFirstname]
      ,[LastUpdatedUserLastname]
      ,[st_name]
      ,[pr_name]
      ,[ct_name]
      ,[pj_name]
      ,[AssignedUserFirstname]
      ,[AssignedUserLastname]
      ,[bg_project]
      ,[no_of_hours]
      ,[BugType]
      ,[SubType]
      ,[Device]
      ,[pj_parent_id]
FROM [SupportTracker].[dbo].[ViewIssueListwBugTypeNDevice]
WHERE
     bg_reported_date between '2012-12-01 00:00:00.000' AND '2012-12-31 23:59:59.999'
     AND bg_id NOT IN (SELECT bg_id FROM [SupportTracker].[dbo].[DashboardRecords])
ORDER BY bg_reported_date ASC

My problem is at the WHERE clause. These two dates have to change every month.

If we are on the 01-01-2013 01:00:00:00, I need these 2 dates to be:
2012-12-01 00:00:00.000 and 2012-12-31 23:59:59.999
If we are on the 01-02-2013 01:00:00:00, I need these 2 dates to be:
2013-01-01 00:00:00.000 and 2013-01-31 23:59:59.999.

Basically I need to capture parts of the database and save it in the table, as it was at the end of each month...

Thanks


texts: file sql.gz give error 1064 when import in phpmyadmin

texts:
I tried to import a .sql.gz file that I exported before another server in phpmyadmin in this new phpmyadmin, the phpmyadmin version is the same, the sql is being imported the same way it was exported (GZIP), but the 1064 error and does not conclude

I was opening the .gz file in the text editor, it includes the delimiter - additional to see if it goes, but the result is the same

In the text editor, the .gzip file is not completely visible to the SQL text, after the thousand line is compressed, small sample text

  q@WG°LSÔµ
  Ä]0Scˆc»†8¼ëaˆ#üp¢–GwuyqdW—ïX¤^ù¡†Çumqd×…±Ä±]mXcˆãº†8¦ëaˆã93†£É`óóe’e/œ8º·àHÙ\
  OìÛx¶2jò6o‚ŸgÛü¼Œ£ÕñEšÍ±ëU¦>Ç…ò‡ †æ!

I need to import this database in phpmyadmin and I can not, I exported another one to see if I could import, no, does phpmyadmin have any configuration that prevents importing the gzip file itself?


texts: How does "Be made using the login's current security context" pass the users password to remote server

texts:
Say you have a SQL Server with a "Linked Server" to another server - said "Linked Server" is set to use "Be made using the login's current security context" as its authentication model.

How does the SQL Server provide the correct password (when dealing with non-windows credentials) to the other server when all the server has is a Hash.

I have transferred logins from server to server before and i know that the hash alone is stored in SQL and this is often presented as a hex value hex when creating logins with passwords, for example:

create login test with password = '0x22A9AE652CFC38938D56A9C3872B266B192D16E4' hashed

If SQL only has a hash available - can it logon to the remote server using the hash? Or is the original (un-hashed) password kept in the user’s session / memory for the entire duration of their connection which SQL can then retrieve and pass to the remote server for login?

If the server can login with a hash instead of the password – can this be done in normal logins or is it purely an internal feature of linked servers?

Purely a curiosity question – like to understand how these things work.


texts: What is the best way to backup logs and shrink logs in SQL Server 2008 r2?

texts:
What is the best way to shrink and backup our db logs?

I have seen many DBAs that do it :

ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE
DBCC SHRINKFILE(<log_file_name_Log>)
ALTER DATABASE [mydatabase] SET RECOVERY FULL

But I wonder, is this the best way?

Am I affecting my database by losing data when changing the status of the recovery log?


texts:
I have the problem that in my DB2 database (SLES, Db2 v10.5) my User Temp Tablespaces are all in state Restore pending, Storage must be defined, Storage may be defined.

When I set off the following command db2 list tablespace I get the following output (e.g.):

Tablespace ID                        = 6
Name                                 = USERTEMP4
Type                                 = System managed space
Contents                             = User Temporary data
State                                = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Can anybody help me to escape from this situation? I didn't start any restore recently so I'm wondering how or why the tablespaces have changed to this state.


texts: How do I replace this where clause with a join?

texts:
Typically when I see SQL that uses something like:

select * from employees where epmloyeeTypeId in (select id from type where name = 'emp')

I replace the where with this:

select e.* from employees e
inner join type t on t.id=e.epmloyeeTypeId and t.name = 'emp'

Is it possible to do the same with the inverse in case its is a not in (like below) instead of an in clause?

INSERT into Subscriptions(ProjectId, RecordTypeCID, NTID, Active, Added, LastUpdate, UpdateBy)
 SELECT @ProjectId, RecordTypeCID, @NTID, 1, GETDATE(), GETDATE(), @NTID
 FROM @Check CHK
 WHERE CHK.ActiveStatus=1
        And Not Exists (SELECT SubscriptionId FROM Subscriptions
                        WHERE ProjectId=@ProjectId
                        and NTID=@NTID
                        and RecordTypeCID = CHK.RecordTypeCID
                        )

Additional considerations

Can I do this:

INSERT INTO Subscriptions(ProjectId, RecordTypeCID, NTID,Active, Added, LastUpdate, UpdateBy)
SELECT @ProjectId, RecordTypeCID, @NTID,1, GETDATE(), GETDATE(), @NTID
FROM @Check CHK
    LEFT JOIN Subscriptions subs ON subs.RecordTypeCID = CHK.RecordTypeCID
        AND NTID = @NTID
        AND ProjectId = @ProjectId

        AND CHK.ActiveStatus = 1
        AND subs.SubscriptionId IS NULL


texts:
Looking into postgres documentation for alter table, it seems regular constrains can be marked as DEFERRABLE (more concretely, INITIALLY DEFERRED, which is what I'm interested in).

Indexes can also be associated with a constraint, as long as:

  The index cannot have expression columns nor be a partial index

Which leads me to believe there is currently no way to have a unique index with conditions, like:

CREATE UNIQUE INDEX unique_booking
  ON public.booking
  USING btree
  (check_in, check_out)
  WHERE booking_status = 1;

To be INITIALLY DEFERRED, meaning, that the uniqueness 'constraint' will only be verified on the end of the transaction (if SET CONSTRAINTS ALL DEFERRED; is used).

Is my assumption correct, and if so, is there any way to achieve the intended behaviour?

Thanks


texts: What's the most appropriate type of index to use for this function-based query?

texts:
I'm using Postgres 9.5.  I'm going to be running a lot of this kind of query

SELECT *
FROM mydb_article
where regexp_replace(url, '\?.*$', '') = :url_wo_query_string

so I figured I would need to create a functional index on my table for that operation.  So I tried

mydb=> CREATE INDEX my_article_idx ON mydb_article regexp_replace(url, '\?.*$', '');
ERROR:  syntax error at or near "regexp_replace"
LINE 1: CREATE INDEX my_article_idx ON mydb_article regexp_repl...

My question is, what's the most appropriate type of index I should be using and if its the above, what else do I need to do to get things to work right?


texts:
I am trying to run a script that will batch insert over 20 million records into a table 10,000 at a time. At the start of the run it seems to be working fine. Although once a number of records had already been inserted (270,000) the script began to slow done considerably. It took 23 hours to insert another 30,000 records. My best guess is that as the number of new records increases the part where the script checks to see if the new record already exist is taking much longer. I have created indexes of the tables used in the script but I need shave off run time for this script. Any help would be much appreciated. My script is below.

CREATE NONCLUSTERED INDEX [plan2TMP] ON [dbo].[plan2]
(
       [l_dr_plan1] ASC
)
INCLUDE
(
l_address,
l_provider
)ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [nameTMP] ON [dbo].[name]
(
       [dr_id],
       [nationalid]
)ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [plan1TMP] ON [dbo].[plan1]
(
[dr_id],
[cmt]
)ON [PRIMARY]
GO

DECLARE @BatchSize int = 10000

WHILE 1 = 1
BEGIN

INSERT INTO plan2(
                     l_dr_plan1
                     , l_address
                     , l_provider
                     )
SELECT TOP (@BatchSize)
                     dr1.link
                     ,ad1.link
                     ,dr.link
from plan1 dr1
INNER JOIN name dr ON dr1.dr_id = dr.dr_id
INNER JOIN name2 dr2 on  dr2.nationalid = dr1.cmt
INNER JOIN address1 ad1 ON ad1.dr_id = dr2.dr_id
WHERE NOT EXISTS (
                  SELECT l_plan1
                  FROM plan2
                  WHERE ltrim(rtrim(dr1.link)) + ltrim(rtrim(ad1.link)) + ltrim(rtrim(dr.link)) = ltrim(rtrim(l_dr_plan1)) +ltrim(rtrim(l_address)) +ltrim(rtrim(l_provider))
                  )
AND dr1.cmt <> ''

IF @@ROWCOUNT < @BatchSize
  BREAK
END

The source data isn't really bad. I could pull out the ltrim/rtrim without encountering any problems. The reason I have the concatenation is that I couldn't come up with a better way to compare the values I was inserting to the values that had already been inserted.

The link value is a unique column in each table (but not the PK or FK). The plan2 table references the links of the plan1, address1, and name tables to connect those records in the back end and show them together in the front end.

The purpose of inserting 10k at a time was that the log file grew so large it brought the server memory to about 10 mbs. With the batch it will commit records in chunks and keep the log file from expanding out of control.


texts: Why would suser_name() not reflect an AD account name change?

texts:
One of our users' names was legally changed, so we changed their Active Directory username to match - from domain\oldname to domain\newname. However, when suser_sname() is called by this user in a stored procedure, it returns the old name, not the new one.

Googling led me to KB 946358 which suggests that their name is being cached on the server and not updated, presumably because suser_name() is calling LsaLookupSids. However, the workaround in that article involves restarting the server, and even if it was I'd still like to understand the problem.

If I change my context to theirs, the correct name comes back:

EXECUTE AS LOGIN = 'domain\newname'
GO
SELECT suser_name()   --returns 'domain\newname'

...I would have assumed that this would also call LsaLookupSids, and so would return the incorrect name. It seems likely that I don't really understand the mechanisms at work here.

Some observations that may matter:

This user doesn't have an explicit login on the server. But they are a member of an AD group that does. The changed name (domain\newname) appears in the result set for exec xp_logininfo 'domain\ADGroupName', 'members';  domain\oldname does not.
The user is calling suser_name() from within a stored procedure, called from a passthrough query in an Access 2003 MDB.
We've changed plenty of users' account names in the past, but have only observed this issue in the last week (two changes were made in last week, both seem to exhibit the issue).
The server is running Sql Server 2008 SP3 x64 on Windows 2008 R2 Datacenter edition.

What's going on? As the DBA, what might I do or where might I look to solve this?


texts: MySQL Server Config, Small Timeframe, Extreme Usage-Scenario

texts:
I want to configure a MySQL server for the following scenario.

A large amount of users will need to sign up for a university course selecting individual "timeslots". The signup page will open at a specific time when around 200 users will try to access the page.  In fact they are probably refreshing the page like crazy a few minutes before that.

There will be about 150 timeslots (rows) available that can be selected by the users. A web application will handle a transaction for each request trying to LOCK IN SHARE MODE a row ("timeslot") to check if it is still available and set it to a "booked" state if it is available. My database is using the InnoDB engine.

The the scenario is a lot of users doing a vast amount of SEELCTs and subsequently within a few seconds a vast amount of UPDATEs. Thinking of a basic Ubuntu MySQL server, what configuration options would you suggest to take a look at to make this "event" happen as smooth as possible. Things I want to avoid for example is the server to be overly durable writing changes to the hard drive during this period.

-- Edit

Just for information: The accepted answer below shows how this can be achieved without any LOCKING and explains very well how the MySQL will behave when trying the LOCKING approach.

However we managed to go through the scenario by changing the commit behavior of MySQL to only write to disk once every second and allowed 2GB of memory usage. We also had 8 thin instances running a rails application below a 4 worker nginx (basically default setup with SSL). We ran it on a desktop i5 system with 12GB of memory (which was much too much) and the server hardly reached a load of 1 (out of 4). So, if you are running a similar scenario: don't panic.


texts:
I am inserting rows in a table, based on a value and on the existence of a row, which  I get from SELECT statements. This function can be run concurrently, so I need to do some extra obviously. The basic steps are (pseudocode)

function doStuff(int someID){
    found = `SELECT someIDFROM targettable`
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
 }

The problem is ofcourse that if process1 reads uniqueID, figures it should insert the value, and process2 does the same before the write is finished, I get 2 lines in my targettable. This is possible (someID is not unique for that table), but in this case unwanted because it was done on wrong assumptions because of the concurrency.

Now my assumption/feeling is that a transaction alone is not enough for this. The second process will have consistent reads I think, but that does not help prevent the second INSERT, does it? so this will not do (I think, please advice me on this. This could be where I'm going wrong). (I'm quite sure at the moment this is not enough to ensure correct behaviour, because of testing)

function doStuff(int someID){
    'START TRANSACTION HERE'
    found = `SELECT someIDFROM targettable where someid = someID `
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
    COMMIT TRANSACTION
}

From what I  read about InnoDB locking reads it could be that I should use those, but I'm not sure as I have never worked with them. It could also be the transaction is enough, and I'm just begin foolish.

My current idea is to use SELECT someID FROM targettable FOR UPDATE, which I figure will give me a lock. But I'm not sure what lock, since it depends on isolation level. This would look like so:

function doStuff(int someID){
    found = `SELECT someIDFROM targettable where someid = someID FOR UPDATE`
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
 }

I'm not sure how to cope with the value I get from another table, but that might be another thing.

Summarizing: Can I get away with just using a transaction, do I need to use a locking-read (if so, how?) or is there another option I missed?

Update: If I do something like

START TRANSACTION;
SELECT * FROM targettable WHERE someid = 3 FOR UPDATE

And query that select in another thread, I get no result (locked?) until this transaction is finished. But if I put an INDEX on that someid, I DO! I'm probably missing something, but I wasn't expecting that.
How can I be sure I don't insert this row twice?


texts: is there a way (clean or unclean) to eliminate a filestream data file on a production SQL Server database?

texts:
Is there a way (clean or unclean) to eliminate a filestream data file on a production database?

I forced a garbage collection on an empty filestream :

sp_filestream_force_garbage_collection @dbname = N'DB' ,   @filename = N'db_mod';

I tried this and memory opt and filestream is't actually enabled on db

SELECT name, description FROM sys.dm_os_loaded_modules WHERE description = 'XTP Native DLL'

this script gives the files in db:

select sdf.name AS [FileName],
   size/128 AS [Size_in_MB],
   fg.name AS [File_Group_Name]
   FROM sys.database_files sdf
   INNER JOIN
   sys.filegroups fg
   ON sdf.data_space_id=fg.data_space_id

FileName Size_in_MB File_Group_Name

db           7282        PRIMARY
   db_mod       1528        db_mod

Can I manage to eliminate this empty file and filegroup that prevents me from using db mirroring?

of course I tried

-- Remove the File

ALTER DATABASE db REMOVE FILE db_mod1 ;
-- Remove the Filegroup
ALTER DATABASE db REMOVE FILEGROUP db_mod ;

I get the error:

  Msg 41802, Level 16, State 1, Line 1
  Cannot drop the last memory-optimized container 'db_mod1'.


texts: Is an explicit KEY redundant if it is covered by a UNIQUE KEY?

texts:
I am auditing an application which I've been developing over the course of several weeks. I've noticed this in CREATE TABLE:

UNIQUE KEY `baz_3` (`baz`,`foo`,`bar`),
KEY `baz` (`baz`),

Is the explicit KEY redundant, as I think it is? I just want to make sure before I DROP it. From what I've read I do believe that the  UNIQUE KEY will act as an Index when the application performs WHERE baz='something' but MySQL seems so intricate that I find it prudent to ask here first.


texts: Postgresql: delete all comments from database

texts:
I have a database for which the comments on objects (PSQL command COMMENT) are just pollution for us: they are obsolete and in a language that no one reads in our organisation. Hence, I'd like to delete all these comments at once. There are hundreds of them and I don't want to write hundreds of command lines "COMMENT ON xxx IS NULL".

Anybody knows how to do this ?


texts: PostgreSQL truncating values of real numeric type

texts:
Using PostgreSQL 9.2 on Windows Server 2007, I have the following table:

CREATE TABLE aaa_dens (read_no INT, position REAL PRIMARY KEY);

Some values in position have 4 or more digits after the decimal point, as shown from a dump:

$ head dump.txt
"read_no"       "position"
    "1"     "150.83"
    "2"     "150.8308"
    "3"     "150.8316"
    "4"     "150.8324"
    "5"     "150.83321"
    "6"     "150.834"
    "7"     "150.83479"
    "8"     "150.8356"
    "9"     "150.8364"

However, SELECT * FROM aaa_dens; shows only a max of 3 digits after the decimal point:

 read_no | position
---------+----------
       1 |   150.83
       2 |  150.831
       3 |  150.832
       4 |  150.832
       5 |  150.833
       6 |  150.834
       7 |  150.835
       8 |  150.836
       9 |  150.836

Furthermore, trying to convert position to numeric(8,4) yields an error, as if it had rounded reads 3 and 4 to 150.832:

ALTER TABLE aaa_dens ALTER COLUMN position TYPE NUMERIC(8,4);
ERROR:  could not create unique index "pk_aaa_dens"
DÉTAIL : Key ("position")=(150.8320) is duplicated.

What exactly is happening here and how to fix it?


texts: How to create an UTC index from a 'DATE' typed column with Oracle?

texts:
I have a Oracle database with columns of type 'DATE' which store timestamps in local time zone. I need to query this column with timestamps in UTC timezone.

How do I create an index on the column that can be used when queried with UTC timestamps and how to format the query?


texts: Can't login ORA-28031: maximum of 148 enabled roles exceeded

texts: I can't login - and receive the error code above. How do I fix this?


texts:
I just migrated my primary's synchronous mirror to a new server and I've noticed that there are 150 to 230 waiting tasks on the mirrored instance (based on the activity monitor.)  I don't recall if the prior mirror had a similar number, however it seem high to be considering the primary only has 9 waiting tasks total and all of them are related to the mirroring process.

I've tried the sp_BlitzWho and the sp_whoisactive scripts to try and identify what is executing and waiting however they return nothing for the default execution.  When I run sp_whoisactive @show_system_spids = 1 I see mirroring commands with wait info attached but only around 16 and they are all attached to the master database.  Using the plain old sp_who which returns on average 250 results with a good 2/3 of them being the command DB MIRROR.  They don't seem to be attached to any database and all of the mirrored databases say they are synchronized.

Why would the mirror have so many waiting tasks to synchronize mirror 5 databases with very little data changes happening on the primary?  Is this normal behavior?

Update

I've rebooted the mirror and the wait tasks come right back.  I've reviewed the disk IO and some of the files will have a 8 to 10 ms wait time on them which doesn't make a lot of sense to me as very little is being transferred from the primary < 100KB per sec on average.  Nothing seems impacted by the waiting tasks on the primary or secondary.

Environment Details

Primary -

64 Core / 488 GB
2 NVMe drives for tempdb data and log
Nitro Hypervisor

Mirror -

128 Core / 1.9 TB
(2) SSD drives for tempdb data and log
Xen Hypervisor
Common -
VM Windows 2012 R2
SQL Server EE 2008 R2 10.50.6000.34
(2) 1.4 TB Drives with 3000 Dedicated IOPS (Data)
(2) 300 GB Drives with 800 Dedicated IOPS (Log) Only one log per DB
Power Mode High Performance
25 Gb/s NIC
MAXDOP Set to 8
Parallelism Threshold set to 50
Instant Disk Initialization granted to service account

Mirroring Config

Sync Mirroring High Availability no witness
Mirroring 5 databases

Update 2

All of the waits seem to be focused on DBMIRROR_SEND wait type.  I just applied a security update for SQL Server (KB4035316) but that made no improvement on the waiting tasks.

Update 3
sp_Blitz output

- ANSI NULL Default Enabled
- Agent Jobs Without Failure Emails
- Agent XPs
- Backups Not Performed Recently
- Database Mail XPs
- Database Owner <> SA
- Extended Events Hyperextension
- Extended Stored Procedures in Master
- Forced Parameterization Enabled
- Forced Parameterization On
- Inactive Tables Without Clustered Indexes
- Jobs Owned By Users
- Last good DBCC CHECKDB over 2 weeks old
- No failsafe operator configured
- Possibly Broken Log Shipping
- Recursive Triggers Enabled
- Server restarted in last 24 hours
- Stats Updated Asynchronously
- Sysadmins
- System Database on C Drive
- Tables in the Master Database
- TempDB Has >16 Data Files
- Transaction Log Larger than Data File
- User Databases on C Drive
- User-Created Statistics In Place
- clr enabled
- cost threshold for parallelism
- max degree of parallelism
- max server memory (MB)
- remote admin connections
- show advanced options


texts:
Suppose I have a website that communicates with a database.

In some page of my site i want to exhibit a list with all the students in my database. There are two kinds of students: regular students, that only participate in regular classes (with attributes such as student_id, name, email, password) and special class students, students who participate not only in regular class but also in special extra classes (with all the attributes the regular students have plus an attribute for the special_class_id).

In the exhibition of the students on the mentioned page, I want it to exhibit the student differently depending on whether it's a special class student or not.

So i'm thinking here what would be the best database design in order for the code for the exhibition of the students to have the best efficiency/performance.

Option 1

Two tables on my database, one for all students (with student_id,name,email,password)  and one extra for special class students (with student_id and a special_class_id corresponding to the id of the special class the student participates). Let's name the table with all students by students and the other one by special_students.

With this option, for the exhibition page, the code would pick each row of the students table and display it. It could then check whether that student_id was inside any row of the special_students table, and if it was, it could exhibit that student differently.

Option 2

Another option would be having only one table on my database with an extra special_class_id column. Then, for regular students this column would have some default value (maybe 0), and for special class students, this column would have value identifying the special_class_id.
With this option for the exhibition page, it seems to me that the code would be faster since it would query only one table .. but on the other hand, adding this extra column seems a bit sloppy.

Would there be a significant performance advantage on the second option, over the first one, considering an hypothetical situation where the site might have 10 million visitors a day? Can this sloppy extra-column interfere in some way and cause problems?   Is there any other option for the database design?

Thanks a bunch in advance.


texts: How can I copy the definition of a field from one table to another at design-time?

texts:
I have a table with ten fields, f1 (being an integer field) to f10 (being a character field). In between there are other integer, character and date fields.
I would like to copy those field definitions to another table, without needing to re-write the whole definitions. I'm working with the Data Dictionary, who seems not to cover this feature.
Edit after first answer
The first answer mentions how to do this at runtime, but I'm specifically looking for a solution at design-time.
Edit
I've found there are different commandline utilities which can be used for working with Progress database: ProDB for creating a new database, ProUtil for doing several tasks (but I don't find a ProUtil <add a field to a table>).
How can I achieve this?


texts:
Checking my Native backup this morning something wierd happened. I will describe the problem here.

1) The msdb table backup set shows me the backup was done successfully.

2) The errorlog shows me that the backup was done successfully.

3) The job history doesn't show any entry, even it is successfully or error.

4) Checking the backup media family table the physical device name column shows me something like this: {F2F1E11C-589A-4AAE-8880-0120D8D1998D}1
and other days it shows me the right directory of my backups.

4) The DPM also do system state and database backup after the SQL Server Backup and runs well.

Well.. if anyone need more informations, please let me know.

Thanks.
Marcos Freccia
@SqlFreccia


texts: What are the available automatic failover solutions for MariaDB Master-Slave replication on Windows Server?

texts:
I am setting up a Master-Slave or Master-Master setup in MariaDB on two seperate servers running Windows Server. I also need to configure an automatic failover solution where client read requests gets redirected to the slave in case the Master is down.
I am not familiar with MariaDB/MySQL but did a good amount of research online. However the solutions are all in Linux. i.e HeartBeat, Keepalive. I understand there is MariaDB MaxScale but Enterprise Subscription is currently not an option.
Can I please get a nudge in the right direction?


texts: Oracle - Search a value in all columns of a table

texts:
Here is my issue.

I have a Oracle table which contains 53 columns. And i would like to do a search for a value for all columns. And i do not want to put all columns in the where conditions.

Is there any optimistic way to accomplish this?

Any help would be appreciated.


texts: MySQL JOIN is fast, but selecting column is slow

texts:
My logs table has half a million rows.  Each row has an indexed log_level_id column pointing to table log_levels (with only 5 rows).  On log_levels, id is an auto-increment primary key and label has no index.

The super simple query below runs fast on MySQL 5.5 but suddenly screeches to a grinding halt on MySQL 5.7 (e.g. less than 1/4 a second to over 10 minutes):

SELECT logs.id ,log_levels.label
    FROM logs
    LEFT OUTER JOIN log_levels ON log_levels.id = logs.log_level_id

Why would the lack of index on log_levels.label cause this slowdown? Why would I need to place an index on this column when I'm not referencing the column in WHERE or ORDER?

Without an index on label, EXPLAIN shows:

table: log_levels
type: ALL
possible_keys: PRIMARY
key: ??? shouldn't this resolve to PRIMARY?
key_len:
ref:
rows: 5
Extra: Using where; Using join buffer (Block Nested Loop)

If I add an index on log_levels.label, EXPLAIN changes to:

table: log_levels
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len:1
ref: logs.log_level_id
rows: 1
Extra:

I've never experienced a difference in query speed based on whether there's an index on a select column (label) that does not appear in WHERE or ORDER clauses.

Any suggestions would be appreciated.


texts: We are doing a full backup once a week and a daily diff between that.  The total db is 80Gb, the diffs start out under 1Gb, then gradually grow to approximately 3-5Gb which is ok, but then all of a sudden jump to 40Gb almost every week.  Why would this happen?  How do I investigate?  I'm reasonably sure that the actual db usage is uniform w/o spikes.


texts: If there is no Data in the Production database can we restore it to Dr server

texts:
I have a Production server and I restoring all databases to DR server. One database does not have the data and I am trying to restore it too, the restore job is running from past 24 hours.

Can I run the restore successfully with out any issues?
How can I go ahead with this restore?

Please let me know, I got tied up with this restore operation.

DB size in production is showing 7 MB.


texts: Indexing for query containing xml column

texts:
I have below queries which performs little slow.So i am planning to create index.I created primay xml index and now performance improved little bit(by looking at execution plan) .Queries are shown below.

Update account set   [dailybalance].modify('replace value of   (/Root/Row[date=''2013-02-04'']/Balance/text())[1] with   (/Root/Row[date=''2013-02-04'']/ Balance)[1] +280')   where   [ID]=257 and [Date]='28-Feb-2013'  and  [dailybalance].exist('/Root/Row[date=''2013-02-04'']')=1;

Update account  set   [dailybalance].modify('replace value of   (/Root/Row[date=''2013-02-04'']/Transaction/text())[1] with   (/Root/Row[date=''2013-02-04'']/ Transaction)[1] +280')   where   [ID]=257 and [Date]='28-Feb-2013'  and  [dailybalance].exist('/Root/Row[date=''2013-02-04'']')=1;

The table has below structure

CREATE TABLE [dbo].[account ](
    [ID] [int] NULL,
    [Type] [char](10) NULL,
    [Date] [date] NULL,
    [Balance] [decimal](15, 2) NULL,
    [TRansaction] [decimal](15, 2) NULL,
    [mybal] [decimal](15, 2) NULL,
    [dailybalance] [xml] NULL,
    [AutoIndex] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_BalanceTable] PRIMARY KEY CLUSTERED
(
    [AutoIndex] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I am using sql server 2008 r2 express.So i think i cannot use full text search. So please advice me how the index should be created so that above query performance is improved.


texts: Type of Foreign Keys for a Dimensional Model Fact Table?

texts:
I've been reading about dimensional models, and in particular fact tables, but I haven't really seen one in action (i.e. the actual content), just diagrams of the table structures and column names

From my reading it sounds like you only put the primary (i.e. primary auto incremented) keys from the dimension tables into the fact table, and not any of the actual dimensional information in the table

Is this accurate? The example I have in my mind looks like this:

+---------+------------+-------------------+
| date_fk | product_fk | units_sold (fact) |
+---------+------------+-------------------+
|  3      | 12         |  1154             |
|  3      | 50         |  484              |
+---------+------------+-------------------+

Where date_fk is the primary auto incremented key for the dimDate dimension table, product_fk is the primary auto incremented key for the dimProduct table, and units_sold is the actual fact, the number of units sold that day for that product

Please confirm that this is the correct method; my dimension tables also uses numeric natural keys so it's tempting to use those instead of the adjacent primary keys to avoid having to do JOINs for every query, however I am committed to using dimensional modelling so I need to do it right


texts:
I had a less less memory usage in mysql 5 vs 5.5 do they do something drastically different? I went from around 100mb to about 1 gb of memory usage.

Does memory usage in 5.5 also grow over time?


texts:
I have 3 tables, one of them is join table:

user table:

CREATE TABLE `user` (
`id`,
`name`)

media table:

CREATE TABLE `media` (
`id`,
`value`,
`type` enum('M', 'T'))

user_media table:

CREATE TABLE `user_media` (
`user_id`,
`media_id`)

I want to create a select which lets me search through user.name and media.value and return formated values.
Ex:

INSERT INTO `user` (`id`, `name`) VALUES
(1, 'Luke'),
(2, 'Vader'),
(3, 'Gregory');

INSERT INTO `media` (`id`, `value`, 'type') VALUES
(1, 'luke@mail.com', 'M'),
(2, 'skywalker@mail.com', 'M'),
(3, '123456789', 'T'),
(4, '123', 'T');

INSERT INTO `user_media` (`user_id`, `media_id`) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 4);

search: luke@mail.com
output:
userId    |email          |tel
1         |luke@mail.com  |123456789

search: 123
output:
userId    |email          |tel
1         |luke@mail.com  |123456789
2         |null           |123

search: Gregory
output:
userId    |email          |tel
3         |null           |null

UPDATE

SELECT u.id AS user_id,
   mm.value AS mail,
   mt.value AS tel
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id
WHERE mm.value LIKE "%luke@mail.com%"
  OR mt.value LIKE "%luke@mail.com%"
  OR u.name LIKE "%luke@mail.com%"

Result:

user_id mail               tel
1       luke@mail.com      luke@mail.com
1       skywalker@mail.com  luke@mail.com
1       123456789          luke@mail.com
1       luke@mail.com      skywalker@mail.com
1       luke@mail.com      123456789

II

SELECT u.id AS user_id,
   mm.value AS mail,
   mt.value AS tel
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id AND mt.type="T"
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id AND mm.type="M"
WHERE mm.value LIKE "%luke@mail.com%"
  OR mt.value LIKE "%luke@mail.com%"
  OR u.name LIKE "%luke@mail.com%"

Result:

user_id mail               tel
1       luke@mail.com      null
1       luke@mail.com      null
1       luke@mail.com      123456789

III

SELECT u.id AS user_id,
   mm.value AS mail,
   mt.value AS tel
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id
WHERE mt.type="T"
  AND mm.type="M"
  AND (mm.value LIKE "%Gregory%"
   OR mt.value LIKE "%Gregory%"
   OR u.name LIKE "%Gregory%")

Result: 0


texts:
I get the following error message when trying to restore the database from backup using RMAN.

rman target /

RMAN> set dbid=130474031
RMAN> configure device type 'sbt_tape' clear;
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> sql 'alter database mount';
RMAN> restore database;

Starting restore at 22-DEC-15
using channel ORA_DISK_1

creating datafile fno=1 name=/opt/oracle/oradata/prod/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/22/2015 12:47:01
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/opt/oracle/oradata/prod/system01.dbf'
RMAN>

EDIT

RMAN> restore database;

Starting restore at 22-DEC-15
using channel ORA_DISK_1

creating datafile fno=1 name=/opt/oracle/oradata/prod/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/22/2015 14:22:01
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/opt/oracle/oradata/prod/system01.dbf'

RMAN> exit

Recovery Manager complete.
[oracle@DR-Corebank ~]$ id
uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba)
[oracle@DR-Corebank ~]$ cd /opt/oracle/oradata/prod/
[oracle@DR-Corebank prod]$ touch aaa
[oracle@DR-Corebank prod]$ ls -ltr
total 875772
-rw-r----- 1 oracle oinstall 298631168 Dec 22 14:22 control03.ctl
-rw-r----- 1 oracle oinstall 298631168 Dec 22 14:22 control02.ctl
-rw-r----- 1 oracle oinstall 298631168 Dec 22 14:22 control01.ctl
-rw-r--r-- 1 oracle oinstall         0 Dec 22 14:22 aaa
[oracle@DR-Corebank prod]$ pwd
/opt/oracle/oradata/prod
[oracle@DR-Corebank prod]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/cciss/c0d0p3     1.1T  163G  850G  17% /
/dev/cciss/c0d0p1     244M   18M  213M   8% /boot
tmpfs                 7.9G     0  7.9G   0% /dev/shm
/dev/mapper/mpath0    493G  248M  467G   1% /opt/oracle2/u002


texts: Things affected globally in a postgresql transaction

texts:
Correct me if I am using the correct term: "affected globally" I mean, if I increase the sequence value, it will be increased for all other running transactions without the need for commit....

I know that sequences have this behavior, but my doubt is there is a list with some other roles that are affected globally, if i update it inside a transaction?

For example, if I set alter table mytable enable trigger all; this will affect the trigger behavior only in the local transaction or in other transactions, note that this is only a example, i am thinking about a lot of other things that i am need to be carefully about it.


texts: How to drop table with all reference to this table

texts:
I want to drop a table, and this table has be many table's foreign key.

So I want to drop all those reference (just refernece, not other table) within the drop table process.

Is there any simple SQL, such as:

Drop table table_name with all reference ?


texts: MongoDB Replication - “Failed with No route to host”

texts:
Hi,

Running MongoDB version 3.4.9 on two separate servers running Centos 7.

Trying to add a create a replica set but when ever I try to add a second server I get this error:

devshardcfgrs:PRIMARY> rs.add("s2-b6:26052")
{
        "ok" : 0,
        "errmsg" : "Quorum check failed because not enough voting nodes responded; required 2 but only t                                                                                he following 1 voting nodes responded: s1-b5:26051; the following nodes did not respond affirmatively: s                                                                                2-b6:26052 failed with No route to host",
        "code" : 74,
        "codeName" : "NodeNotFound"

What am I doing wrong?

This is my /etc/hosts file:

127.0.0.1   localhost

::1         localhost

172.24.1.42 s1-b5

172.24.1.43 s2-b6

All help is appreciated :)


texts: Postgres: insert new row in table if the table does not have records where column_name = 'abc' occurs N times?

texts:
I would like to do this in one transaction
So let's take table with columns:
user -> [first_name, last_name]

The use case is that we don't want to insert a new record into a table, if the table already has records where user.first_name = 'ABC' occurs 5 times;
INSERT INTO user VALUES
('john', 'doe') ...

Apologies if this is simple, am a developer and SQL is not my strong point.


texts: I was interrupted while transferring a very large table. How can I pick it up from where it stopped?

texts:
I wrote a python script to select and insert rows between tables in different databases (from SQL Server to Postgres). the table has around 2000000000 rows and the transfer stopped somewhere in the middle of it. I tried to select the rows with an offset as the number of rows already transferred. But count(*) takes up too much server resources and the server becomes unresponsive when I try. (memory usage rises from 100MB to ~8GB in a very short time)

I'm wondering if there's a way to pick up the transfer from where it stopped. It took around 3 days to transfer the number of rows I currently have in Postgres.

Thanks.

Also, I know the approximate number of the rows. So maybe I can do something like this: if there are approximately 12392320 rows, delete everything after the 12390000th row and just start from 12390001. Is something like that possible?


texts: Is possible to mysql change some engine tables automatically?

texts:
I have 3 WordPress blog hosted on cPanel using MySQL. All 3 of them were working fine until yesterday. Now, the support says that “some tables changed from 'MyISAM' to InnoDB”.

In the logs I get:

[mydb.wp_usermeta] Error: Unknown storage engine 'InnoDB'
[mydb.wp_usermeta] error: Corrupt

And the same for other tables.

I don't know why those tables changed the engine. Is it possible for MySQL to have changed it automatically for some reason?


texts:
At the moment I'm partitioning my table daily by
PARTITION BY RANGE COLUMNS(date)

On a daily basis I load data into that table, I have billions of rows in that table. I was thinking on switch to exchange partition for better performance. However the data is getting from multiple sources at different times. I do however can do exchange partition if I could do partition the table by (date, sourceId).
Is that possible? Do I have to use sub-partitions or is there other ways to solve it?


texts: Unable to Generate Scripts for Publications

texts:
Version is SQL 2008 R2 Enterprise
I am attempting to document our replication configuration.

Replication is running and working fine:

In SSMS I right click on a publication and select Generate Scripts.
I receive the error

SQL Server cannot generate the SQL script.
Additional Information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Could not find stored procedure ''. (Microsoft SQL Server. Error: 2812)

The T-SQL statement EXEC sp_helpsubscription 'SubscriptionName'
returns the following:
Msg 2812, Level 16, State 62, Procedure sp_MSrepl_helpsubscription, Line 537 [Batch Start Line 268]
Could not find stored procedure ''.

Any ideas on what I could check?


texts: Mongorestore not restoring full collection and not returning an error

texts:
I know this topic is old, but I think I am having a similar problem. I have been searching from a solution all over Stackoverflow

I am running a mongodump followed by a mongorestore and in 2 out of 12 collections the numbers of documents restored are way less than the number of documents on the backup. The thing is there are no errors.

Bellow is the output fot the 2 collections on the dump

  2020-01-22T10:16:26.961-0300 done dumping montele.usuario (960
  documents)

  2020-01-22T10:29:35.268-0300 done dumping montele.negocio (86851
  documents)

And now the output for the restore

  2020-01-22T10:35:21.121-0300 finished restoring montele.usuario (31
  documents, 0 failures) 2020-01-22T10:37:29.974-0300 finished restoring
  montele.negocio (26120 documents, 0 failures)

Interesting enough .... if I run the mongorestore again without dropping the db this happens (I did this to see if it would add the remaining data):

  2020-01-22T11:30:41.914-0300 finished restoring montele.negocio (0
  documents, 86851 failures)

With the failure code being:

  E11000 duplicate key error collection: montele.negocio index: id dup
  key:

I hope someone can shine a light and point me in right direction.


texts: How and what is the most efficient way to join two tables, retaining a particular field from both?

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

Table 1

ID
Component
Data1
Data2

Table 2

ID
Component
Data3
Data4

If I have rows in these tables, with items Component1, Component2, Component3, as entries in the component field for table 1, and Component3, Component4, and Component5 in the component field for table 2, I am looking to query such that I can get all results like:

Component1, data1, data2, data3, data4
Component2, data1, data2, data3, data4
Component3, data1, data2, data3, data4
Component4, data1, data2, data3, data4
Component5, data1, data2, data3, data4

Note that there are multiple entries of Component, and so I will need to group by the field to get aggregate summary data (using count, avg, etc. on the data fields). I am using SQL Server 2012 but need a solution that will work on 2005 ideally, a portable solution that works on other RDBMS would be preferable, but not necessary right now.

My question is two fold:

1) How do I perform this query? Inner, outer, and full joins seem useless, as they only let me retain Component from either one table or the other, not both. I'm unsure if a Cartesian product is the solution, I suspect it may be but I can't quite see how.

2) What is the most performant way of handling this, given that the number of entries in both tables will potentially be massive? Currently I have two SPs that get the summary data for the tables individually. As the SPs return only a handful of rows - maybe 20 to 30 in practise - because they summarise the data, is it more efficient/possible to perform the solution to part 1) above against the SPs rather than as a single query? An alternative I have is to simply merge the results of the SPs in code, I would rather do this on the database but efficiency will be quite important.

Any help appreciated- and I apologise if this question has been asked, it seems like it should be an obvious and common problem, but I couldn't find any help on Google et. al. though it could be that I was just using the wrong search terms!


texts: Could not obtain information about Windows NT group/user, error code 0x6ba

texts:
When I run
EXEC master.dbo.xp_logininfo [DOMAIN_B\GroupName],'members'

I am getting an error:
Could not obtain information about Windows NT group/user 'DOMAIN_B\GroupName ', error code 0x6ba.

The SQL Server instance (MS SQL Server 2019 Standard edition, named instance) is installed on the machine in another domain, eg. DOMAIN_A.
The service account for SQL Server database engine is (for test reasons) domain admin in DOMAIN_B.
I can create a login for Windows Group (DOMAIN_B\GroupName) or individual user of the DOMAIN_B without any problems or errors – so the instance can communicate with the DC/AD.
0x6ba = 1722 The RPC Server is Unavailable error – we’ve checked almost every issue about this error found on the internet (on DC or remote computer) with no success – everything seems fine, replication is finished, all services are running, no firewall or permission issues.
Any ideas what else to check to be able to list the members of the Windows Group on SQL Server instance (need it for application purposes)?


texts: Randomly generate data under conditions for sqlite

texts:
As part of creating a machine learning exercise, I have an sqlite database that looks like this:

The "Pizza", "DateDimension" and "Customer" tables are already populated, there are 5 different pizzas and 10 customers.
How could I generate random data for the sales table, but give conditions to create a pattern?
For example, on Monday/Tuesday a random sales figure between 15 and 25, on the other days of the week a random sales figure between 10 and 20, between 20 and 30 on the weekend.
Knowing that for a day, a sale of 15 pizzas would be for the customer of ID=1 a sale of 2 pizzas of ID=2 and 4 pizzas of ID=3,
for the customer of ID=2 a sale of 3 pizzas of ID=2, and
for the ID=3 customer a sale of 3 pizzas of ID=4 and 3 pizzas of ID=2.
This makes a total of 15 pizzas, of which 8 ID=2 pizzas, 4 ID=3 pizzas, 3 ID=4 pizzas, divided between three customers.
I don't know if I'm clear enough in my explanations, basically we need a random number of pizza sales for each day, and that this number is itself randomly split between the types of pizza and the customers, under certain conditions in order to follow a pattern in the number of sales per day.


texts: MySQL binlog format change from MIXED to ROW

texts:
One of my customer has MySQL master-slave setup at on-premises, where the binlog format is kept to be MIXED. Now, we are planning to migrate the DB to AWS RDS using DMS, the binlog format should be kept as ROW. I am not sure whether MIXED to ROW at this point of time at source will impact the source DB or replications. I see MySQL docs talking about the implication of STATEMENT to ROW change, but not from MIXED to ROW. Can someone help me on this.?
If the above is not doable, another plan is to go with Daisy chain approach, where an EC2 instance is created as slave connected to the slave DB of on-prem. This EC2 slave should have the binlog format as ROW. Even in this case, at on-prem the binlog would be MIXED and EC2 slave would be ROW. I am not sure whether this approach (within the same DB) will work, or anything break.
Any expert suggestion/solution (with MySQL document reference for the solution, if possible) would be much appreciated.


texts: EER Diagram - subclasses which are neither disjoint nor fully overlapping

texts:
Suppose we are writing an enhanced entity-relationship diagram (EERD) for a company's database. This company offers two services A and B. If the two services were disjoint, then this might look something like:

In many cases you could request both services from the company, in which case we would use an "o" instead of a "d".

A person cannot request service B by itself. A person can request services as often as they want. But it's highly unlikely anyone would request services more than once every 10 years or so.

Suppose that for this company, you can either request service A and B together, or service A only. What is a good way to describe this constraint succinctly in the diagram?


texts: How often does SQL Server refresh group policy?

texts:
Does SQL Server check against the Windows GPO on the local server it is running on or with the domain controller?
Does SQL Server update the GPO at its own interval or immediately when the server has its GPOs applied? If it has its own interval, is there a specific refresh rate? I am having difficulty finding docs on this so I figured I would ask the community.
Thanks


texts: Masters database not replicating on slave after restarting client

texts:
I'm trying to implement master slave replication and It's works fine with no errors, database is replicated on slave, but when I close my servers and open them again and add something in masters database it's not replicated in slave , do I have to follow these steps every time I start my server?

Setup on Masters server

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.20'
IDENTIFIED BY 'secretpassword';
mysql> FLUSH PRIVILEGES;

mysql> use mydb;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> exit;

my.cnf file

[mysqld]
log-bin=mysql-bin
binlog-do-db=mydb
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

in terminal again

# service mysqld restart

mysql > SHOW MASTER STATUS;

# mysqldump -u root -p mydb > mydb.sql
# scp mydb.sql 192.168.1.20:/opt/

SETUP SLAVE 2 SERVER

 # vim /etc/my.cnf
 [mysqld]
 server-id=2
 replicate-do-db=mydb

 # /etc/init.d/mysqld restart
 # mysql -u root -p mydb < mydb.sql

 mysql>  CHANGE MASTER TO MASTER_HOST='192.168.1.10',
-> MASTER_USER='repl_user',
-> MASTER_PASSWORD='secretpassword',
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=107;

mysql> SLAVE START;
mysql> show slave status \G

Also it's is only replicating one database by dumping one database on slave server

 # mysqldump -u root -p mydb > mydb.sql

How can I dump all the databases from masters to slave server?


texts:
I have 43 tables on 2 different servers which I have to sync regularly. Because of our network policy and IG reasons, we can't have link server object between these two servers. Standard procedure so far is to create a SSIS package that will do the transfer however, I have come across situation where I need 43 of those. As much as I would love to get on with the task, there has to be a smarter way of doing this. I have seen some articles about generating packages dynamically using C# but C# ain't my strongest suit.

Is there a way from within SSIS to perform this task dynamically?

I was thinking like maintaining mapping tables, one with source and destination tables and another with source and destination column mappings and looping through it but obviously metadata needs to be refreshed to achieve the successful execution.

Any thoughts or ideas will be highly appreciated.


texts: How to model a relational database for a sales business domain?

texts:
I'm trying to come up with a solution that essentially boils down to visualising sales data, across many depts, in many stores, in many areas. The most significant challenge is a way to conceptualise/store this data in a meaningful way.

Store breakdown:

8 stores per area
3 areas per region
3 regions nationally

Department breakdown:

50 micro departments per store (think “chocolate”, “confectionary”, “headphones”) that belong to:
8 major departments (e.g. “chocolate” and “confectionary” belong to “grocery”, “headphones” and “CDs” belong to “entertainment”) that, in turn, belong to:
2 department divisions (each division would hold 4 of the major depts)

Sales data is generated on a weekly basis and then we aggregate it how we see fit. Each week a new spreadsheet is created and each store has three columns devoted to their store, which breaks down each micro department, the sales this year, the sales last year (and then we look at % gains/losses).

So what I'm trying to do is conceptualise a way to store the data meaningfully (all weeks, all departments, all stores) and then we'll perform some analysis on it later to extract what we need.

My current thinking is:

each store instance would have a store ID, name, area & region.
each micro department instance would have an ID, name and the major dept it belongs to
each week would have a week ID (we use financial weeks) and alternatively a date
we then create sales instances with a store ID, department ID, sales TY, $ sales LY and a week #

Is there a better way of representing the data or something that I'm missing?


texts: Connecting to AWS RDS DBInstance with MySQL workbench

texts:
I am trying to evaluate AWS RDS to use as possible DB for our application. I have created a DBInstance(micro) and added IP security group. I am able to connect to the DBInstance from Xampp shell command line and i am able to run queries from that.

But when i try to connect with workbench to the same DBInstance, it gives me error that 'MySQL server has gone away'.
Following are the steps i followed to connect with workbench:

Give the Endpoint address of DBInstance as Hostname.
Port : 3306
Set the username and password to master user and its password.

My database is completely new, I have just created a plain DB and trying to connect with workbench right away. I am using workbench version 5.2.25.

Any ideas on what am i missing? And yes, I am just a beginner with MySQL and AWS.

Edit: As asked by RolandoMySQLDBA, i have run the query SELECT USER(),CURRENT_USER(); and in the result i get one row with two columns USER()=ei@xxx.xx.xxx.xxx and CURRENT_USER()=ei@%. ei is the master username for my RDS DBInstance. And the column USER() has my ip address after @.

I tried using both ei@xxx.xx.xxx.xxx and ei@% with workbench, but i get access denied error .


texts: Do all nodes of a SQL Always-on Availability group need to be the same version of SQL?

texts:
We currently have a number of SQL 2012 server pairs set up with SQL Availability groups where we are just syncing data on the secondary for a RO copy, no listener is currently set up for HA.

We now have an opportunity to add a listener so we can utilize the secondary for read-only and HA fail-over.  With HA fail-over, we started thinking of down the road scenarios like upgrading to SQL 2016.

Can we add a SQL 2016 server as a secondary node to our existing AG and when we're ready to migrate, fail-over the SQL 2012 primary to the SQL 2016 secondary and then make it the primary node going forward?

We don't like doing upgrades in place so migrating data to new servers with minimal downtime can be risky, especially if outage windows are small.  Just wondering if this is even an option to experiment with.  In theory, it could definitely minimize the data and server migration windows to a few minutes, if you can mix SQL versions in an AG.  I haven't come across anything that states you cannot do this but haven't had the time to try.


texts: How to set up triggers in MySQL for copying a field

texts:
I have an in-house application that I've put together, and I'm VERY new to SQL in general.

I have a table that contains most of our data. Let's pretend it looks like this.

Matrix

|Name       | Description        |
----------------------------------
|Server01   | First Server       |

Now I've added another table and let's pretend it looks like this.

OtherInfo
|Name       | Price              |
----------------------------------
|Server01   | 100                |

I've done an initial copy of data from Matrix to OtherInfo. People will be adding entries to Matrix, and I want the Name from the Matrix to go into the OtherInfo table.

Basically, the Name field should always replicate what is in Matrix. If something gets changed or deleted on Matrix, the same thing should happen on OtherInfo.

How do I set this up?


texts: Maximum granularity of user permissions (certain rows within table?)

texts:
Our current system architecture is fairly simple. There's a global list of devices, which move between gateways. The idea is such that the global list of devices is stored in one table, such that any gateway can read the information about any device, but can only write to the devices resident on their specific gateway.

At the moment, this must be enforced in client logic, which is obviously not quite what I'd want, for a few important (and less important) reasons. I'd much prefer it enforced at the server level.

Does there exist a common database which makes this sort of permissions granularity definable?


texts:
We have 2 database environments a "Production" Database and a "Reporting" Database located on different physical windows server 2003 boxes. The reporting Db is derived off the Production database and contains some modified tables schema's and additional "Pre Cooked" Data tables which would be too costly to otherwise process on the fly.

What I had originally created was a database script (MS SQL) on our Reporting Server to grab the Live Production database at 1AM and restore it in our Reporting environment by running all the scripts to get it up and going such as the tables restores, and modification of the data. The problem with this approach is that I quickly began to realize a) how long this approach takes (there is no option of putting a 'modifieddate' on any production records) and b) how a junior DB developer (me) should probably not dive into this stuff yet because he's more of a programmer :).

Would it be part of the 'best practice methodology' to create a windows service which basically runs the restore at 1AM and sends the email if the process failed? I'm not sure if writing a c# component is going to allow me the level of granularity that MS SQL Scripts allow. So by using a programming language to accomplish this task - is this nuts and should this all be done in the database?

How would you recommend I run the syncing of the two environments?

Let me know what you think!


texts: Is there any way to reduce/shrink tempdb.mdf size without restarting SQL Server

texts:
Is it necessary to restart SQL Server after DBCC SHRINKFILE on tempdb.mdf or is there any other method to reduce the size of tempdb.mdf without restarting SQL Server?

Kindly help as I need this for a production server and I'm hoping to do the shrinking without any down time.


texts:
When ever I try to create a database with SQI Tools for firebird I receive this error...

SQL> create database 'C:\Remake'
CON> user 'sysdba' password 'masterkey';
Statement failed, SQLSTATE = 08001
I/O error during "CreateFile (create)" operation for file "C:\REMAKE"
-Error while trying to create file
-Access is denied.
SQL>

I went to the C:\Program Files\Firebird\Firebird_3_0 directory in CMD by using the cd command and then typed isql. I have also tried this when running cmd as the Administrator. I have googled this issue many times and all I get back is responses for Linux to do with the terminal, can anyone help me with Windows?

I am a first time firebird user, can anyone help me with this?


texts:
I've created a trigram index:

CREATE DATABASE people;

\connect people;
CREATE TABLE people
(
    id        bigserial NOT NULL,
    name      text      NULL
);

CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION pg_trgm;

CREATE INDEX name_trigram_idx ON people USING gin (name gin_trgm_ops);

But it's not used:

psql -h localhost -p 5439 -U postgres -d people -c 'EXPLAIN (ANALYZE true, FORMAT TEXT) SELECT * FROM people WHERE name ILIKE '\''%juxtapositions%'\'''
                          Gather  (cost=1000.00..270871.24 rows=1695 width=87) (actual time=15727.803..15730.149 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on people  (cost=0.00..269701.74 rows=706 width=87) (actual time=10508.202..15714.003 rows=0 loops=3)
         Filter: (name ~~* '%wisest juxtapositions%'::text)
         Rows Removed by Filter: 5650000
 Planning Time: 17.167 ms
 Execution Time: 15730.344 ms

What could be a reason for it?

Update

postgres version:

psql -h localhost -p 5439 -U postgres -d people -c 'SELECT version()'
                                                             version
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.4 (Debian 11.4-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

Query with set enable_seqscan=off and ANALYZE, BUFFERS:

psql -h localhost -p 5439 -U postgres -d people -c 'set enable_seqscan=off; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM people WHERE name ILIKE '\''%wisest juxtapositions%'\'''
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on people  (cost=1666.59..4820.04 rows=850 width=55) (actual time=49.955..49.961 rows=0 loops=1)
   Recheck Cond: (name ~~* '%wisest juxtapositions%'::text)
   Buffers: shared hit=3693
   ->  Bitmap Index Scan on name_trigram_idx  (cost=0.00..1666.38 rows=850 width=0) (actual time=49.937..49.944 rows=0 loops=1)
         Index Cond: (name ~~* '%wisest juxtapositions%'::text)
         Buffers: shared hit=3693
 Planning Time: 1.083 ms
 Execution Time: 50.160 ms
(8 rows)

Update 2

pg_trgm version is 1.4:

psql -h localhost -p 5439 -U postgres -d people -c '\dx'
                                       List of installed extensions
     Name      | Version |   Schema   |                            Description
---------------+---------+------------+-------------------------------------------------------------------
 fuzzystrmatch | 1.1     | public     | determine similarities and distance between strings
 pg_trgm       | 1.4     | public     | text similarity measurement and index searching based on trigrams
 plpgsql       | 1.0     | pg_catalog | PL/pgSQL procedural language


texts: AlwaysOn Availability Groups: connecting correctly?

texts:
This question is about the correct way of accessing an Availability Group Listener:

Assume I have two AlwaysOn Availability Groups with the following AG's: AG1 and AG2.
I also have 2 listeners called LISTENER1 (on AG1) and LISTENER2 (on AG2).
I have 2 SQL nodes NODE1 and NODE2 and both have 2 SQL instances running: INSTANCE1 and INSTANCE2.

How do I correctly connect to the primary replica of AG1 via the listener?
I know it's not supposed to be NODE1\INSTANCE, but which of the following is correct?

LISTENER1

or

LISTENER1\INSTANCE1

I noticed both work fine and I assume the latter connects to the primary replica of LISTENER1 and than lets SQL Browser do its magic and connects to INSTANCE1 (which - by luck - happens to be correct in this particular setup).

I noticed if I use LISTENER1\INSTANCE2, this works as well, BUT connects to the INSTANCE2 instance of the node which hosts the primary replica of AG1!

The rabbit hole goes even futher: if I install a default instance on both SQL nodes I get yet another behavior: Connecting to LISTENER1, connects me to the default instance.

Is my logic correct (SQL Browser service) or am I just completely wrong here?

FYI: Named instances in this case use dynamic ports and all listeners are hosted on port 1433


texts: Add missing rows to table via checking relations between other tables

texts:
I have a set of tables: tender, product, product_fix, products_m_products (parent child relationship), product_m_tender. product_m_tender is missing some rows. These rows can be constructed through the use of the other tables.

Basically with tender.id i can find all products that belong to the tender via product_m_tender.tender_id. All the product id's are given by product_m_tender.product_id and product_m_tender.product_fix_id.  Parent child relation is defined only for product_fix.

Is it possible to construct a insert query that inserts all the product_m_tender relations if they are not already in the table.

Something like this:

insert into product_m_tender(tender_id, product_fix_id)
values(tender_id, product_fix_id if not present in product_m_tender and (parent in product_m_tender or child in product_m_tender))

EDIT

tender

CREATE TABLE public.tender
(
    id bigint NOT NULL DEFAULT nextval('tender_id_seq'::regclass),
    CONSTRAINT tender_pk PRIMARY KEY (id)
)

id
__
1

product_fix

 CREATE TABLE public.product_fix
    (
        id bigint NOT NULL DEFAULT nextval('tender_id_seq'::regclass),
        CONSTRAINT tender_pk PRIMARY KEY (id)
    )

id
_
1
2
3

products_m_products

CREATE TABLE public.products_m_products
(
    parent_product_id bigint NOT NULL,
    child_product_id bigint NOT NULL,
    CONSTRAINT "pmp:pk" PRIMARY KEY (parent_product_id, child_product_id)
)

parent_product_id | child_product_id
1                 | 3
2                 | 3

product_m_Tenderer

CREATE TABLE public.product_m_tender
(
    id bigint NOT NULL DEFAULT nextval('product_m_tender_id_seq'::regclass),
    tender_id bigint NOT NULL,
    product_fix_id bigint,
    CONSTRAINT product_m_tender_pk PRIMARY KEY (id)
)

id | tender_id | product_fix_id
1  |   1       |       1
2  |   1       |       3

Expected result:

product_m_tender

    id | tender_id | product_fix_id
    1  |   1       |       1
    2  |   1       |       3
    3  |   1       |       2


texts: database mirror problem with Network Service account

texts:
I have two SQL Server 2008 R2 instances installed on same Server and the engine log on as Network Service account which is set by installation.

there is a database TestDB on both instances. I try to setup database mirror for this case and I failed with following error:

  The server network address "TCP://myserver.mycompany.com:5023" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

Then I changed the account to Local System for engine and restarted sql server service and I am able to setup mirror.

How to resolve this problem if I want to keep Network Service to run the engine service?


texts: Transaction log rollback broke Log Shipping chain?

texts:
Is it correct that performing a transaction log restore to a previous point in time will break the log shipping chain?

If this is correct, is there a way to recover from this situation without having to restore a full backup and re-initialise log shipping on the 2nd server?


texts: Help with query: Get MAX value of rows that previous row has certain value


texts: How to correctly use PFILE keyword in DDS Logical File? (AS400)

texts:
I'm having trouble wording this question right (also not sure if I'm in the right stack section)

My issue is that I have a logical file ORDFILE located in the ORDRENTLB library, that is suppose have access to the physical file data of CIFFILE located in the CUSTINFOLB library.

When compiling, all I get for the program is the error "ended abnormally".

However, when I remove PFILE(CUSTINFOLB/CIFFILE), and change the type to PF, it compiles perfectly.

The CIFFILE has compiled successfully as its only a PF.

what I have tried:
Moving ORDFILE to the same library as CIFFILE (abnormal ending error)
Tried stating CUSTOMER as a foreign key by placing an F next to CUSTOMER in ORDFILE (got an error)
Tried placing single quotes around 'CUSTINFOLB/CIFFILE', (error)

what am I doing wrong?

thanks for your time.

EDIT

Scope: To create a small distribution system on the iSeries platform.
Create a series of libraries that includes code for the following:

Customer Information File (CIF) Functionality
Order Entry Functionality
Purchasing Functionality
Inventory Functionality

My requirements:

Create 4 DDS Specifications for each of the 4 file groups based on
the information specified.
Create 4 DFU programs for each of the 4 files you set up with DDS
specifications.
Create 4 CL programs to run the DFU programs you have created.

Include some comments as well as a message that will appear on the screen once the menu option has been chosen.  For example, if you chose Option # 1 to Enter CIF data, a message should initially appear such as -
                     'CIF DATA ENTRY PROGRAM IS NOW EXECUTING'

Create 3 Menus for each of the 3 departments(Order Entry/Inventory
/Purchasing) with appropriate titles on each menu.
Create a main menu called MAINMENU to access the 3 department menus.
Create a Query/400 report based on the CIF file and add it to the
main menu as an option.

Ensure that you have followed the 3 requirements as follows:

Create the Query as a report by choosing the  'Print' option.  Format the CIF # to eliminate the ‘,’ from the number on the report. Place a Page heading on the report.

The Main menu should include the following menu options:

Go to the 3 various ‘department’ menus Run the CIF Query program Access the Spool file Sign off

Set up the CLP programs on the menus you have created.

The CIF Master File must only exist in one place, but all departments must be able to access the same file.

  The Database:

  Customer Information File =>

  Customer Number (5 digit numeric field) – KEY

  Company Name

  Contact Name

  City

  Phone number

  Total Customer Order amount ($)

  Order Entry File =>

  Order Entry Number (6 digit numeric field) – KEY

  Customer Number

  Item Number Ordered (10 char.)

  Date Ordered Quantity Ordered

  Ordered By (Person's Name)

  Total amount of Order ($)

  Inventory File =>

  Part Number (6 digit numeric field) – KEY

  Part description

  Quantity on hand

  Unit Cost

  Out of Stock Indicator (1 byte Alpha)

  Purchase File =>

  Purchase Order Number (4 digit numeric field) – KEY

  Part number

  Quantity Purchased

  Quantity Back-ordered

  Purchase Date

  Total Purchase Order Amount ($)


texts:
We currently have tempdb (8 files) on solid state drive.

We know that primary mdf and transaction log ndf are recommended to be on different drives, to reduce contention. Should tempdb files be on different drives to have consistent principle, or can they all be on the same drive?

The following article stated they are all on same drive, however looking for a background technical explanation.
Recommendations for more than one tempdb file

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'G:\tempdb2.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'G:\tempdb3.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
....


texts: Can I set the maximum locking level that a transaction can acquire?

texts:
What I want to achieve is something like this:
SET LOCAL max_locking_level="ACCESS SHARE";
ALTER TABLE foo ALTER COLUMN bar SET NOT NULL; -- Error, since it needs an ACCESSS EXCLUSIVE lock

But I haven't been able to find the right setting in the documentation yet.
For some context: we're using flywheel for scripted database migrations, and part of the review process is to make sure that migrations can be applied without downtime. One of the ways we're achieving this is by enforcing (configurable) statement/lock timeouts on all migrations. This is working well, but it still leaves room for error: some migrations need a long statement/lock timeout, which can be acceptable if it doesn't use exclusive locks. It has happened that developers underestimated the lock level an operation needed, and as a result created migrations that hold long ACCESS EXCLUSIVE locks, which will cause downtime when deployed. The only thing that prevents this is a good eye from code reviewers, which isn't reliable enough.
I'm hoping there is a way that we can enforce a max lock level, as this will make it easier to ensure prevent long exclusive locks (i.e. only allow long migrations if the max lock level is low)


texts: Copy un-replicated Bin logs from Master to Slave , if Master goes down MySQL 5.7 Enterprise

texts:
I have async-Classic file based replication with 2 Nodes as Active - Active.
R/W is happening on Primary Node (PN) only and Secondary Node (SN) has been setup as HA only. SN has been configured as read_only mode.

In case if PN Database goes down when I/O thread (of SN) trying to fetch bin-logs resulting which not all events got replicated to SN. Now , data comes in unsync state .

As per my another script i will re-direct all R/W operations to SN by disabling its read_only mode post PN goes down.

Now question is :

 Q. If i copy those binary logs from PN at point where replication got break and get it executed at SN end. how much it is feasible ?

       Things i Know :  For this , can i use execute mysqldump utility from
                        SN with its *read-binary-logs-from-server*
                        parameter. This will copy the logs from PN to SN and
                        then i can execute it . But my doubt is , as PN DB
                        is down ( but VM box is running ), can i still be
                        able to fetch bin-logs ?

Please give your feedback on this . How much this solution will work . I cannot use any other 3rd party tool or other modes of replication due to time constraints and cost constraints

                            *****EDIT*****

If DB at PN is down , i cannot make use of mysqldump .

What i have figured out is , we can take the position at which replication went down and i will fetch those records from binary logs and get it executed at SN. In this way un-replicated records get inserted at SN


texts: Skipping stopwords (and indexing small words) for one fulltext index only, in MySQL

texts:
I have a rather odd situation where it might be preferable to not use stopwords and to have a value of ft_min_word_len equal to 1. The index in question would be dealing with only a few words per row as an average and it is important to get scores back that can distinguish between rows with data such as "who" and "who is" and return as a result against a search string such as "Who is this Matt bloke?" (The table is a big list of sentence stubs for an experimental chatbot idea).

The default settings (which are good for most everything else) would block such tiny stubs from the index. However, if this is the global DB setting then it would make fulltext indexes very slow and noisy for most other uses.

Is there a way to have fulltext indexes and use match on that table only that have an empty stopwords list and allow tiny words? Or is there another way of finding partial matches on large lists of sentences stubs to match full sentences against?


texts: Reserving dates with quantities, table schema wrong?

texts:
Okay I have a table like so.

ID   Productname  Quantity          Image       QuantityOut  LastDateReserved

1    productOne          5     'image.jpg'                 3       2013-03-15
2    productTwo          1     'product.jpg'               2       2013-03-02

I have a page that checks for the last part of an event date + 5 days buffer. Now here is where I get really confused. Say for instance I want to check out only 3 of productOne, how would I make that work with this table schema? Do I need to add more fields or tables? The way I have it now is that my LastDateReserved would be overwritten with a new date. Thus erasing the old date and not being accurate on when the earliest date actually is. What would be the proper table structure here?

If my question needs to be reworded, please let me know and I will change it.


texts:
I have two servers/instances, both MSSQL 2012. I have a linked server set up on SERVER1 "using the login's current security context" (@useself='TRUE') and RPC set to TRUE. SERVER1 is using domain account MYDOMAIN\SQL$ACCT for its service account.

Here is what happens when I run the following at a command prompt:

C:\> sqlcmd -S Server1 -E -d MYDB -Q "SELECT * FROM SERVER2.MyRemoteDB.dbo.Contacts;"
Msg 18456, Level 14, State 1, Server SERVER2, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Here is my setup for SPN on SERVER1:

C:\> setspn -L MYDOMAIN\SQL$ACCT
Registered ServicePrincipalNames for CN=SQL$ACCT,OU=Users,OU=SQL Admin,OU=Administrative Control,DC=YY,DC=ZZZ:
    MSSQLSvc/Server1.yy.zzz:1433
    MSSQLSvc/Server1.yy.zzz

C:\> setspn -L Server1
Registered ServicePrincipalNames for CN=SERVER1,OU=Production,OU=Servers,DC=YY,DC=ZZZ:
    CmRcService/SERVER1.yy.zzz
    CmRcService/SERVER1
    WSMAN/SERVER1
    WSMAN/SERVER1.yy.zzz
    TERMSRV/SERVER1.YY.ZZZ
    TERMSRV/SERVER1
    RestrictedKrbHost/SERVER1
    HOST/SERVER1
    RestrictedKrbHost/SERVER1.YY.ZZZ
    HOST/SERVER1.YY.ZZZ

Domain account MYDOMAIN\SQL$ACCT is set up for Trust this user for delegation to any service (Kerberos only). SERVER1 is set up for Trust this user for delegation to any service (Kerberos only).

I am sysadmin in both instances, so no permission issues within SQL Server.

what am I missing?


texts: I want to create a database and share the username and password with a friend who wants to access it remotely over the internet. I have mysql workbench installed but don't really know how to share a db remotely. Step by step instructions would be helpful.


texts: Should I be concerned about excessive Com_change_db commands?

texts:
I installed MONyog recently to check for problems on a MySQL server that I admin. Most of the information was useful and helpful, but there's one warning I am not sure is worth fixing.

It reports that Com_change_db commands are excessive (currently 9+ million or 58 per second), or unusually high. The connections to the database are strictly Windows .NET applications and they use Connector/Net as an API.

I typically connect such that I specify which database to use when creating a MySqlConnection object. I discovered in a test that doing this causes the Com_change_db to increment by one when the connection is established.

I can "fix" it by not specifying a database, and instead qualifying the database within the query every time. (e.g. db.table.col instead of table.col)

Is it worth going through the entire code base of the application to make this change? Is MONyog concerned with something that doesn't matter?


texts:
I need to be able to search for SQL objects (logins, etc). With a smaller number of servers, I would connect to them all via a registration group in SSMS and do the search.

Does anyone have a better way with a large number of instances?

Thanks


texts: The definition of object '[sp_name]' has changed since it was compiled on fail-over to mirror database

texts:
We have two SQL 2012 database servers configured in a mirror with a witness for automatic fail-over.

Yesterday the primary server suffered a HDD degradation which triggered the fail-over, however when the secondary server became the principle we started to see many errors on execution of some SPROCs.

  The definition of object '[sp_name]' has changed since it was compiled

I understand that this can be resolved by running sp_recompile on the new principle, which would force a recompile on all SPROCs, functions and triggers.

This is not ideal as it requires manual intervention on fail-over. Is this a problem I can resolve through amending the configuration, or is it a known SQL Server bug?


texts: is there a documented limit to the number of slaves you can have in redis?

texts:
I'm trying to find any documentation about the maximum number of slaves you can set up in a redis  master / slave configuration.  We can potentially have 200 slaves.
If there is a limit, I might switch gears and consider messaging solution with REDIS instead of replication.

So far I haven't been able to find anything.

Any suggestions?


texts:
We have sql server 2014 alwayson availability group set up for a database. Synchronous-commit availability mode is used. We ran a few PAL (Performance Analyzer Log) reports to obtain performance data on the primary replica and they showed IO alerts where read and writes were >25ms. The IO alerts occurred for both logical and physical IOs and on drives C:, F: (that has db files) and L: (that has transaction logs). I ran this statement:

SELECT
    wait_type, waiting_tasks_count, wait_time_ms,
    wait_time_ms/waiting_tasks_count as 'time_per_wait'
FROM
    sys.dm_os_wait_stats
WHERE
    waiting_tasks_count > 0
    and wait_type = 'HADR_SYNC_COMMIT';

and got 12ms for the time_per_wait value. This means the latency between the primary and secondary replicas were 12 ms.

My question: does the IO response time reported by PAL include this 12ms latency between the primary and the secondary replicas?

Thanks


texts: Duplicate key on delete and update of unique key

texts:
I have a table with 10^7 records. I do the following:

Delete a record using a unique key to identify it
Update another record's unique key to the one I have just deleted

MySQL 5.7 complains about duplicate keys. Schematically, the sequence of commands looks like the following:

create database foo;
use foo;

create table users(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `login` (`login`),
  KEY `email_idx` (`email`)
);

insert into users values(1, 'foo', 'foo@gmail.com');
insert into users values(2, 'bar', 'bar@hotmail.com');

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
delete from users where login = 'foo';
update users set login = 'foo' where email = 'bar@hotmail.com';
COMMIT;

The code above does not fail, but the actual code does fail, even when just one transaction runs.

Why could this be happening?


texts: How to add/update data from slave server to master server in MariaDB?

texts: I am experimenting on whether I can tweak the functionality of master-slave replication. I can write data in my master server which gets updated in the slave server but it cannot happen the other way. I want to find a way where I can write and update data via both the servers. Is it possible to do it ? If yes, what is the way to implement it ?


texts: I can't create relationship in my database sql server

texts:
I have designed a  database for my website. It has 3 tables: user, topic and comment.

A user has many topics (or articles)
A topic (or article) has many comments
A user has many comments

I created the relationships for them  after I set the insert and and update specification as cascade.

I want that

if I delete a user then the topics of this user and the user's comments will be deleted
If I delete a topic then all comments of this topic will be delete by cascade

My diagram:


texts:
I'm making a database which should be able to handle over 1,000,000,000 rows.

in order to optimize it's performance i've decided to split tables.

so instead of having 1,000,000,000 entries in one table for over 10 years i would split it to 3650 tables and one table for each day.

there would be no more than join or union of 2 queries now i want to know wether having too many tables degrade performance or not and if so How much ?


texts: group by and display contents of last row for each result

texts:
I've got some data of two objects and I want to retrieve the last row of both the objects, preferably in one query.

Table structure:

CREATE TABLE data
(
  pid serial NOT NULL,
  dt timestamp without time zone,
  object_id integer,
  info_1 numeric(10,8),
  speed numeric,
  CONSTRAINT dat_pid PRIMARY KEY (pid)
)

Sample data:

1, 2014-04-29 12:02:56, 8, ....
2, 2014-04-29 12:02:10, 8, ....
3, 2014-04-29 12:01:02, 8, ....
5, 2014-04-29 12:01:32, 6, ....
.....

I want a row per unique object ID, each row being the newest one available. I tried to achieve it by using this query, but it wants pid to be in the group by clause. But I don't want to group by the PID, but at the tracker id....

SELECT *
FROM data
GROUP BY object_id


texts: I am a SQL Server dba. I have been asked to compare our production environment to test from a number of different perspectives. These are windows patch level,sql service pack, to database size and compabilty modes. I also need to identify global environmental differences such as max dop settings to memory allocation. Can anyone advise of useful tsql or powershell where this can be captured as a report style format.


texts:
I want to get all data of tree by ID.

My table is:

ID     NAME     PARENT_ID
------------------------
1      A        NULL
2      B        1
3      C        1
4      D        3
5      E        4
6      F        NULL
7      G        6
8      H        NULL

I want the output result based on the following:

Input(ID)       Output(IDs)
-----------------------
1               2,3,4,5
2               1,3,4,5
3               1,2,4,5
4               1,2,3,5
5               1,2,3,4
6               7
7               6
8               NULL

How can i do that?


texts: Is there an option to turn existing global variant to constant in MySQL or MariaDB?

texts:
I'm trying to prevent changing some global variables which I set from the ini file.

For example:

server_audit_logging=ON

I want to prevent it from changing by:

SET GLOBAL server_audit_logging = OFF;

Is there a way to achieve it?


texts:
in my mariadb i have replication,
i tried to change the location of the binlogs by changing the configuration file as so:
log-bin = /new/path/mysql-bin
I also tried to change the index location as so:
log_bin_index = /new/path/mysql-bin.index
but every time when I restart I see that the location of the binlog files is /var/lib/mysql...
i tried to check with mysqld --verbos --help | grep bin
and I saw that the lob-bin parameter is mysql-bin and not what I wrote in the cnf...
how to I make the MySQL to take the value of the log-bin from the cnf file?
this is the cnf file of the MySQL for sure...


texts:
I'm trying to set up a user with limited permissions that would be able to create foreign tables. I have two databases, hr_db and accounting_db. I have created an hr_user user for hr_db and a accounting_user user for accounting_db. I only want the accounting_user user to have select rights on some hr_db tables, such as the users table. To do this, as a superuser I went to the hr_db database and ran:

GRANT CONNECT ON DATABASE hr_db TO accounting_user;
GRANT SELECT ON people TO accounting_user;

I set up a connection to hr_db from accounting_db using a foreign data wrapper:

CREATE SERVER hr_db FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'hr_db', port '5432');

I then added a mapping for the accounting_user user:

CREATE USER MAPPING FOR accounting_user SERVER hr_db
OPTIONS (user 'accounting_user', password 'secretpassword');

The password for accounting_user is the same as the one I use to log in from the command line. This works fine:

psql -U accounting_user -W hr_db
[enter accounting_user password]
SELECT * FROM people LIMIT 10;

I can create a regular table in the accounting_db database as the accounting_user user:

psql -U accounting_user -W accounting_db
[enter accounting_user password]
CREATE TABLE test (person_id integer NOT NULL);
DROP TABLE test;

But if I try to create a foreign table:

CREATE FOREIGN TABLE hr_people (person_id integer NOT NULL)
SERVER hr_db OPTIONS (table_name 'people');
ERROR:  permission denied for foreign server hr_db

As a superuser, I can create the hr_people foreign table and the accounting_user will have access to it. So the foreign data connection to hr_db seems correct. What else do I need to give the accounting_user in order for it to be able to create and drop foreign tables?


texts: MySQL not returning successful results on statement - query looks like it's hanging but is successful

texts:
So we have a script(s) that runs in succession.
Randomly this task would halt/freeze and error out simply because it did not see a returned response from the MySQL server saying the statement ran fine breaking the entire procedure.
However when manually checking against the DB, the statements all ran fine it's just the script (using JBDC) aborted since it didn't see a response.

Nothing in the error logs, etc.

Is there a setting or reason why MySQL would be doing this?
This is version 5.1 .

I thought of network issues causing this but this seems to happen in 2 different environments, both through a WAN and local LAN .

any ideas?


texts: Performance: order of tables in joined statement

texts:
I have the following SQL statement, running on a SQLite database on a Windows mobile device.

SELECT
    table1.uniqueidentifier1, table1.int1, table1.varchar1,
    table1.decimal1, table1.decimal2
FROM table1
INNER JOIN table2 On table1.PK = table2.FK
WHERE table2.uniqueidentifier2 IN (uniqueidentifier1,uniqueidentifier2,....)
ORDER BY table1.varchar1

As there are several hundred thousand records in each table and the device isn't really new this takes some time.

Would the performance be better, if I switched the tables, somehow like this:

SELECT
    table1.uniqueidentifier1, table1.int1, table1.varchar1,
    table1.decimal1, table1.decimal2
FROM table2
INNER JOIN table1 On table1.PK = table2.FK
WHERE table2.uniqueidentifier2 IN (uniqueidentifier1,uniqueidentifier2,....)
ORDER BY table1.varchar1

Please note: in the first statement I select from table 1` and join table 2, in the second, it is switched.

Why or why not is it faster?


texts: Query to compute unspecified values in "Other" category

texts:
The problem that we are trying to solve is explained herein using a simple (and deliberately sub-normalized) table.
The table is defined as follows:
create table expense_profile
(
  id  INT UNSIGNED AUTO_INCREMENT,
  name  VARCHAR(10),
  category  VARCHAR(16),
  percentage DECIMAL(15,2),

  PRIMARY KEY(id)
);

The table is populated as follows:
INSERT INTO expense_profile(name, category, percentage)
VALUES('Alice', 'Housing', 15), ('Alice', 'Transportation', 5),
('Alice', 'Food', 25), ('Alice', 'Utilities', 5),
('Alice', 'Healthcare', 5), ('Alice', 'Personal', 3),
('Bob', 'Transportation', 7), ('Bob', 'Food', 18),
('Bob', 'Healthcare', 8), ('Bob', 'Personal', 20);

In this table we have accounted for 58% of Alice's expenses and 53% of Bob's expenses.  Implied therein is that 42% of Alice's expenses and 47% of Bob's expenses are in the "Other" category.

Q) Is there a query that would allow us to list, for the specified user, all the categories, including the "Other" category?

For example, we would like to have the following output for Bob:
+------+----------------+------------+
| name | category       | percentage |
+------+----------------+------------+
| Bob  | Transportation |       7.00 |
| Bob  | Food           |      18.00 |
| Bob  | Healthcare     |       8.00 |
| Bob  | Personal       |      20.00 |
| Bob  | Other          |      47.00 |
+------+----------------+------------+