texts:
I have an older application that uses TopSpeed as the database engine. I want to use some of the data contained in these tables in another application that uses SQL. To accomplish this, I purchased the TPS ODBC driver and used Access to move the data from the TPS tables to an SQL database by using the linked tables feature.

This works fine, but I'm looking for an automated solution (plus, the Access way is messy). Is there a tool out there that could help?


texts: Install old Cumulative Update SQL Server 2016 Standard

texts:
We were running on SQL Server 2016 Standard Edition 2016 SP2-CU4 on our ALWAYS-ON HA cluster.
Due to some recent issues one of our nodes got corrupted and we had to replace it with a new node. Since this was a fresh image from AWS Marketplace it came with SP2-CU13 (latest) out of the box.
The client wants this to be rolled-back to the original SP2-CU4 (no plans/requirement of upgrading to CU13).
As a primary step I tried to install the SP2-CU4 KB article on the instance but that does not work with the attached error message. (No SQL Server instance or feature can be updated on this computer)

Q1 - How can SP2-CU4 installed on the server.
Q2 - What is the impact if the cluster runs on different CU's.


texts: Sync database table schema and stored procedures

texts:
We have 2 databases. One is at our server, the other is local. We need to sync them both in a manner that local database has all data and server database has no data only empty tables and stored procedures and functions.

In server database we are changing stored procedures, functions, as well as tables. These changes are to be synchronized into Local DB i.e.

Common at both databases:

table 1 (id, name, price)

Changes happened in Server DB:

table 1 (id, name, price, IsActive)

We want IsActive field to be synchronized into local db, same way we need to sync stored procedures and functions.

So in simple words we need a solution to sync stored procedures, functions, and table schema without messing up existing data.

Can any one suggest any tool or code with which we can satisfy the same? With some google and search we found a codeproject article but that can be used for fresh database. Still sharing the link.

Thanks


texts: Just one Active Directory group will not sync to SQL Server

texts:
Modifying the Active Directory group membership for a particular user propagates to our SQL Server instances as expected — except when one specific group is added.  What is causing this issue, and how can I fix it?

The servers in question are SQL Server 2016 SP1 running on Windows Server 2012 R2.

Given that the user is me, I can query my groups on one of the servers:

DROP TABLE IF EXISTS #Groups
CREATE TABLE #Groups
(
    [Name] NVARCHAR(30) NOT NULL PRIMARY KEY
)

INSERT #Groups ([Name]) VALUES
(N'ORG\DBDevelopers'),
(N'ORG\DBFullAccess'),
(N'ORG\DBReadOnly')

SELECT g.[Name], IS_MEMBER(g.[Name])
FROM   #Groups g

Results:

Name
------------------------------ -----------
ORG\DBDevelopers               1
ORG\DBFullAccess               0
ORG\DBReadOnly                 1

If my login is removed from or re-added to ORG\DBReadOnly, the change is propagated to the SQL Server instances as expected, modifying permissions accordingly.  However, adding my login to ORG\DBFullAccess does not appear to modify permissions, nor does it change the results of the query above.  However, the query:

EXEC xp_logininfo N'ORG\DBFullAccess', 'members'

says that I am a member of the group:

account name       type     privilege mapped login name  permission path
------------------ -------- --------- ------------------ ----------------
ORG\my.login       user     user      ORG\my.login       ORG\DBFullAccess
ORG\another.login  user     user      ORG\another.login  ORG\DBFullAccess
ORG\a.third.login  user     user      ORG\a.third.login  ORG\DBFullAccess

Logging onto the Windows server itself via RDP also indicates that I am a member of ORG\DBFullAccess.  From a shell:

PS C:\Users\my.login> gpresult /R

<Banner, various likely-irrelevant information, etc.>

USER SETTINGS
--------------
    ⋮
    The user is a part of the following security groups
    ---------------------------------------------------
        ⋮
        DBFullAccess
        DBDevelopers
        DBReadOnly
        ⋮

This behavior is occurring with multiple SQL Server instances each running on their own Windows Server instances.  Things we've tried include:

Removing my login from DBFullAccess and re-adding it, forcing AD propagation after each step.
Simply waiting overnight, with my machine powered off.
Going directly onto one of the servers via RDP and using sqlcmd to query there.  (I work remotely, and I connect to these servers by VPN, so my own PC is not itself on the AD network.  I use SSMS via a runas /netonly command, which permits me to access the servers.)

Keep in mind, again, that adding and removing DBReadOnly works, and I can see the results when I disconnect from and reconnect to the SQL Server instance.

Our contract system administrator has recommended simply rebooting the Windows servers over the weekend.  For a number of reasons, that action is suboptimal, plus we don't know for sure it will help.

How can I continue to troubleshoot the issue?  Or, how can it be fixed?

Edit: We have a test server on AWS EC2 that is running SQL Server 2017 on Windows Server 2012 R2.  The servers mentioned earlier are bare-metal servers in a colo facility.  I do have the DBFullAccess group on the SQL 2017 instance.


texts: what is the impact of changing log shipping schedule timing jobs?

texts:
I have two severs primary and secondary log shipped server , after configuring it, I noticed that the latency of last copied file and last restored file increased and the alert of monitor sever fired a lot of times as I see it in SQL server error logs so I  modified the alert job at primary server  to fire every 15 minutes instead of 2 minutes but now I decided to reschedule the time of copy jobs to fire every 15 minutes instead of 10 minutes to decrease the the time of latency for both of the copy and restored jobs.

My question: is there any threats that may affect the log shipping process or is it normal to change the job schedule even for the alert, copy, restore and backup jobs?


texts: delete old 'Monitoring Agent' metrics and 'Automation Agent' Debug logs via scheduler

texts:
We use Ops Manager 1.6.2.251

# mongo
MongoDB shell version: 2.6.10
connecting to: test
> show dbs;
admin                   (empty)
alerts                  0.203GB
apiv3                   0.578GB
backupagent             0.078GB
backupalerts            0.078GB
backupbilling           0.078GB
backupconfig            0.078GB
backupjobs              0.078GB
backuplogs              2.077GB
backupoplogs            0.203GB
backupsnapshot          0.078GB
cloudconf               0.203GB
local                   0.078GB
mmsdb                   0.078GB
mmsdbagenterrors        0.328GB
mmsdbagentlog           1.078GB
mmsdbanalytics          0.078GB
mmsdbautomation         0.578GB
mmsdbautomationlog      1.578GB
mmsdbbilling            0.078GB
mmsdbconfig             1.078GB
mmsdbdays               0.078GB
mmsdbhours              0.203GB
mmsdbjobs               0.078GB
mmsdbminutes-even       0.203GB
mmsdbminutes-odd        0.203GB
mmsdbpings              0.578GB
mmsdbprofilerstats      0.078GB
mmsdbprovisioning       0.078GB
mmsdbprovisionlog       0.203GB
mmsdbqueues             0.078GB
mmsdbratelimit          0.078GB
mmsdbrrdcache           0.078GB
mmsdbrrdsampling        0.078GB
mmsdbserverlog          0.203GB
mongo-distributed-lock  0.078GB

Our MMS DB use 15 GB of 150 GB available. We can't extend the volume.
We don't use MMS for backup. We use mongodump on other node.

  Ops Manager Application Database This database contains application
  metadata used by the Ops Manager Application. The database stores: •
  Monitoring data collected from Monitoring Agents. • Metadata for Ops
  Manager users, groups, hosts, monitoring data, and backup state. For
  topology and specifications, see Ops Manager Application Database
  Hardware.

At the moment we only enabled Monitoring Agent on Prod. But we wish to enable it on all environments, but cleanup metrics data older than 30 days.

In Oracle you can do something like this. This statement can be scheduled jobs with Oracle Scheduler.

 DELETE
  FROM YOUR_TABLE
 WHERE creation_date <= TRUNC(SYSDATE) - 30

The same we wish to to with Automation agent DEBUG logs. Howto do this with MongoDB MMS meta databases?

From FAQ

  What is the data retention policy for Ops Manager? Ops Manager retains
  two distinct types of data: metrics, which describe usage; and
  snapshots, which back up your data.

  Data-retention policies, as defined in the Terms of Service, are
  always subject to change.

  As of this writing, Ops Manager preserves:

  Minute-level metrics for 48 hours. Hourly metrics for 94 days.
  Snapshots according to their retention policy.


texts:
I am trying to add a calculated member to an MDX query which will create a set of "Age Bands" for people in my database. I have a measure which contains the age of each person.

I have started with this MDX

SELECT
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY {
    (
        [People].[Gender].[Gender],
        [People].[Some].[Other Attributes]
    )
  } ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

I have another measure ([Measures].[Age]) which I would like to band up and return with the rows, so eg anyone aged 0-25, 26-50 etc

Here is what Ive tried

WITH SET [Age Band]
    AS IIF([Measures].[Age]<26,"0-26",IIF([Measures].[Age]<51,"26-51","Over 50"))
SELECT
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY {
    (
        [People].[Gender].[Gender],
        [People].[Some].[Other Attributes],
        [Age Band]
    )
  } ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

However the error I get is

  The  function expects a tuple set expression for the  argument. A string or numeric expression was used.

What am I doing wrong? Is there an easier/better way to do this without chaining IIF commands?


texts: Insert/update data into relational database tables only after administrator approval - how to approach it?

texts:
I'm building a relational database that holds 21 tables, which is connected to my website application through PHP.

The website has search functionality which allows users to search this database. Also, users can enter new data, through a special form. Because these entries must pass rigorous control by administrators, I want to make some kind of approval system, available only to the administrators, which can see user input before it appears in the search results, and allow it, return it to the user to make changes, or delete it.

My question is what would be an approach for this situation from a relational database design standpoint, and what is the best practice?

P.S. I know my question is probably wide, but I could not found an answer by searching the internet or other posts on this website.


texts:
When trying to export and import an 58G database yields the following error:

  ERROR 1071 (42000): Specified key was too long; max key length is 1000
  bytes

Dump created using mysqldump -q --opt and import is just cat bigfile.sql|mysql ...

MySQL versions:

Import: 5.1.49-1ubuntu8.1
Export: 5.1.41-3ubuntu12.10-log (Ubuntu)


texts:
I'm trying to learn how this pivot thing works. I don't think I'm understanding it quite right.
I just want to do a simple count for a bunch of item types that have some statuses. The table I'm working from has columns: [Status], [ItemType], and a bunch of others.

Here is what I'm doing currently

SELECT [NEW]
    ,[IN]
    ,[OUT]
    ,READY = [READY FOR DISPOSAL]
    ,TRANSIT = [IN ROUTE TO DISPOSAL]
    ,[DISPOSED]
    ,[TYPE] = [ItemType]
FROM [EVAULTTEST].[dbo].[Items] AS ei

PIVOT(
COUNT([Status])
FOR [Status] IN ([NEW], [IN], [DISPOSED]
    , [IN ROUTE TO DISPOSAL], [OUT], [READY FOR DISPOSAL])
) AS piv

And the result

1   0   0   0   0   0   FIREARMS
0   1   0   0   0   0   FIREARMS
1   0   0   0   0   0   FIREARMS
0   1   0   0   0   0   FIREARMS
1   0   0   0   0   0   MONEY
1   0   0   0   0   0   Automobiles
0   1   0   0   0   0   Aircraft
1   0   0   0   0   0   Automobiles
1   0   0   0   0   0   Automobiles
1   0   0   0   0   0   ALCOHOL
0   0   1   0   0   0   MONEY
0   0   1   0   0   0   AUTOMOBILES
0   0   1   0   0   0   AUTOMOBILES

Why don't these collapse into something like this:

2   0   0   0   0   0   FIREARMS
0   2   0   0   0   0   FIREARMS
1   0   0   0   0   0   MONEY
3   0   0   0   0   0   Automobiles
0   1   0   0   0   0   Aircraft
1   0   0   0   0   0   ALCOHOL
0   0   1   0   0   0   MONEY
0   0   2   0   0   0   AUTOMOBILES


texts: Rank users by different periods and overall

texts:
I'm trying to create a ranking system for a game which should have 2 different rankings. Below you will find the Model (or the result after querying the database) which should be presented to the player:

1. Hot leaderboard (periods: Day, Week, Month)

   - #rank (user's position in the leaderboard)
   - user_fk
   - won games count
   - lost games count

2. General leaderboard (Overall data)

   - #rank (user's position in the leaderboard)
   - user_fk
   - won games count
   - lost games count

As you can see, the models are the same, but the queries are different. Below you can find the database structure for the User model and for the Score logging.

User model

id
created_at/updated_at
picture
nickname
zone_id
xp
played games

James: win/lost count
Lisa: win/lost count
Jacob: win/lost count

As you can see, the user can play against 3 different opponent players: James, Lisa and Jacob. Every time the player finishes a game with an opponent player (CPU), the win or lost counts are incremented. Based on that, a new XP (experience point) is added or subtracted from the user's XP.

Score log model

uuid
user_id
date_created
against
won (true, false)
zone_id

For the hot leaderboard I'm trying to write the following query:

Select users ordered by number of won games DESC, lost games ASC,
where date_created <= 24h AND againts = OpponentPlayer AND zone_id = ZONE LIMIT 250

And for the General leaderboard:

Select users ordered by XP DESC, won games DESC, lost games ASC
where updated_at <= 90 days and zone_ID LIMIT 999

The third thing that I don't know how to solve is to: How to get a specific user's rank based on the general leaderboard?

Is this the right approach to log user's played games?
Should I create a different table for leaderboards and update their value once in a while?


texts: bit columns generating warnings - how can a cast in the field list affect the cardinality estimate?

texts:
A few days ago I had a question about warnings in the execution plan

Type conversion in expression may affect “CardinalityEstimate” - on a computed column?

and that was solved, thanks Paul. Now I have another question regarding the cardinality estimate, this time it is caused by the bit data type.

I am using sys.data_spaces and sys.indexes

to run a simple query to get some information about my indexes:

SELECT i.name AS index_name
,[ObjectType] =
  i.type_desc +
  CAST ( CASE WHEN CAST (i.is_primary_key AS INT)  = 1
  THEN SPACE(2)  + '     Primary Key '      ELSE '' END AS NVARCHAR(30)) +

  CAST ( CASE WHEN i.is_unique_constraint = 1 THEN SPACE(2)  +
  'Unique Constraint' ELSE '' END AS NVARCHAR(30))+

  CAST ( CASE WHEN i.is_unique = 1 THEN
         CASE WHEN i.is_primary_key = 0 AND i.is_unique_constraint = 0
              THEN SPACE(2)  + 'Unique' ELSE '' END
         ELSE SPACE(2)  + 'Non-Unique' END AS NVARCHAR(30)) +

  CAST ( CASE WHEN i.has_filter = 1 THEN SPACE(2)  + 'Filtered'
         ELSE '' END AS NVARCHAR(30)) +

  CAST ( CASE WHEN i.is_padded = 1 THEN SPACE(2)  + 'Padded'
         ELSE '' END AS NVARCHAR(30))

    --,i.type_desc
    --,is_unique
    --,ds.type_desc AS filegroup_or_partition_scheme
    ,ds.name AS filegroup_or_partition_scheme_name
    ,ignore_dup_key
    --,is_primary_key
    ,is_unique_constraint
    ,fill_factor
    ,is_padded
    --,is_disabled
    ,allow_row_locks
    ,allow_page_locks
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE 1=1
  AND is_hypothetical = 0
  AND i.is_disabled = 0
  AND ds.is_system = 0

GO

the query runs fine

but somehow dealing with the bit columns is upsetting the execution plan, causing some warnings related to conversion:

  --Type conversion in expression (CONVERT(nvarchar(30),CASE WHEN
  CONVERT(bit,[i].[status]&(32),0)=(1) THEN '  Primary Key ' ELSE ''
  END,0)) may affect "CardinalityEstimate" in query plan choice,

  --Type conversion in expression (CONVERT(nvarchar(30),CASE WHEN CONVERT(bit,[i].[status]&(64),0)=(1) THEN '  Unique Constraint' ELSE
  '' END,0)) may affect "CardinalityEstimate" in query plan choice,

  --Type conversion in expression (CONVERT(nvarchar(30),CASE WHEN CONVERT(bit,[i].[status]&(131072),0)=(1) THEN '  Filtered' ELSE ''
  END,0)) may affect "CardinalityEstimate" in query plan choice,

  --Type conversion in expression (CONVERT(nvarchar(30),CASE WHEN CONVERT(bit,[i].[status]&(16),0)=(1) THEN '  Padded' ELSE '' END,0))
  may affect "CardinalityEstimate" in query plan choice

My question(s) are:

I am dealing only with the query returning columns, should I worry about this warning?

is it related to the bit data type?

is there any way to change the code and get rid of this warning?

if it is only noise, can I regulate it?


texts: Optimize very slow SQL joins on multiple tables or use different engine?

texts:
We have something similar to Google Analytics, but decided to not use something that's already available (but didn't fit our needs exactly), and instead created our own "mini-analytics".

Now, this was all easy and fun, but as it grows, the architecture either wasn't designed properly, or the wrong tools were used to solve the problem.

The problem lies with queries that look like the following: "Get all user sessions that have the following events: login, chrome browser version 58, and profile_view".

Currently this hits the following tables:

CREATE TABLE `logins` (
    `session_id` bigint(20) NOT NULL,
    `request_id` INT(11) NOT NULL,
    `timestamp` INT(11) NOT NULL,
    `login_data` mediumblob,
     KEY `session_req_idx` (`session_id`, `request_id`),
     KEY `timestamp_idx` (`timestamp`)
)

CREATE TABLE `browsers` (
    `session_id` bigint(20) NOT NULL,
    `request_id` INT(11) NOT NULL,
    `timestamp` INT(11) NOT NULL,
    `browser_data` mediumblob,
     KEY `session_req_idx` (`session_id`, `request_id`),
     KEY `timestamp_idx` (`timestamp`)
)

CREATE TABLE `profile_views` (
    `session_id` bigint(20) NOT NULL,
    `request_id` INT(11) NOT NULL,
    `timestamp` INT(11) NOT NULL,
    `profile_data` mediumblob,
     KEY `session_req_idx` (`session_id`, `request_id`),
     KEY `timestamp_idx` (`timestamp`)
)

Some notes:

All the mediumblob columns are JSON objects, but we haven't upgraded to MySQL 5.7.8 yet.
All tables have the same columns and indexes.
Each table contains in between several million and several billion rows.

One of the problems I seem to have is that I can't limit the inner queries (if using them), and joins also don't appear to work.

What I wonder is mostly: can this be efficiently solved using a SQL solution, or would this lean more towards one of the NoSQL (for example, a graph database) solutions?

--
EDIT:

Queries are built up using a loop that concatenates subqueries in the following manner:

For a single table (e.g. "sessions that have a profile view after timestamp x"):

SELECT DISTINCT
    `grouped`.`session_id`
FROM (
    SELECT
        `session_id`
    FROM
        `profile_views`
    WHERE
        `timestamp` > x
) `grouped`
ORDER BY
    `session_id` DESC
LIMIT
    100

Two tables (e.g. "Sessions that have a profile view and login"):

SELECT DISTINCT
    `grouped`.`session_id`
FROM (
    SELECT
        `session_id`
    FROM
        `logins`
    WHERE
        `timestamp` >= x
    AND
        `session_id` IN (
        SELECT
            `session_id`
        FROM
            `profile_views`
        WHERE
            `timestamp` >= x
    )
) `grouped`
ORDER BY
    `session_id` DESC
LIMIT
    100

I'm looking into joins, but at the moment they appear to return different results, for example something like the following:

SELECT DISTINCT
    `A`.`session_id`
FROM
    `logins` `A`
INNER JOIN
    `profile_views` `B`
ON
    `B`.`session_id` = `A`.`session_id`
WHERE
    `A`.`timestamp` > x
ORDER BY
    `session_id` DESC
LIMIT 100;


texts: How to use timestamp/rowversion data types in Visio?

texts:
I am using Visio 2010 to create a ER diagram. When I insert the column names, I can't find timestamp or rowversion data types.

Is there an alternative?


texts: Question about sp_AllNightLog from Brent Ozar

texts:
I'm not sure it's the right channel for my question. I hope so. Does anyone have experience with sp_AllNightLog from Brent Ozar?. i installed it, but it doesn't create backups.

my stepps:
1. installed Ola Hallengren’s Maintenance Scripts
2. enable xp_cmdShell

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdShell', 1;
GO
RECONFIGURE;
GO

installing sp_AllNightLog, sp_AllNightLog_Setup, and sp_DatabaseRestor (no change in the scripts)
executed sp_AllNightLog_Setup:

sp_AllNightLog_Setup
  @RPOSeconds = 60,
  @RTOSeconds = 60,
  @BackupPath = 'B:\T_Backup',
  @RestorePath = 'B:\T_Backup',
  @Jobs = 2,
  @RunSetup = 1;

Jobs enabled: sp_AllNightLog_PollForNewDatabases and sp_AllNightLog_Backup_*

if i look in the folder B:\T_Backup then i don't see any files. Does anyone have any idea what it could be?


texts: How to design tables for airbnb style booking system

texts:
all. My English is not very good, but I will try to describe my question as clear as possible.

I want to design a airbnb(https://www.airbnb.com) style booking system, this kind of booking system allows somebody books idle resources of the others.

Here is my system needs:

Resource provider can set resources' available time of booking. The minimum time slot is 1 hour between 9am-12pm.
The default setting can be set as repeat periodically. For example: 5pm-8pm every Saturday available.
Also resource provider can change the time table whenever necessary. For example: stop provide the resource this Saturday because it's not available temporary.
Other users can book single or multiple time slot of any resource provided by many resource provider as long as it's available
Booking is open for the next 28 days.

Now I have difficulty in designing tables for the resource provider to set the available time.

Can your guys give me some advice?


texts: How does one correctly implement optimistic locking in MySQL?

texts:
Possible Duplicate:
  How to correctly implement optimistic locking in MySQL

Our team has deduced that we must do #4 below when doing optimistic locking (all data is read and update using a version field) or else there is a risk that another thread can update the same version of the record. I'd like to validate that this is the best way to do it.

Create a version field on the table you want to use optimistic locking for e.g. column name = "version"
On selects, make sure to include the version column and make note of the version
On a subsequent update to the record, the update statement should issue "where version = X" where X is the version we received in #2 and set the version field during that update statement to X + 1
Perform a SELECT FOR UPDATE on the record we are going to update so that we serialize who can make changes to the record we are trying to update.

To clarify, we are trying to prevent two threads which select the same record in the same time window where they grab the same version of the record from overwriting each others if they were to try and update the record at the same time. We believe that unless we do #4, there is a chance, that if both threads enter their respective transactions at the same time (but have not issued their updates yet), when they go to update, the second thread that will use the UPDATE ... where version = X will be operating on old data.

Are we correct in thinking we must do this pessimistic locking when updating even though we are using version fields/optimistic locking?


texts: Using collate: When to use and when not to use

texts:
In one of my applications we had tsql like shown below

SELECT DISTINCT [Dblist].[DbName] AS [DbName]
FROM [Common].dbo.[Dblist]
WHERE dbname not in (
    SELECT [name]
    FROM master.dbo.sysdatabases )

For one of my customers it gave an error related to collation as the database Common (part of my application) has default collation sql_latin1_general_cp1_ci_as whereas master db has collation latin1_general_ci_as. I found a solution using collate and it worked. I want the solution to work on all collations.

SELECT DISTINCT [Dblist].[DbName] AS [DbName]
FROM [Common].dbo.[Dblist]
WHERE dbname not in (
    SELECT [name] COLLATE DATABASE_DEFAULT
    FROM master.dbo.sysdatabases )

Now I am confused. Should I use COLLATE in all queries which involve system databases?
When to use collate and when not to use?

Also, is the above way of using collate proper (like COLLATE DATABASE_DEFAULT)?  Here DATABASE_DEFAULT  is latin1_general_ci_as itself as it is the collation of master database. Then how did the solution work as they again don't match with Common database? I want solution which will work on all collations.


texts:
A simple question,

We have just done a P2V and are re-testing the environment and moved from SQL 2005 to SQL 2008R2. I am taking this chance to redo my back up plans. I have set up a remote folder on the domain and am having trouble accessing it. I have permissions on the folder of SERVICE plus AD administrators, and my SQL instance is running the Local Network service built in account.

This is not working I am trying to follow the question Here but not sure how to use this proxy in a maintenance plan / sql job. I am trying to avoid setting my SQL service accounts to administrator. Can this be done?


texts: arbiter bring down the three node mongodb replica set

texts:
I have a typical 3 node mongodb replica set. One primary, one secondary and one arbiter. I thought the replica set would survive one node offline but it didn't. Please help me understand what went wrong.

Here is what I observed. Are they supposed to work like this?

When I take the arbiter offline (killing the instance), both two data nodes become secondary (they were primary and secondary).
When I take the primary offline (killing the instance), the secondary and arbiter remains secondary and  arbiter. There is no voting to select the new primary.

Thanks!

Here is the config

{
  "_id": "0",
  "version": 8,
  "protocolVersion": NumberLong(1),
  "writeConcernMajorityJournalDefault": true,
  "members": [
    {
      "_id": 3,
      "host": ":27017",
      "arbiterOnly": true,
      "buildIndexes": true,
      "hidden": false,
      "priority": 0,
      "tags": {

      },
      "slaveDelay": NumberLong(0),
      "votes": 1
    },
    {
      "_id": 4,
      "host": ":27017",
      "arbiterOnly": false,
      "buildIndexes": true,
      "hidden": false,
      "priority": 0,
      "tags": {

      },
      "slaveDelay": NumberLong(0),
      "votes": 0
    },
    {
      "_id": 5,
      "host": ":27017",
      "arbiterOnly": false,
      "buildIndexes": true,
      "hidden": false,
      "priority": 3,
      "tags": {

      },
      "slaveDelay": NumberLong(0),
      "votes": 1
    }
  ],
  "settings": {
    "chainingAllowed": true,
    "heartbeatIntervalMillis": 2000,
    "heartbeatTimeoutSecs": 10,
    "electionTimeoutMillis": 10000,
    "catchUpTimeoutMillis": 60000,
    "catchUpTakeoverDelayMillis": 30000,
    "getLastErrorModes": {

    },
    "getLastErrorDefaults": {
      "w": 1,
      "wtimeout": 0
    },
    "replicaSetId": ObjectId("")
  }
}


texts:
I'm working on the backend of a quality evaluation tool. And I hit a the limit of too many columns.

SQLSTATE[HY000]: General error: 1005 Can't create table xxx.xxx (errno: 185 "Too many columns")

And because I've seen this question. Let me explain:
The company I work for is selling advanced sensors with algorithms in the firmware. With every new firmware, we want to compare it to the previous ones, to check if something still works, has improved or is now worse. For this, we have a number of test scenarios (200+). Each scenario consists of (400-2.000) measurements from which all are needed. I call a run through one scenario a record.
In my previous design I created a table for each record and I ended up with 50.000+ tables, which is not optimal, to say the least. In an attempt to normalize the data I created the following schema:

There is on table for each scenario (slug_1 and slug_2 here). And a idmap table to relate each record with the firmware and other important data (here shown as other_stuff). The problem now is, that I need 1-4 columns for each measurement in one scenario which means 8.000 columns max, which is a new problem.
Besides the id and the table name, there are no relations, nor search requirements on these large tables. Each request would consist of two rows from the same table to compare them.
Question:

Is there a better design to store the data?

I thought maybe I should store the measurements as json string or serialize the measurements array directly to binary. I could use a compression algorithm before storing and decompress the data after reading?

Are this many columns even that bad, if they are never searched?

If not, how can I get around the

errno: 185 "Too many columns"

Edit 1:
To answer some questions:

Are all the tests pass/fail, or do you need to store more detail.

Every measurement consists of two values at the moment. Ok (bool) and Ct (int). Ok just stores if there was an error or not. The sensor can provide ~10 different errors, why the test failed, but I'm reducing this to true/false for now. If there is a need to store the error, I'll create an error table and store the relation instead. The second value Ct stores the time needed for this measurement. It is possible that there will be a 3. value in the future, but for now this is not needed.

Does the type of detail depend on the test?

No, it doesn't in all scenarios the measurements are the same.

Is there a Product or Sensor these tests apply to?

Yes, I store all sensors, firmwares, and other things in separate tables like other_stuff and relate it with the idmap table to the records of the tested scenarios.

Here some info what I'm working with:
Laravel 8 with php eloquent on a mysql database.


texts: Conversion error on paramater but not variable

texts:
I've got a stored procedure that accepts a parameter;

CREATE PROCEDURE [dbo].[Stored_Proc_Name] (@ParamID NVARCHAR(255) = NULL)

I call this stored procedure and pass in a uniqueidentifier, something like this;

EXEC [Stored_Proc_Name] 'a6ed99c1-29c8-43f4-9e3a-0065e6dc7fc1'

The stored procedure does a bit of XML processing and returns a result set, it's fairly simple.

For a few paramaters that are passed I'm getting a type conversion error;

Msg 8114, Level 16, State 5, Procedure Stored_Proc_Name, Line 0
Error converting data type nvarchar to int.

I initially thought that this was an issue with the data but the issue resolves itself if i declare a local variable within the stored procedure, like this and then use that local variable in place of the parameter. The only places that this parameter is used is within WHERE clauses within my stored proc.

CREATE PROCEDURE [dbo].[Stored_Proc_Name] (@ParamID NVARCHAR(255) = NULL)
AS
DECLARE @localID nvarchar(255)
SET @localID = @ParamID

The issue is resolved and the procedure runs fine.

Any ideas on the next step of the investigation or something obvious that I've missed?

Update:

Upon further investigation, this was an issue with an XML attribute missing a [1] so it appears to have been trying to convert multiple attributes which was causing the error. I'm currently investigating how it was fixed with the variable and will update once I have a sensible answer. I'm going to mark the answer as accepted so that somebody gets the bounty.


texts: Is there a standard way to replace codes with values from a lookup table for reporting or analytics?

texts:
Suppose I have a "main" table that was probably heavily normalized and consists largely of columns that merely contain codes that are lookups into other tables (they are probably foreign keys but feel free not to assume so in your answer if it makes sense).  For instance, maybe this main table is a list of people and one of the columns is for favorite_color.  Of course then our lookup table would looks something like this:

color_cd    color_name
0           Unknown
1           Red
2           Blue
3           Orange
4           Other

Now if I'm creating reports from this main table or perhaps feeding this data into some software for domain experts to use, I'd rather replace the favorite_color code with the color_name from the lookup table.  One solution of course is to do this with a simple JOIN in my SELECT:

SELECT main.col1, main.col2, colors.color_name as `Favorite Color`
  FROM main LEFT OUTER JOIN colors ON (main.favorite_color = colors.color_cd);

This gets the job done.  Now suppose that the main table has dozens or maybe hundreds of such columns and millions of rows.  Some of the lookup tables are small, like the above example, but others might have hundreds or thousands of values.

Is there any sort of standard way of doing this transformation, or is the writing a long with dozens or hundreds of joins the way to go?  Is this type of thing a typical component of any ETL product?  Is it preferable to write a script that handles this without joins and outside of SQL?  Please forgive what are likely some abuses or misuses of terminology; I'm not even sure if the above process has a name.


texts: Drop user did not reduce size of tablespace file

texts:
I have a  development environment in which I frequently copy a certain schema.

When I'm done with the copy, I will use OracleEM to drop that user/schema.

My problem is that this does not seem to reclaim the space that the schema was occupying from my tablespace file.

I read doing an DROP SCHEMA .... CASCADE would do this, but the users are already deleted.

Now my entire drive is filled up.

How can I reclaim this space now?


texts: Can I use impdp FULL=Y for an Oracle database charset conversion?

texts:
I need create a copy of an Oracle 12.2 instance (Oracle Restart) running on an Linux VM. The new instance needs a charset conversion from WE8ISO8859P1 to UTF8

I started by creating a clone of the VM that is now up and running.

I was thinking about following these steps:

expdp FULL=YES from the cloned machine to a safe storage location
destroy the database from the clone using DBCA
create a new database on the clone using DBCA setting UTF-8 as charset
impdp FULL=YES from the previous exported datapump

I have some doubts about the impdp FULL=YES... will this work or will it overwite some configuration on SYS or other Oracle maintained schemas with some values that still refers to WE8ISO8859P1 charset?

Will this work or should I exclude all the Oracle maintained schemas from the impdp?


texts: Why does SqlSentry Plan Explorer not give the duration of my queries?

texts:
I have a large nasty stored procedure in a 13gig database that takes 45 minutes to run when I include the actual execution plan. I save the query plan from SSMS and open in up in SQL Sentry Plan Explorer and I don't see a duration for any of the queries:

I've searched through the entire list of queries and resorted it, the entire column is empty. I know some queries in this particular stored procedure take a long time to run. While the stored procedure is large, it not a stored proc with millions of microsecond queries, and I know for a fact that certain queries take minutes to run.


texts: Postgres declare table as a parameter for a function?

texts:
I would like to create a function that operates on a table, for example

create or replace function test(t table)
    returns void language plpgsql as
$func$
begin
    select * from t limit 10;
end;
$func$

Then, I can call the function with any tablename, e.g.

select test(myTable);

How would I do something like this?


texts: PostgreSQL determine column type when data_type is set to ARRAY

texts:
I came across an oddity where PostgreSQL sets the data_type to ARRAY in the information_schema.columns table. I did the following query to gain some insight:

SELECT * FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name='__table_name__';

I noticed the udt_name column and am wondering if it is a reliable way to determine what type of array the column's data is?

SELECT column_name, data_type, udt_name FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name='__table_name__';


texts:
I'm trying to extend PostgreSQL to index bit strings up to 1000 bits. (These bit strings are created by quantization of high-dimensional vectors, so for each dimension up to 4 bits are assigned). Insertions are rather infrequent, whereas searches are the mostly used operation. In a search, I would like to get all the rows that exactly match the bit string.

It looks like a perfect job for GIN (in combination with my own data type), or what do you think?


texts:
Is this a good idea to manage members of a group like so:

*Assuming members can belong to one group and no other

*Simplified model

Group:
  name
  someid

User:
  name
  ForeignKey(Group)
  state #integer 0-no group 1-waiting for approval from a group admin 2-belong to an group

Something like facebook groups


texts: Risks associated with automatically shrinking TempDB as a SQL agent job?

texts:
I have a situation where I have a constraint on TempDB to not exceed 350 GB in size. I'm using autogrowth 10 (percent) on TempDB, as I've read that it is supposed to be best practice on databases exceeding 500 MB.

When I used

 dbcc checkdb with estimateonly

on every database, I've noticed that the estimated requirement on TempDB is much lower than I currently have. Summing the values returned by dbcc checkdb with estimateonly gives a value of about 75 GB.

TempDB's size is currently 300 GB. I can't increase the permitted size of TempDB any further.

I've heard that our Company previously had a SQL job which shrinked TempDB automatically if it exceeded some value, but it is not used in our new environment. I am conflicted however, that being too liberal with shrinking TempDB may cause issues.

Using the information from this post, is there any major risks associated with autoshrinking TempDB to the size of, say, 200 GB if the current size exceeds 200 GB or so, seeing that dbcc checkdb with estimateonly returned an estimated minimum size of 75 GB which would lead this to a rather high relative buffer?

I've read that is not the optimal practice to shrink TempDB, but may it be more "legimate" in this instance in reference to the estimated minimum size of TempDB?

  EDIT

I've made the following Query which shows, for instance, internal object MB space, internal object dealloc MB space, statement text (the Query), total elapsed time, is_user_process:

WITH task_space_usage AS (
    SELECT dmv_tsu.session_id,
           dmv_tsu.request_id,
           SUM(dmv_tsu.internal_objects_alloc_page_count) AS alloc_pages,
           SUM(dmv_tsu.internal_objects_dealloc_page_count) AS dealloc_pages,
           dmv_es.login_name,
           dmv_es.host_name,
           dmv_es.program_name,
           dmv_es.is_user_process,
           dmv_es.total_elapsed_time,
           er.start_time,
           er.estimated_completion_time,
           er.command
    FROM sys.dm_db_task_space_usage dmv_tsu WITH (NOLOCK)
    INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
    INNER JOIN sys.dm_exec_requests er ON dmv_tsu.session_id = er.session_id and dmv_tsu.request_id = er.request_id
    WHERE dmv_tsu.session_id <> @@SPID
    GROUP BY dmv_tsu.session_id, dmv_tsu.request_id,dmv_es.login_name,dmv_es.host_name,dmv_es.program_name,dmv_es.is_user_process,
           dmv_es.total_elapsed_time,er.start_time,
           er.estimated_completion_time,
           er.command
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       ISNULL(
           NULLIF(
               SUBSTRING(
                 EST.text,
                 ERQ.statement_start_offset / 2,
                 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset
                  THEN 0
                 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan,
       TSU.host_name,
       TSU.login_name,
       TSU.program_name,
       TSU.is_user_process,
       TSU.total_elapsed_time,
       TSU.start_time,
           TSU.estimated_completion_time,
           TSU.command
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 10 DESC;

My question is:

Is it more beneficial to have a Query like this, in combination with a SQL agent job which says: `

If TempDB > maxSize then CheckTheQueryPosted, terminate all queries which is a user process

I know this is not code, but I meant something in the lines of this concept?


texts: Assigning permissions for sysmail_update_account_sp

texts:
I have a Microsoft SQL Server 2014. The mail system is configured via DBMail and
there is one stored proc in main web app (having its own database) which executes msdb's sysmail_update_account_sp by selecting the appropriate profile using msdb.dbo.sysmail_account.

The problem I am facing is the web application has a SQL login and I want to switch context and execute sysmail_update_account_sp.

EXEC AS USER = @UserName

    SET @AccountNr = ( SELECT TOP 1 account_id FROM msdb.dbo.sysmail_account WHERE name = @DatabaseName ORDER BY account_id )

    IF @AccountNr > 0

    EXECUTE msdb.dbo.sysmail_update_account_sp
                @account_id = @AccountNr
                , @email_address = @ParamSMTPFrom
                , @account_name = @DatabaseName
                , @display_name = @DatabaseName
                , @replyto_address = @ParamSMTPFrom
                , @description = @EmailAccountDescription
                , @mailserver_name = @ParamWebAppSmtpServer
                --[ , [ @mailserver_type = ] 'server_type' ] 'optional only SMTP supported'
                , @port = @ParamWebAppSMTPPort
                , @username = @ParamWebAppSMTPUser
                , @password = @ParamWebAppSMTPPwd
                , @use_default_credentials = 0 -- we will always have nor domain credientials @DefaultCredentials
                --use account name and password
                --not databse credentials
                , @enable_ssl = @SSL -- bit 1 for use ssl
REVERT

Error occurring is:

  Cannot execute as the database principal because the principal
  "SERVERNAME\dbMailUser" does not exist, this type of principal cannot
  be impersonated, or you do not have permission

Things I checked:

SERVERNAME\dbMailUser has DatabaseMailUserRole permission in msdb currently.
If I add the executing stored proc user (from web app) as sysadmin,I don't have to switch or anything. But I want to limit the permission to it's minimum.

How can I execute as a different user (SQL or Windows Login) by switching context properly? The current SQL login is a database owner.

The requirement is the login should be able to configure DBMail from a stored procedure and able to send mails (the DatabaseMailUserRole), without it being in the sysadmin role.

Am I in the right direction, regarding my understanding of how DBMail configuration works?


texts: Azure automatic fail-over vs always on availability groups clarification

texts:
I am trying to understand automatic failover vs always on availability groups in Azure hybrid and geo-region situations:

Is it possible to have on-premises SQL databases, VM SQL databases and Azure databases in the same availability group? Or does one need to separate on-premises databases from cloud databases in separate always-on availability groups?
Automatic failover is not possible in hybrid situations, it has to be forced manually. But, can automatic failover be implemented across regions in the cloud or does it have to happen in the same region?


texts: How to manage concurrencies without locking tables in MySQL?

texts:
In our php app, we have a table that records orders recieved at the reception. Since we can have up to 7 agents/active accounts, how is it possible to manage the concurrencies in the orders table without having to lock the table each time.

The issues are:

That before inserting any row in the main table, there must be a chain of check ups to run on the customer, etc... which impose multiple selects, then the insert.
Orders are loaded by batch, not one by one, by the agents. I fear locking the table will impose them to redo the orders' registration again due to the database forbiding access.

Should I even be worried of the writing management of MySQL or, indeed I have to procede to a locking system?


texts: How to find Oracle's installed version and release from a program?

texts:
I need to determine from a program what version of Oracle is installed in each of the Oracle Homes on a server.  As there may not be any databases created in the Home yet, I need to be able to do this outside of the database (i.e., without connecting to the database).  Also, it would be highly preferable to be able to do this from a remote program.

This is from a windows program running .Net (C#, if that matters).

I am currently reading remote registry keys (using this technique: https://stackoverflow.com/questions/1566547/how-to-read-remote-registry-keys), to find all of the Oracle Homes according to this method.  This works fine, however, I have looked around those keys and do not see any information on the exact version/release.

The name of an Oracle Home itself is of course 1) not a reliable indicator and 2) does not have the exact version/release (for instance "10.2.0.4.0").  Basically I am looking for a way to figure out what the Oracle Universal Installer tells you in the Installed Products button.

I should clarify, all of the Servers will be running Windows 2003-2008.


texts:
I have a doubt. What means this Scalar Operator in the print below?

I've been investigating our database cause we have severals RESOURCE_SEMAPHORE_QUERY_COMPILE . I've seeing a lot of queries using this in the joins which costs more. Is it normal or it's not a coincidence?

Thanks,

Armando


texts: How to use postgresql service file in pgadmin4

texts:
My service file is working well in PgAdmin3 and in QGIS.
I'm trying to use the same service file in PgAdmin4. But no luck, I still have the same message:

  Unable to connect to server: definition of service "XXXX" not found

My service is located in:

/etc/postgresql-common/pg_service.conf

I'm on Ubuntu 18.04 and I used this webpage to install PgAdmin4: https://askubuntu.com/a/831263/363564


texts: PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly

texts:
We use PostgreSQL 9.1.7 on Ubuntu Linux 12.04 on a master server and PostgreSQL 9.1.7 on FreeBSD 9.0-RELEASE on a replica server. The replica and master servers return different results on the same SQL query. A query plan shows that an index (a BTree one, we do not use hash indexes at all) is used to get the result so it looks like an index is in inconsistent or incomplete state on the replica server.
The query on the master server:

db1=# select id from users where email='xxxx@xxxx.net';
   id
---------
 1698116
(1 row)

db1=#

The query on the replica server:

db1=> select id from users where email='xxxx@xxxx.net';
 id
----
(0 rows)

db1=> select created_at from users where id=1698116;
         created_at
----------------------------
 2013-03-04 10:40:05.221214
(1 row)

db1=>

As you can see the replica DB already contains a user with proper ID so the data is in place but just not indexed yet for some reason. We double checked the replica was in receiving/reapplying state so this was not a temporary outage. The user never got indexed. We also used to experience similar problems with PostgreSQL 9.0 on CentOS 5.6 so we don't think this is something FreeBSD- or PostgreSQL 9.1-specific.

We use the replica server to run lots of heavy SQL queries, can this be a root of the problem? Anyway how can we efficiently detect and prevent situations like this in the future? The replica was not down today and there was no single error line in logs so we detected this inconsistency only by occasion.


texts: Mysql 5.6 - Create view with progressively increment a datetime field grouped by


texts: pg_dump issue while dumping single schema

texts:
pg_dump does refuse to dump a given schema in my DB.

pg_dump -d <dbname> -s -n dm_geo -f <filename>

Result

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.5.4
-- Dumped by pg_dump version 9.5.4

SET statement_timeout = 0;
...
SET row_security =off;

--
-- PostgreSQL database dump complete
--


texts: How to select multiple values into an array and loop through? (postgres 9.3)

texts:
I have simple table for the sake of argument. I have a function that selects ids and loops through them called loop_test. I can select an array of ids and loop through them, causing my changes in a transaction.

CREATE OR REPLACE FUNCTION loop_test() RETURNS void AS $$
DECLARE
        _ids_array INTEGER[];
        _id INTEGER;
BEGIN
        SELECT ARRAY(SELECT id FROM loop_test) INTO _ids_array;
        FOREACH _id IN ARRAY _ids_array
        LOOP
                UPDATE loop_test SET looped = TRUE WHERE id = _id;
        END LOOP;
END;
$$ LANGUAGE plpgsql;

Table:

db=# \d loop_test;
      Table "public.loop_test"
    Column     |  Type   | Modifiers
---------------+---------+-----------
 id            | integer |
 other_id      | integer |
 id_copy       | integer |
 other_id_copy | integer |
 looped        | boolean |

db=# select * from loop_test;
 id | other_id | id_copy | other_id_copy | looped
----+----------+---------+---------------+--------
  1 |       10 |         |               |
  6 |       15 |         |               |
  2 |       11 |         |               |
  7 |       16 |         |               |
  3 |       12 |         |               |
  4 |       13 |         |               |
  5 |       14 |         |               |
(7 rows)

When I call select loop_test(), I get the following results:

db=# select * from loop_test;
 id | other_id | id_copy | other_id_copy | looped
----+----------+---------+---------------+--------
  1 |       10 |         |               | t
  6 |       15 |         |               | t
  2 |       11 |         |               | t
  7 |       16 |         |               | t
  3 |       12 |         |               | t
  4 |       13 |         |               | t
  5 |       14 |         |               | t
(7 rows)

I would, however, like to create a function to select both the id and the other_id into an array. I was told about using something like agg_array, but I don't completely understand how that works.

I was imagining something like the following?

CREATE OR REPLACE FUNCTION agg_loop_test() RETURNS void AS $$
DECLARE
        _ids_array INTEGER[][];
        _id INTEGER;
BEGIN
        SELECT AGG_ARRAY(SELECT id, other_id FROM loop_test) INTO _ids_array;
        FOREACH _id IN ARRAY _ids_array
        LOOP
                UPDATE loop_test SET id_copy = _id[0], other_id_copy = _id[1] WHERE id = _id[0];
        END LOOP;
END;
$$ LANGUAGE plpgsql;


texts: Which MongoDB administration UIs offer a table view (OS X)?

texts: The only admin UI we found that offers a table view is MongoVue, which is only available on Windows. We're on Macs with OS X. Do any exist?


texts: SQL Server making arithmetic abort enabled

texts:
I am using SQL Server 2008.

In my stored procedures, I often have to call for XML data type, functions (like split).

Is it possible to permanently enable arithmentic abort for a database? I know it can be done using properties window > options but is there any script i can use to turn it on?

The reason to look for script is obvious; i have too many DBs to do it manually.

I assume set arithabort off and set arithabort on turns on arithabort only for the context.


texts:
I have a query that calls a remote table through Linked Server.
However, in the execution plan the Remote Query is 71% of total cost and the time taken to run is over 4mins. It returns all rows in the remote table (Actual Number of Rows value is over 1 million).

If I run the remote query separately, the Actual Number of Rows value is 2.

Why does it load all rows?

Below is the subquery part that calls the remote tables...

If I run the remote query part (the subquery) in a new window, the Actual Number of Rows is 2, and time taken is 1 second...


texts: Should I reindex/rebuild while my DB is up and running or should I take it offline? Also after doing some digging around I came across this article that recommends Rebuilding after 40% fragmentation and Reindexing between 10% to 40%. Can anyone verify this? My DB is a quite large and is slowing down more and more.


texts: How are derived table implemented in MySQL

texts:
When MySQL makes a derived table as a result of

SELECT (SELECT ...) etc
or SELECT * FROM a JOIN (SELECT * from B) etc
or SELECT * FROM (SELECT ...)

Are these derived temporary tables created in-memory or the file-system? Does it depend on the size of the derived table?


texts:
I am faced with this situation,  an application that has multiple states for different entities. Entity A can have Opened, Closed and Waiting. Entity B has its own set of states, I was considering using Attribute and Value table to fill this gap without tightly coupling static strings in the app which would not scale as well as using the database. I am now sure how to be structure this in the database however and need some help, currently I have
Is this a good approach?
Update
I was trying to have the database save states that are possible for other Entities. For example, Orders can have states such as Completed or Processing etc... instead of having enums in my code I was thinking to have those saved in the database and use them from one source which I think would be more scalable in the event we get more states in the future. I was looking for an efficient way of storing states that could reference different entity types.


texts: Why is searching by one varchar column not working correctly on replica?

texts:
PostgreSQL: 9.6, master, replica.
Async replication, wal archiving.

We detected that if we execute a select query on a varchar column against a replica, then the results return no rows, but if we execute against a master then the results will be correct.

SELECT id FROM platform WHERE name = 'test'

Master: 10
Replica: now rows

SELECT name FROM platform WHERE id = 10

Master: test
Replica: test

SELECT id FROM platform WHERE name in ('test', 'test1')

Master: 10, 11
Replica: 11

SELECT name FROM platform WHERE id in (10, 11)

Master: test, test1
Replica: test, test1

I didn't find any information about such issues and I don't know how to solve it.


texts: Handle concurrency on Sql Server to modify specific column

texts:
I have table that suffers from racing condition and I don't know how to fix it. I can't solve it 100% in code, so I need the server to help me.

The scenario

Admins of a system may accept or reject an appointment.
This can be done by email (via token etc) or by logging in the system and doing it via admin panel.

The problem appears when two admin do it at the same time:

t = 0:
admin A clicks accept from the system email;
admin B clicks reject from the admin panel;

t = 1:
back-end checks was appointment already handled?
This happens at the same time for both admins, so they both
       pass the test -> appointment is waiting for approval...

t = 2:
back-end A sends query "update status to accepted"
back-end B sends query "update status to rejected"

t = 3: both sqls hit the server.
The server than changes the appointment to accepted, then proceeds to execute the second query, and changes the appointment to rejected.

This table has heavy traffic, so locking it is not an option.

What I think could solve my problem, is to:
first: have a query lock the row;
second: check the the status column to see if it is already set;
third: if not set, then set the value;
fourth: release the lock;

meanwhile, the second query would keep trying to acquire the lock. After it succeeded, it would then proceed to check whether the value was already set, and then it would find that indeed it was, and would return an error or something.

How can I go about doing something like this on Sql Server?

The flow would look something like this:

while (can't read) {

    try to acquire lock;
}

// lock acquired.
read appointment from table;
if value is null, set the value;
release lock;

I figured out my framework is using READ_COMMITTED_SNAPSHOT as default.


texts: Does clustering provide disaster recovery in MS SQL Server?

texts: I mean can the nodes of MS SQL Server failover clustering be geographically separated and besides high availability also provide disaster recovery?


texts: How can I establish whether it would be a good idea to upgrade from Express to Standard?

texts:
In Express 2019 running on Linux, If I right click my database I can see that it is size 11506 Mb with 1963 Mb free space. Currently showing 17 users.

I am trying to work out whether it would be better to delete records or upgrade.

Given the cost to upgrade is significant, how can I establish what performance benefits we might gain?

If we trial upgradeing can we roll back?


texts: Wrong Memory is displaying in task manager for Default and Named instance in Sqlserver

texts: I have installed 2 instances on a Sqlserver 2016.When the Performance of the server is degrading ,  i could see huge transactions are happening on the Default instance and there were no connections and a single small database in a named instance.But when i see in the task manager , i see the memory of the named instance is increasing drastically with no CPU and default instance remains idle with High CPU.


texts: AWS RDS showing more disk usage than PgAdmin statistics

texts:
I am using AWS RDS to host one PostgreSQL database. I notice that the disk usage at the AWS dashboard is much more (2x) than what is shown at PgAdmin. Hopefully someone can help me to figure out it.
AWS

PgAdmin

I find this issue after I tried several times to create a materialized view for caching very complex query results. All failed because of out of space (for computing?). I expect the remaining space (~7GB originally) would return but it didn't!!


texts: Data integration name of best practices to avoid corruption, caused by becoming out of sync?

texts:
I have identified an issue in the design of a system and I am trying to find the right language to describe the cause of the issue so I can report it to managers, and get the issue fixed.

The problem is we have a web service syncing data by copying changes (based on a datetime of last edit column) in a student enrollments table from one system to another system. But because the sources system deletes rows when a student drops a subject, drops are not being synced.

To be able to detect the drops the system needs to maintain a state of what it has copied to the second system so it can recognize when rows have been dropped or needs to validate that they tables are in sync, but I am not sure how to describe this and reference a specific best practice or methodology to backup my argument.

Does anyone know the name of the best practices or data integrity rules that would prevent this type of logical error in the design?


texts: Dynamically records not getting to cursor in trigger

texts:
I am using cursor in trigger in MySQL. I want to get records of recently added id. I am using this:

CREATE DEFINER=`root`@`localhost` TRIGGER `calculate_property_ratings` AFTER INSERT ON `node` FOR EACH ROW BEGIN
DECLARE property_id integer;
DECLARE ratings integer;
DECLARE cur CURSOR FOR SELECT field_rating_rating, field_property_detail_target_id from field_data_field_rating as fr JOIN field_data_field_property_review as pr on pr.field_property_review_target_id = fr.entity_id JOIN field_data_field_property_detail as pd on pd.entity_id = pr.entity_id WHERE pr.field_property_review_target_id = NEW.nid;

DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
call dolog(concat('node id-->', NEW.nid));
IF(NEW.type = 'review') THEN
    call dolog(concat('type-->', NEW.type));
    OPEN cur;
        ins_loop: LOOP
            FETCH cur INTO ratings, property_id;
            call dolog(concat('ratings -->', ratings));
            call dolog(concat('property_id -->', property_id));
            IF NOT(EXISTS(SELECT 1 FROM average_rating_property WHERE (pid = property_id))) THEN
              INSERT INTO average_rating_property (pid,total_ratings,total_count) VALUES (property_id, ratings, 1);
            ELSE
              UPDATE average_rating_property SET total_ratings = total_ratings + ratings AND total_count = total_count + 1  WHERE pid = property_id;
                call dolog('at update');
            END IF;
        END LOOP;
    CLOSE cur;
END IF;
END

If I provide static value instead of NEW.nid at line no.4 then I am getting records and  loop is working, else loop is not working. It means NEW.nid is not working in cursor. But it is working while logging at line no. 7.

Is there any mistake in my code?


texts: Oracle DB export, possible charset conversion if NLS_LANG not set

texts:
I need to export an Oracle database from one server and import it on another server. But I am getting the following information message at the beginning:

  Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit
  Production Export done in US7ASCII character set and AL16UTF16 NCHAR
  character set server uses UTF8 character set (possible charset
  conversion)

Although the export still terminates successfully without warning, I was wondering whether the data in the database is still correct. Is there a need to set the NLS_LANG variable to match the character set of the server (in this case UTF-8)?

If I set the NLS_LANG variable to UTF-8 I don't get this information message. But the target server where I import the database again shows the following:

  import done in UTF8 character set and AL16UTF16 NCHAR character set
  import server uses AL32UTF8 character set (possible charset
  conversion)

If I understand that correctly these two servers use (slightly) different character sets - UTF-8 and AL32UTF8.

I am confused on what I should set the NLS_LANG variable or even whether it is necessary to set. Should I use AL32UTF8 as NLS_LANG for the export and import?

EDIT: In case this information is important: I am using a SUSE Linux and for the export I use the command exp that comes with Oracle.


texts: I have several work packages for each I have setup up a job in sql server agent. Sometimes I should run all together. What is the best way to run them all in a given sequence? I'm a bit surprised that sql server agent is not able to include jobs as job steps to be executed. I did try


texts: Effect of logical fragmentation of range scan in SQL Server Table

texts:
To the best of my knowledge, this is what happens during a range scan of a table for some data when the table has got a clustered index:

The lower range is first found out, traced to the leaf node where it is present, then traversing the leaf level page by page through the next page pointer as long as the largest value in that page is less than the larger value of the range scan condition.

Once such a page is reached where the last row exceeds that, the process stops, does a binary search within that page to find out that record, and returns all records found till now.

This bringing up page after page can be expensive I/O operation if the pages are not in memory, hence to mitigate this, read-ahead scans are done at one level up by the parent pages, so that by the time the process advances to a leaf-level page to verify it, it has already been brought to memory by one of the parent level pages.

I am watching some SQL Server tutorial videos where it says that if there is logical fragmentation at the leaf level, so that the physical page order does not coincide with the logical page order, the read-ahead scan fails and the db is forced to do a full I/O operation to retrieve all pages.

Can someone tell me why? Why is the physical order important at all? Isn't the entire traversal done through logical traversal via the next page pointers of the leaf level pages?

What effect does the logical fragmentation have on the read-ahead scan exactly?


texts:
I'm a newbie to data warehousing and I've been reading articles and watching videos on the principles but I'm a bit confused as to how I would take the design below and convert it into a star schema.

In all the examples I've seen the fact table references the dim tables, so I'm assuming the questionId and responseId would be part of the fact table? Any advice would be much appreciated.


texts: MySQL equivalent of PostgreSQL's psql \c command

texts: I'm learning PostgreSQL though oddly enough figured out to switch users while in the psql command line to use \c database username; what is the equivalent to switch from one user to another in MySQL's command prompt?


texts:
If someone walk me though the following questions I'd be very grateful.

  Consider a relation R(A, B, C, D, E) with the following functional dependencies:

A, B → C
D, E → C
B → D

  i) decompose R so that it is in 2nd normal form.

  Second part of the question: ii) suppose R is populated with the following data:

| A  | B  | C  | D  | E  |
| a1 | b1 | c1 | d1 | e1 |
| a2 | b1 | c2 | d1 | e2 |
| a2 | b2 | c1 | d3 | e2 |

  Show that your 2NF decomposition is lossless with respect to joins. To do this , produce a join expression amongst the decomposed tables, and show that its execution results in the original table above.

Thanks.


texts:
I had to delete around 600K records daily from a table. The problem I am facing is that whenever delete happens, the log database size increases to 1 GB. Even after the delete operation has been done, log database size doesn't shrink to its normal size. This is a problem as log DB size is increasing to 1 GB every day.

I cannot use truncate as table to be deleted is being referenced by other tables.


texts: SQL Server "Locate backup file" change to include diff

texts:
In SSMS, when using the GUI to select backup files to restore, the .diff are excluded (First image) and you need to change to show all files (second image).

.diff is the file extension I am using for differential backups. Extension types for SQL backups beyond the 3 primary are fairly open, someone might choose other extensions for any number of reasons. Other then not being visible in the GUI as pictured, I am not aware of any other issues with varied extension types.

I have googled around and not found anything about this.

I have looked through all the tools > Options choices, and not found a place to add '.diff' to the backup file types to display.

I am using SSMS v18.2

How can I change the values in "Locate backup file" to include other extensions?


texts: The pgAdmin 4 server could not be contacted

texts:
Whenever I install PostgreSQL, it stops working after about 6 months.  This is the second time this happened!  Last time this happened, I uninstalled and then installed version 4.29.  How can I debug the PostgreSQL client or server and get a stack trace?

Splash screen opens and never closes unless I click on it.

I can't show more of the top section of the log because the error dialog pops up.  When closing the error, the system tray icon for PostgreSQL crashes and closes everything.

After further troubleshooting, I found 8 "PostgreSQL Server" tasks, 1 pg_ctl task, 1 pgAdmin 4 Desktop Runtime in the Task Manager.  Killed them all and restarted this and restarted the service in Admin Tools > Services.  Same behavior.
C:\Program Files\PostgreSQL\12\bin\postgres.exe


texts: Migrating database from SQL Server 2005 Enterprise to SQL Server 2008 Standard

texts:
I'm attempting to migrate a database from SQL Server 2005 Enterprise to SQL Server 2008 Standard. I've run into an issue because there is a partition function defined in the database of the Enterprise edition. I know Standard editions do not support partition functions. Upgrading the version of SQL Server I'm migrating to Enterprise isn't an option.

I'm looking for a way to remove the partition function without causing any negative effects to the database currently so I can migrate it to the 2008 Standard Edition.

Any help would be greatly appreciated. Thanks!


texts:
Hope everyone is having a good week. I have what I believe to be a simple straightforward question about Postgres editors.

I have worked with 3 relational databases over the last 5 years (Teradata, MySQL & MSSQL), but encountered Postgres in the last month.

I'm an analyst, so my day to day is a lot of ad hoc querying.

In the past I have been used to the idea of simple luxuries: tabs in query editors, a quick CMD-S to save, etc.

My question is - why do these features lack in so many Postgres editors?

Postico for example has no easy save option - who does not save queries they write?

Is the way Postgres typically is used fundamentally different than other databases? I understand some are optimized for high throughput etc, but is my use (analyst) so very different than features developers would need?

Is it just because I'm relying on open source editors?
Anyone have recommendations?

Thanks
-Justin


texts:
SELECT COUNT(DISTINCT User_id) FROM Session
GROUP BY Floor_id;

SELECT COUNT(DISTINCT User_id) FROM Session
JOIN Floor ON Floor.id = Floor_id
GROUP BY Floor_id;

The second query runs 20x faster than the first query.

Floor_id is a FK to Floor.id.

Why is this the case and how should we know when we can make use of JOINs to speed up queries?

CREATE TABLE `Session` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `startTime` datetime NOT NULL,
  `endTime` datetime NOT NULL,
  `ssid` varchar(45) DEFAULT NULL,
  `AccessPoint_id` int(11) DEFAULT NULL,
  `User_id` int(11) NOT NULL,
  `Floor_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`User_id`,`AccessPoint_id`,`startTime`),
  KEY `fk_session_AccessPoint1_idx` (`AccessPoint_id`),
  KEY `fk_session_User1_idx` (`User_id`),
  KEY `time` (`startTime`,`endTime`),
  KEY `fk_Session_Floor1_idx` (`Floor_id`),
  CONSTRAINT `fk_Session_Floor1` FOREIGN KEY (`Floor_id`) REFERENCES `Floor` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_session_AccessPoint1` FOREIGN KEY (`AccessPoint_id`) REFERENCES `AccessPoint` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_session_User1` FOREIGN KEY (`User_id`) REFERENCES `User` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1099568396 DEFAULT CHARSET=utf8


texts: Tuning query to remove joins for reporting

texts:
This is the query I was sent. There is currently no data in the system as I was converting this query from teradata to SQL server.

SELECT user_id FROM user_table
LEFT OUTER JOIN
(
SELECT user_id , MIN(event_date) FROM event_table WHERE event_code='859' GROUP BY user_id
) join_859
ON user_table.user_id = join_859.user_id
LEFT OUTER JOIN
(
SELECT user_id , MIN(event_date) FROM event_table WHERE event_code='223' GROUP BY user_id
) join_223
ON user_table.user_id = join_223.user_id

Would the following query return the same results and perform better?

SELECT user_table.user_id, event_table.event_date
FROM user_table
LEFT OUTER JOIN event_table ON user_table.user_id = event_table.user_id
WHERE event_table.event_code IN (
    '223',
    '859'
)


texts:
Imagine that I want to populate the last 10 questions of stackoverflow. I have a table that keep the questions list, a table to keep the question info, a table to list question's asker.

Now imagine that I want to get the last ten questions, so I have to join some tables and get the last items by ORDER BY ... DESC LIMIT 0 , 10. This gets me the last ten questions without any problems, but I think that there may be one issue.

MySQL may be first joining all tables and then pick the last ten items, so if I have to join 1 million rows to 1 million rows, it gets really bad performance.

I want to know if MySQL does do this, and if yes, how can I go around this issue?.


texts: Oracle 12C peformance issue - waiting time increased drastically

texts:
Recently we upgraded oracled DB from 11G to 12C. During first week after upgrade we did not notice any performance issue. However, suddenly decrease in performance (not even gradual decrease) popped up. Here are the details observed. Request your guidance to debug the issue. Let me know if any details needed further

1. AWR report shows a sudden increase in wait time. Report was taken for 2 hours period

2. AWR  before problem:


texts: Performance and profiling on SELECT * FROM [table] ISNULL([column], '') = '' VS EXIST (SELECT * FROM [table] WHERE [condition])

texts:
I asked a similar question here. The answerer told me to ask about profiling and performance here on [dba.stackexchange].

I have two condition in my queries that both do the same thing. I was wondering how can I mesure the performance of both to choose the best one. I know I should do profiling, but i don't know how. I've read this and found it to be more confusing than helping. And do do not wish to profile the whole query, only this condition.

IF (SELECT * FROM [table] WHERE ISNULL([column], '')) = ''

IF EXIST (SELECT [column] FROM [table])

I expect the second way to be more performant because it does not call a function, but i am far from being an expert in SQL.

How can i profile them?

or which one is the best?


texts: INSERT/UPDATE in same query from previous SELECT with some singularities

texts:
I'm trying to perform a INSERT|UPDATE by selecting data from another DB and this is what I have so far:

INSERT INTO pdone.reps
    (veeva_rep_id,display_name,username,`first`,`last`,email)
SELECT Id, CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1)),username, firstname, lastname, email
FROM veeva.`user`
WHERE Id = "00580000003UB5VAAW"

First problem, I got this error:

  [Err] 1064 - You have an error in your SQL syntax; check the manual
  that corresponds to your MariaDB server version for the right syntax
  to use near 'from veeva.user WHERE Id = "00580000003UB5VAAW"' at
  line 1

and I'm not sure what is wrong on the query, any advice?

firstname could be JOHN or John or jOhn or any and I want to normalize as John, is UCASE(LEFT(firstname, 1) fine for this? The same apply to lastname
If firstname is JOHN and lastname is DOE then username should be John Doe with a space between them, is my CONCATENATE right?
I should insert some statics fields like one URL or just VEEVA how I can do that? Values are not present on the query shown here but is just add two more columns to the insert avatar_url and rep_type
I am planning to add ON DUPLICATE KEY UPDATE but can be possible to add a restriction based on a column? Lets said UPDATE only if now > lastSyncDate?


texts:
I have an existing PostgreSQL cluster v11.5 with streaming replication.
I want to set up high availability managed by Patroni.
I installed Patroni on both machines, I set up an etcd cluster on three others Virtuals Machines ['http://10.A.A.191:2379', 'http://10.B.B.99:2379', 'http://10.C.C.170:2379'] but Patroni starting failed with systemctl.
When I start Patroni with patroni -c /etc/patroni.yml command, I have the errors below:

2019-12-26 16:51:38,207 DEBUG: Selection next machine in cache. Available machines: ['http://10.A.A.191:2379', 'http://10.B.B.99:2379', 'http://10.C.C.170:2379']
2019-12-26 16:51:38,207 INFO: Selected new etcd server http://10.C.C.170:2379
2019-12-26 16:51:38,208 DEBUG: Converted retries value: 0 -> Retry(total=0, connect=None, read=None, redirect=0, status=None)
2019-12-26 16:51:38,208 DEBUG: Starting new HTTP connection (1): 10.C.C.170:2379
2019-12-26 16:51:38,210 DEBUG: http://10.C.C.170:2379 "GET /v2/machines HTTP/1.1" 404 19
2019-12-26 16:51:38,211 ERROR: Failed to get list of machines from http://10.C.C.170:2379/v2: EtcdException('Bad response : 404 page not found\n',)
2019-12-26 16:51:38,211 INFO: Retrying on http://10.A.A.191:2379
2019-12-26 16:51:38,211 DEBUG: Converted retries value: 0 -> Retry(total=0, connect=None, read=None, redirect=0, status=None)
2019-12-26 16:51:38,211 DEBUG: Starting new HTTP connection (1): 10.237.132.191:2379
2019-12-26 16:51:38,214 DEBUG: http://10.A.A.191:2379 "GET /v2/machines HTTP/1.1" 404 19
2019-12-26 16:51:38,214 ERROR: Failed to get list of machines from http://10.A.A.191:2379/v2: EtcdException('Bad response : 404 page not found\n',)
2019-12-26 16:51:38,214 INFO: Retrying on http://10.B.B.99:2379
2019-12-26 16:51:38,215 DEBUG: Converted retries value: 0 -> Retry(total=0, connect=None, read=None, redirect=0, status=None)
2019-12-26 16:51:38,215 DEBUG: Starting new HTTP connection (1): 10.B.B.99:2379
2019-12-26 16:51:38,217 DEBUG: http://10.B.B.99:2379 "GET /v2/machines HTTP/1.1" 404 19
2019-12-26 16:51:38,217 ERROR: Failed to get list of machines from http://10.B.B.99:2379/v2: EtcdException('Bad response : 404 page not found\n',)
2019-12-26 16:51:38,217 INFO: waiting on etcd

The problem seems to be at API level DEBUG: http://10.B.B.99:2379 "GET /v2/machines HTTP/1.1" 404 19
etcd is reacheable from PostgreSQL host:
curl -s http://10.B.B.99:2380/members |jq -r returns

[
  {
    "id": 730XXXXXXXXXXX000,
    "peerURLs": [
      "http://10.B.B.99:2380"
    ],
    "name": "etcd1",
    "clientURLs": [
      "http://0.0.0.0:2379"
    ]
  },
  {
    "id": 956XXXXXXXXXXX000,
    "peerURLs": [
      "http://10.C.C.170:2380"
    ],
    "name": "etcd3",
    "clientURLs": [
      "http://0.0.0.0:2379"
    ]
  },
  {
    "id": 1551XXXXXXXXXXX000,
    "peerURLs": [
      "http://10.A.A.191:2380"
    ],
    "name": "etcd2",
    "clientURLs": [
      "http://0.0.0.0:2379"
    ]
  }
]

etcd1 start command:

ExecStart=/usr/local/bin/etcd \
--name etcd1 \
--advertise-client-urls http://0.0.0.0:2379 \
--listen-client-urls http://0.0.0.0:2379 \
--initial-advertise-peer-urls http://10.B.B.99:2380 \
--listen-peer-urls http://0.0.0.0:2380 \
--initial-cluster etcd1=http://10.B.B.99:2380,etcd2=http://10.A.A.191:2380,etcd3=http://10.C.C.170:2380 \
--initial-cluster-token etcd-cluster-atom \
--initial-cluster-state new \
--logger zap \
--log-outputs stdout \
--log-level debug

Someone could help me please?
What is wrong?
Many thanks

Versions:
etcd Version: 3.4.0
etcdctl version: 3.4.0
API version: 3.4
patroni 1.6.3
python-etcd==0.4.5


texts: MySQL error in where clause : Unknown column

texts:
When I try to execute the query below, I receive the error message:

  Unknown column 'have_discount' in 'where clause'

My query is:

SELECT
    DISTINCT(MP.item_id), item_title, item_user_id, item_price, item_discount_price, url_slug,
    thumbnail_img, thumbnail_ext, thumbnail_width, thumbnail_height, is_free_item, default_img,
    default_img_ext, default_img_width, default_img_height, item_category_url_str,

    IF( ( DATE( NOW() ) BETWEEN item_discount_fromdate AND item_discount_todate), 1, 0 ) AS have_discount,

    item_category_id, NOW() as date_current, date_activated, MP.total_downloads,
    MP.item_sold, MP.total_views, MP.total_comments, MP.total_likes AS likes,
    MP.total_dislikes, MP.total_rating, MP.rating_count, MP.total_users_rated,
    (MP.total_rating/MP.rating_count) as rating, MP.default_img_ext , MPL.item_title,
    MPL.lang_id, MPL.item_description, MPL.item_highlight_text, MP.user_section_id, is_downloadable_item

FROM mp_item AS MP, mp_item_lang AS MPL

WHERE have_discount = 1
AND item_status = 'Ok'
AND MP.item_id = MPL.item_id
AND item_user_id = 3
AND MPL.lang_id = 2
ORDER BY date_activated DESC
LIMIT 0,12

I am trying to list items with a discount. How can I do this?


texts: SQL Server 2008-R2/2012 with Failover Cluster - minimizing downtime of program updates

texts:
I have SQL Server 2008-R2 with Failover Cluster like this. Planning to upgrade to SQL Server 2012.

Problem -

The downtime of my program updates (not Microsoft updates/patches).
Both of my cluster nodes have separate sets of connected IIS and App servers.
I need to upgrade all of them: IIS + App server + SQL.
Cannot run old IIS+App against upgraded SQL or the other way around (causes version conflicts).So the minimum downtime is the time of SQL upgrade running (can at least prepare one node's IIS+App in advance and then switch to it when ready).

Now I hear there is something clever in SQL Server 2012 to minimize the downtime even further in this kind of situations. But what exactly might it be?

(please be specific, I know they have this big AlwaysOn Availability Groups banner :-)
PS basically can change the entire approach if needed (like dropping Failover Cluster in favor of something better)


texts: ODBC Driver tracing not capturing any logparser events

texts:
I have enabled Driver Tracing using these directions. I am running SQL Server 2014, which uses the 2012 native driver.

I run the start batch file then the following LogParser command:

  "c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT SUBSTR(Field1, 0, 2) AS CountryCode, SUBSTR(TO_STRING(Field2), 0, 5) AS Zip INTO ZipCodes  FROM 'c:\Users\jdearing\Downloads\us.txt'" -i:TSV -headerrow:OFF -o:SQL -server . -database LogParserTest -cleartable -transactionRowCount:0  -ignoreMinWarns:OFF

This inserts zero rows. instead producing this error for every row:

An error occurred while uploading data to SQL table
  SQL State:     22001
  Native Error:  0
  Error Message: [Microsoft][ODBC SQL Server Driver]String data, right
  truncation

Upon stopping the trace the etl is 1024k, but has no rows according to this logparser query:

  "c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT * FROM 'SnacTrace_000001.etl'" -i:ETW

I noticed a disparity in the GUIDS in the providers.txt batch file and the output of logman query providers | findstr SQL

echo {BA798F36-2325-EC5B-ECF8-76958A2AF9B5} 0xFFFFFFFF 128 SQLNCLI > providers.txt
echo {A9377239-477A-DD22-6E21-75912A95FD08} 0xFFFFFFFF 128 SQLNCLI10 >> providers.txt
echo {2DA81B52-908E-7DB6-EF81-76856BB47C4F} 0xFFFFFFFF 128 SQLNCLI11 >> providers.txt

Versus

SQLNCLI.1                                {BA798F36-2325-EC5B-ECF8-76958A2AF9B5}
SQLSERVER.09                             {AB6D5EEB-0132-74AB-C5F5-B23E1644DADA}
SQLSERVER.10                             {48D59D84-105B-00FA-6B49-03462F696737}
SQLSERVER.11                             {B2A28C42-A7C2-1563-97CC-3BE49FDA19F9}

So I updated the batch file to:

echo {BA798F36-2325-EC5B-ECF8-76958A2AF9B5} 0xFFFFFFFF 128 SQLNCLI > providers.txt
echo {48D59D84-105B-00FA-6B49-03462F696737} 0xFFFFFFFF 128 SQLNCLI10 >> providers.txt
echo {B2A28C42-A7C2-1563-97CC-3BE49FDA19F9} 0xFFFFFFFF 128 SQLNCLI11 >> providers.txt

Still no luck. What else can I try? Is logparser not using the native client?


texts: how to update 'update trigger fired record's column?

texts:
I'm new on this task.

I tried to update when I/F table insert after trigger. but it wasn't work..

There is my trigger source like..

create trigger T_ATABLE_I
after insert on ATABLE for each row
...
-- all sql queries done.
:new.IF_FLAG := 'Y'; -- it is I/F success flag on ATABLE.
...

I heard after trigger can't update :new..

But I must update this column when whole task just done.

conditions

'ATABLE' table don't have ID.
I must use 'AFTER' trigger because of some business role issue.

How could it be possible?


texts: User created automatically because of AD Group?

texts:
I have a Development SQL Server that I'm managing with Active Directory Groups, sometimes when I do my checks on the server I find that some users were "automatically" added to SQL Server Database with their schema as default...

I noticed this because one user created a view and it was created with on his own schema...

Is there any configuration parameter that is allowing this? How can this be avoided?

Thanks,
Jimm


texts: Survey database design: associate an answer to a user

texts:
I'm doing the conceptual model for a survey database.

The goal is store the answers given by users (it's going to be an Android app).

I have three entities: user, question and option.

A question will have one or more options (for example: How many employees do you have? 1-40, 40-1000, +1000).

Options will have a text (1-40) and a value (the value selected by user).

User will select one (or more) of these options.

My conceptual design is:

I don't know how to associate an answer with an user.

How can I represent that relation?
Do I have another entity to represent option value?

This model will store questions and pre-made answers (offered answers) and allows them to be re-used in different surveys.

I have to represent question like this one:

This question is related to this one: Survey database design: first version. Are there errors?


texts:
Is there are way to trace if DBCC commands are being run against a SQL Server database?

Specifically I am looking to see if the following 2 commands have been run at any point?

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

The reason is that the cache keeps emptying and I am pretty sure there isn't a great amount of memory pressure on the server.

I would really like to  rule out the fact that this is being done by someone and really is a genuine issue I need to investigate and/or add more memory to the server.

Thanks in advance
David


texts: MYSQL Replication _ Master port is 3306 and slave port is 3307

texts:
I have set master - slave replication.

Master mysql port = 3306
master server id = 1

Slave mysql port = 3307
slave server id =2

log-bin is enabled on master, and also replication user is
created on master with replication rights.

change master to
master_host = 'masterIP',
master_port=3306,
master_user = 'username',
master_password= 'password',
master_log_file = 'mysql-bin.000037',
master_log_pos = 89265280;

But when I run

Start Slave;

It shows error as below.

Error Code: 1794. Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave.

Can anyone help ?


texts: How to get particular object from jsonb array in PostgreSQL?

texts:
I have a field called 'user' that holds a json array that roughly looks like this:

"user":

[{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}]

Now I want a query like:

select count from tablename where id = "1"

I'm not able to get the particular field count from an array of json objects in PostgreSQL 9.4.


texts:
Error messages constantly showing up in log files and I don't know what to do with them:

2015-11-26 11:08:05 MSK ERROR:  duplicate key value violates unique constraint "repl_stat"
2015-11-26 11:08:05 MSK DETAIL:  Key (client_id)=(2) already exists.
2015-11-26 11:08:05 MSK STATEMENT:  insert into replication_statistics(id, client_id,client_ip,connect) values('{CB2FAAC1-8E54-4772-9F78-B45775447FEB}', 2,  '192.168.0.12', '20151126 11:08:05')
2015-11-26 11:08:12 MSK ERROR:  duplicate key value violates unique constraint "repl_stat"
2015-11-26 11:08:12 MSK DETAIL:  Key (client_id)=(2) already exists.
2015-11-26 11:08:12 MSK STATEMENT:  insert into replication_statistics(id, client_id,client_ip,connect) values('{9AF4106E-6FCD-4B8F-9DD3-7DCD3B707557}', 2,  '192.168.0.12', '20151126 11:08:12')
2015-11-26 11:08:20 MSK ERROR:  duplicate key value violates unique constraint "repl_stat"
2015-11-26 11:08:20 MSK DETAIL:  Key (client_id)=(2) already exists.
2015-11-26 11:08:20 MSK STATEMENT:  insert into replication_statistics(id, client_id,client_ip,connect) values('{C9B76DE6-C977-4E86-97E0-1E49E4189F9A}', 2,  '192.168.0.12', '20151126 11:08:20')

They seem to show up every 8 seconds. I cannot find replication_statistics table. How can I possibly get rid of them?
Firstly this error was 3 days ago when I set up Continuous Archiving. Archiving worked well. Now I turned it off, but error messages didn't go away.
I found pg_stat_replication view, but it has 0 rows.

PostgreSQL version 9.4.2 x64, OS Windows Server 2012 R2


texts: join value in a RETURNING which is not inserted

texts:
I'm doing an insert from a query which joins two tables, then I want the new ID from the inserted row, and a field from the original row which is not involved in the insert. Is it possible? I get a "column doesn't exist" error.

INSERT INTO new_table (x,y)
select A.x,B.y
from A
  join B on A.w = B.z
RETURNING id,B.z;

The new_table has a unique constraint on (x,y).

The new_id and B.z are needed to insert into a second table.


texts: MySQL large databases, unique index effect on efficiency?

texts:
I'm working on a table which holds around 300k records. I'm using a 3 columns unique index to prevent duplicate records. For some reason the database is taking long to insert new rows.

I have a few questions regarding UNIQUE indexes and efficiency:

Will a UNIQUE index make a table much slower?
Would it be useful to store old rows in another table and reduce the table to around 1k records?
And most importantly is there any way to know how long my db takes to insert a row? In phpMyAdmin I can see the "time to process query" but only on SELECT queries


texts: MySQL is not releasing memory after memory tables are dropped

texts:
After creating several tables of memory engine and dropping them, mysqld is not releasing all the corresponding memory.

Below are the exact steps to reproduce the problem. Memory usage is observed using htop (RES column).

Disable swap

sudo swapoff -a

Launch mysql with memory limit of 4GB

docker run -d -m 4g --shm-size 4g --name mysql_test \
    -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:5.7.25

docker exec -it mysql_test bash

# test to allocate 3G memory in /dev/shm
dd if=/dev/zero of=/dev/shm/dummy bs=1M count=3K
rm /dev/shm/dummy

# create a named pipe for loading data
mkfifo /var/lib/mysql-files/tmp.pipe

# launch mysql client
mysql -uroot

Create database

create database if not exists mem;
use mem;

-- allow memory table of max size 4GB for this session
set max_heap_table_size = 1024 * 1024 * 1024 * 4;

  mysqld memory usage: 193M

Create table and load data

In mysql shell:

create temporary table memory_tbl ( idx varchar(1000) ) engine=memory;
load data infile '/var/lib/mysql-files/tmp.pipe' into table memory_tbl (idx);

In another terminal:

docker exec -it mysql_test sh -c 'yes 0 | head -1048576 > /var/lib/mysql-files/tmp.pipe'

  mysqld memory usage: 1210M

Back to mysql shell to duplicate the table:

create temporary table memory_tbl2 like memory_tbl;
insert into memory_tbl2 select * from memory_tbl;

  mysqld memory usage: 2227M

Duplicate the table again:

create temporary table memory_tbl3 like memory_tbl;
insert into memory_tbl3 select * from memory_tbl;

  mysqld memory usage: 3243M

Drop tables

drop table memory_tbl;

  mysqld memory usage: 3244M

drop table memory_tbl2;

  mysqld memory usage: 3244M

drop table memory_tbl3;

  mysqld memory usage: 2302M

Try allocate memory

In another shell:

docker exec -it mysql_test bash

# allocate 1.5G memory
dd if=/dev/zero of=/dev/shm/dummy bs=1M count=1536
rm /dev/shm/dummy

# allocate 2G memory
dd if=/dev/zero of=/dev/shm/dummy bs=1M count=2K
# error occurs
# dd: error writing '/dev/shm/dummy': Cannot allocate memory
rm /dev/shm/dummy

I wonder if this is a bug of MySQL server. Otherwise, would there any way to ask MySQL to release the memory without restarting mysqld?

Testing environment:

Ubuntu 18.04.1 LTS
Kernel 4.15.0-34-generic
Docker 18.06.1-ce
mysql-5.7.25
glibc 2.24


texts:
How could SP be modified the way it will execute update only in case any updated value is different?

Assume we have a table

CREATE TABLE students
(
    StudentId INT PRIMARY KEY,
    Name nvarchar(255) NULL
 )

|----|------|
| Id | Name |
|----|------|
| 1  | Jane |
|----|------|
| 2  | John |
|----|------|
| 3  | NULL |
|----|------|

and the single student update stored procedure

[dbo].[student_update] (@StudentId int, @Name nvarchar(255)) AS
update [dbo].[students]
set [Name] = @Name
where [StudentId] = @StudentId

So again, how this could SP be modified the way it will execute update only in case @Name does not equal to [Name]?

Please take into account that [Name] could be NULL

DECLARE @areFieldsDifferent = CASE WHEN (
([NAME] is null and  @Name is not null) OR
([NAME] is not null and  @Name is null) OR
([NAME] <> @Name)
)
then 1 ELSE 0 END;

But how could be compared @Name with [Name] from Select * From Students where StudentId = @StudentId?
And how to use it to not call update statement?


texts:
I'm working at the pgconfig.org[1] tool. PgConfig it's a tool for suggest a tuning configuration for a PostgreSQL environment.

I'm finding some disk tuning documentations to implement a suggestion for operating system tuning. Any tips which parameters are important?

References:

http://pgconfig.org


texts: How can I generate object DDL, like the CLI "show" command does?

texts:
When we run the show command, e.g. show table db.tablename; show view db.viewname, it lists the object definition. I believe it will query the dictionary tables and rebuild .

Could you help us to understand how it queries the internal tables?


texts: Sync many client database to one central database

texts:
We have client application which can be installed on local machine where users can work offline and create projects in it. This application is already developed.

Now we need to create Admin application where Admin can view all the Projects created by the client application. So basically we need to sync all the client database with one central database.

I considered using Microsoft Sync Framework but the challenge is.

Suppose I have table MasterProject in which ProjectId is Primary key and Identity.
user1 creates two project so the Project ids will be 1 & 2.
user2 creates 2 project its Project ids will be 1 & 2.

Now I need to sync both these data to Central database it won't allow since project id is Primary key at central database. If I remove primary key at central database the Sync Framework does not support.
I have also tried to create composite primary key on ProjectId and UserId and tried to trick the sync framework by matching the schema of tables but it does not work.

I don't want to change the database of client application.
Is there any way to achieve this?


texts: Allow transparently altering View's underlying table

texts:
How to create a view that does not prevent altering its underlying table? There's a certain reason why I can't drop and re-create the view every time I need to change a column type or add a new column. This is because the alter script is generated by our ORM when we change the complex type definition and there's a stable deployment procedure I don't want to change.

-- test initialization
drop view if exists test_dep_trk_v;
drop table if exists test_dep_trk;
create table test_dep_trk ( a varchar(10) );

-- the view MUST at all times contain all the columns of the table
create view test_dep_trk_v as select * from test_dep_trk;

-- this statement cannot be changed
alter table test_dep_trk alter column a type varchar(11);

.

ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view test_dep_trk_v depends on column "a"
SQL state: 0A000


texts: How to perform backup on quite active 500G+ MariaDB Galera Cluster?

texts:
what would be your preferred method to perform backup on quite active 500G+ MariaDB Galera Cluster?

Using mariabackup/innobackupex (fork of Percona Xtrabackup) with throttle. You must think about FLUSH TABLES WITH READ LOCK which can cause problems to whole cluster if lock lasts to long. When using Percona XtraDB Cluster this isn't big problem because you have LOCK TABLES FOR BACKUP.
Put node in Desync state, perform backup, and turn off Desync mode after backup finishes. Here potential problem could be if backup takes too long, and it will take some time for a node to catch up.
Create Slave and perform backups on Slave. You have to monitor that slave is in sync with master. Maybe perform occasional consistency checks?
Use mysqldump. I think taking backup of 500G+ database using mysqldump would take too long.

Interested to hear your opinions.

Regards,
Marko


texts:
Unfortunately I have recently lost my VPS and the provider restore it after 1 week, then the only things that I had was the raw files on my server which were accessible in recovery mode.
so by moving those files from the server to my local machine and the other new server which I have bought, I need to recover those IBD files to the MySQL.
What I have to do with these *.IBD files in order to have my data on a live mysql service again?


texts: Matching 3 columns in row - if one column is null then exclude it from comparison

texts:
A   B   C    Equal
1   1   1    Y
2   2   NULL N

Hi, I have 3 columns which I need to compare : Columns A,B,C.
If these 3 columns are the same then column 'Equal' = 'Y'

But, if a column is null, then it must be excluded from the comparison, e.g. in the example Row 2 has a null, but the remaining columns are all the same, so the Equal should be yes - because all non-null values are equal

http://www.sqlfiddle.com/#!3/dedd9/2

I thought I could use isnull(C,A) = A but it does not work. Could anyone help please

 declare @t table
 (
 A decimal,B decimal, C decimal
 )

 insert Into @t(A,B,C) values (1,1,1)
 insert Into @t(A,B) values (2,2)

 select A,B,C,'Equal' = case when ISNULL(A,B) = B and ISNULL(B,C) = C and ISNULL(C,A) = A then 'Y' else '' end
 from @t