texts: Foreign key not able to get one to many working

texts:
I want to have a one to many relation with player points but I need it in such a way that entity framework sees it as a collection so i can add to it. I mean that one player can have multiple points requests.

Player Table

Player Points Table

CREATE TABLE [dbo].[player](
    [id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_player_id]  DEFAULT (newid()),
    [Name] [varchar](150) NULL,
    [player_id] [varchar](50) NULL,
    [description] [varchar](max) NULL,
    [address] [varchar](150) NULL,
    [gender] [varchar](30) NULL,
    [town] [varchar](150) NULL,
    [zipcode] [varchar](50) NULL,
    [telephone] [varchar](255) NULL,
    [email] [varchar](255) NULL,
    [active] [bit] NULL,
    [createdDate] [date] NULL,
    [author] [varchar](255) NULL,
    [siteid] [uniqueidentifier] NULL,
    [regEmailSent] [bit] NULL,
    [regEmailActivated] [bit] NULL,
    [teamId] [uniqueidentifier] NULL,
    [isDeleted] [bit] NULL,
    [deletedDate] [date] NULL,
    [modifiedDate] [date] NULL,
    [modifiedBy] [nvarchar](250) NULL,
    [isArchived] [bit] NULL,
    [districtId] [uniqueidentifier] NULL,
    [dob] [date] NULL,
 CONSTRAINT [PK_player] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[player]  WITH CHECK ADD  CONSTRAINT [FK_player_team] FOREIGN KEY([teamId])
REFERENCES [dbo].[team] ([id])
GO

ALTER TABLE [dbo].[player] CHECK CONSTRAINT [FK_player_team]
GO

How do i add a foreign so that A player can have many point rows the field that im using to link them with is playerId.
And My Player Points Table

CREATE TABLE [dbo].[player_points](
    [id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_player_points_id]  DEFAULT (newid()),
    [playerId] [uniqueidentifier] NOT NULL,
    [playerName] [nvarchar](255) NULL,
    [teamId] [uniqueidentifier] NULL,
    [points] [int] NULL,
    [pointsToRedeem] [int] NULL,
    [dateToBeAwarded] [date] NULL,
    [approvedBy] [varchar](50) NULL,
    [approved] [bit] NULL,
    [approvedDate] [date] NULL,
    [siteId] [uniqueidentifier] NULL,
    [sentApproveEmail] [bit] NULL,
    [isActivated] [bit] NULL,
    [authUserName] [nvarchar](50) NULL,
    [authCreatedDate] [date] NULL,
    [modifiedDate] [date] NULL,
    [modifiedBy] [nvarchar](255) NULL,
    [playerNumber] [nvarchar](50) NULL,
    [isProcessed] [bit] NULL,
    [sentEmailToAdmin] [bit] NULL,
    [sentEmailToUser] [bit] NULL,
 CONSTRAINT [PK_player_points] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


texts: Can maintenance plans be used in SQL Server multiserver (Master-Target servers) administration?

texts:
Since I failed to configure SQL Server 2012 Multiserver (Master and Target Servers) administration, I am eager to understand whether it (setting Master and Target servers) would permit to send from Master to Target servers also Maintenance Plans used in SQL Server Agent jobs as well as configured there mail notifications as well as enabling in Target servers Database Mail?

And how can I configure-check it except of using wizards?


texts: MySQL single table static and dynamic pivot


texts: Finding records not present across 3 tables - Sqlite3

texts:
From the diagram provided, what I'm trying to achieve is to get all the Users of a particular workspace that are NOT associated with a task within user_task.

user_workspace table contains two FKs: user_id (from User) and workspace_id (from Workspace)

user_task table contains two FKs: user_id (From user) and task_id (from Task)

Here's the SQL I'm tinkering with to give a sense of what I'm trying to achieve:

SELECT * FROM User INNER JOIN user_workspace ON (user_workspace.user_id = User._id)
LEFT OUTER JOIN user_task ON (user_task.task_id = Task._id)
WHERE (user_workspace.workspace_id= ?)
AND (user_task.task_id= ? AND user_task.user_id IS NULL)
ORDER BY User.name ASC

I think I'm close but I can't seem to get the exact result I want. Thanks for reading.


texts: Query taking long on table with low record count

texts:
I have an Oracle flat table that grew to just below 800MB. The table was truncated and new records are being inserted. Currently there are <40 records in the table but a select * takes >30secs.

Could this be because the table is still so big (~800MB) that it is taking so long?

There are no indexes.

EDIT

I found out from the person that cleared the table that they did a delete instead of a truncate


texts: Update a unique column with an unknown number of values

texts:
I have a table with three columns, ID, a foreign key field and a text field (unique). The user should be able to update all text fields where the FK has a certain value.

The problem is that the user can enter as many values and in whatever order he likes. If he enters less than there are in the database all of them should be removed and replaced with the new ones, for example:

The names are already in the database: John, Amy and Joe.

The user wants to update these names with the following: Amy and Jessica. Which leads to an error (because he is trying to update the value John with Amy when Amy already exists) and a problem (if it would work): the value Joe would not be deleted.

Because of this I see myself forced to have two statements: DELETE and then INSERT. The problem with this is that if one of the insert statements fails, I will lose the data that was in the database initially (John, Amy and Joe).

I've tried setting up a transaction to solve the problem so that I could rollback if something failed. However, this leads me back to the original problem: Integrity constraint violation: 1062 Duplicate entry 'John' for key 'Name'.

The names correspond to items whose characteristics are stored in a separate table, since each item can have various names.

What does my database design need to look like for this problem not to occur?

I've asked this question on StackOverflow already, and it was suggested I should revisit my database design. Here is the original question (with code): https://stackoverflow.com/questions/31727353/update-a-unique-column-with-an-unknown-number-of-terms

CREATE TABLE Items
(
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
Characteristics varchar(255)
)

CREATE TABLE Names
(
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
ID_ITEM int,
Name varchar(255) UNIQUE
)


texts: ntwdblib.dll : How to configure it to connect over tcpip ? (SQL Server, legacy)

texts:
We have an old app that uses ntwdblib.dll to connect to sql server.

It works, but it appears to connect over named pipes.

Due to moving the sql server to a data center, we need to change this so it connects over tcpip.

Currently, we connect by connecting to:  < server name>\< instance name>

We tried putting just the server name, and < server name>,1433  and that did not work.

How do we specify/configure for this lib to use tcpip?


texts: Cannot connect to . A severe error occurred on the current command

texts:
Recently, and on more than one occasion now, we have attempted to connect from one of our SQL Server instances to another using SSMS only to be greeted with the following error:

A severe error occurred on the current command. The results, if any, should be discarded.

The following error occurs when we attempt to connect using SSMS locally:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

Connections via the Dedicated Administrative Connection (DAC) result in the same errors. SQLCMD reveals the following error:

SQL Server Configuration Manager reports that both the SQL Server service and the SQL Server Agent service are running.

The SQL Server error log stops at the same time as entries in the Event Viewer begin to be logged, reporting connection issues for SQL Server Agent jobs.

SQL Server Scheduled Job 'sp_WhoIsActive' (0x868EBDEC06F71344AC717E0C6A333D13) - Status: Failed - Invoked on: 2018-09-18 10:38:00 - Message: The job failed.  Unable to determine if the owner (sa) of job sp_WhoIsActive has server access (reason: Unable to connect to server - check SQL Server and SQL Server Agent errorlogs).

All of our SQL Server services have been configured to run under Group Managed Service Accounts (gMSA's).

The only solution so far has been to restart the servers, but clearly this is not a satisfactory solution, especially since the issue has now occurred more than once.

The only thing we have to go on is that there are numerous occurrences for the following error message in the SQL Server Error log before the connection issues occur.

2018-09-17 13:31:19.20 Logon       Login failed for user '[domain][gMSA account for SQL Agent]$'. Reason: Failed to open the explicitly specified database '[database_name]'. [CLIENT: [local machine]]
2018-09-17 13:32:03.63 Logon       Error: 18456, Severity: 14, State: 38.

Is it possible that the maximum number of connections has been exceeded as a result of these login issues? If so, why is it still not possible to connect via the DAC?

Is anyone please able to advise?

Further details:

Microsoft SQL Server 2016 (SP1-CU8) (KB4077064) - 13.0.4474.0 (X64)   Feb 24 2018 13:53:17   Copyright (c) Microsoft Corporation  Web Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor)
SQL Server Management Studio v17.8.1
Maximum number of user connections is set to unlimited (0).
Shared Memory, TCP/IP and Named Pipes are all enabled.


texts:
I have a 2GB mysql backup file that has several databases in it and would like to change the name for one of the databases. What approaches  can I use to tackle this issue ? I have used sed but it ends up making changes where it should not.

sed 's/CREATE DATABASE \/*!32312 IF NOT EXISTS*\/ `mydb`/`mydb2`/g' all_databases.sql > out.sql


texts: How to set IP segment and connection string with IPv6?

texts:
For this PostgreSQL configuration example:

https://repmgr.org/docs/4.4/quickstart-authentication.html

    local   replication   repmgr                              trust
    host    replication   repmgr      127.0.0.1/32            trust
    host    replication   repmgr      192.168.1.0/24          trust

    local   repmgr        repmgr                              trust
    host    repmgr        repmgr      127.0.0.1/32            trust
    host    repmgr        repmgr      192.168.1.0/24          trust

I have two questions.

Question 1

This is how a netmask looks for IPv4: 192.168.1.0/24.

How to do with an IPv6 IP address like 230b:c010:103:5858:a6a3:3:0:1?

Question 2

If use JDBC to connect to a PostgreSQL server, I can use a connection URL containing 192.168.1.11:5432. How to do it with IPv6? Is it like 230b:c010:103:5858:a6a3:3:0:1:5432?


texts: Joining 2 queries to match Months side by side

texts:
I have 2 SQL statements that I want to join up. The first gets the actual data per month for Turnover accounts. The second query gets budgets (if any) for the same Turnover accounts.

Query 1 Actuals

/* Turnover 1 */
SELECT * FROM
(
SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', T0.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount'
FROM OACT T0
LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
LEFT JOIN OBGT T2 ON T0.[AcctCode] = T2.[AcctCode]
WHERE T0.GroupMask = 4 AND T0.Levels = 5
AND (T1.RefDate BETWEEN '20150101' AND '20151231')
GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate) AS q
PIVOT
(
    SUM(Amount)
    FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

Sample data

Query 2 Budgets

/* Turnover 1 */
SELECT * FROM
(
SELECT T0.AcctCode AS 'AcctCode', T3.AcctName AS 'Description', T3.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0) THEN T1.DebLTotal ELSE T1.CredLTotal END AS BudgetAmt, T1.Line_ID AS 'LineID'
FROM OBGT T0 INNER JOIN BGT1 T1 ON T0.[AbsId] = T1.[BudgId]
INNER JOIN OACT T3 ON T0.[AcctCode] = T3.[AcctCode]
WHERE T3.GroupMask = 4) AS q
PIVOT
(
    SUM(BudgetAmt)
    FOR LineID IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11])
) AS P

Sample Data

For the 2nd query, 0 represents Jan, 1 represents Feb ... and 11 represents December.

How do I combine the 2 queries such that I can get data such as below?

Not a duplicate as this contains a query to retrieve budgets.


texts:
I've got a postgresql+postgis database filled with GPS data:

CREATE TABLE "my_table" (
    "id" serial NOT NULL PRIMARY KEY,
    "gps_id" text NOT NULL,
    "lat" numeric NOT NULL,
    "lon" numeric NOT NULL,
    "speed" numeric NOT NULL,
    "geom" geometry,
    "timestamp" timestamp with time zone NOT NULL,
);

My data comes in regularly but there are some gaps. I'd like to find the average speed and build up a linestring from the data grouped by "gps_id" AND by gaps greater than X hours:

SELECT
   gps_id,
   avg(speed) as avg_speed,
   ST_MakeLine(my_table.geom ORDER BY timestamp) as line
FROM my_table
GROUP BY gps_id

It's pretty easy to do find the basic aggregate but grouping by time-gap has me stumped. I've tried to group by time buckets (using date_trunc) and then counting the number of gps messages within the time bucket and tossing if < X but that's not really the behaviour I want. I'd like to be able to get something like:

Data Output:

gps_id,       avg_speed,     line,        gap
ABC,          12.01          01002...     0
ABC,          20,20          01002...     1
DEF,           0,2           01002...     0

A bonus would be to find the start and end times of each group so that I could eyeball where/when the gaps are happening.

Any ideas?


texts: Effect of changing data types with row compression enabled

texts:
The company I work for has a couple of SQL Server databases with tables containing +- 500.000.000 rows. We are running Enterprise editions of SQL Server 2008R2 and 2014.

Big data types

When I look at the used data types in the largest table I see a lot of BIGINT columns. Examining the data in those columns with a script from Thomas Larock and scripting the MIN() and MAX() values myself I concluded that data in those BIGINT columns can easily be fitted in INT or even SMALLINT/TINYINT columns. (I'm aware that some columns could need the range of BIGINT in the future,so I'm not blindly changing all the data types without talking to developers first)

When comparing the possible savings when changing the datatypes it seems the table could be half the size it is currently (not even considering indexes and other tables). These numbers are without any data compression.

ROW Compression

On the large table ROW Compression is enabled. I'm wondering what the actual impact of 'downsizing' the columns datatypes might be, keeping in mind that ROW compression is using only the bytes that are needed. For example, if a value can be stored in 1 byte, storage will take only 1 byte.

Actual question

Would it help to downsize data types, so the ROW compression uses less resources? Or is it save to say 'because ROW compression is enabled there is no difference between BIGINT, INT or SMALLINT data types'?


texts: Update primary SQL Server in mirrored environment using transaction logs from old environment

texts:
I'm currently migrating a large (~40gb) database to a new environment. The new environment is a HA/mirrored set up with primary, mirror and witness nodes.

I'm backing up the database in the old environment, restoring to primary & mirror, and turning on mirroring. During this time the old environment is still active, and data is changing. When we are ready to go live with the new environment I plan to take another transaction log from the old environment and restore that to the new primary server. Is that possible? Will this be successfully synchronised to the mirror?


texts: How to formulate equality predicate on POINT column in PostgreSQL?

texts:
How to select from a table with an equality predicate on point column?
To be clear: it's a native Postgres point data type, not geometry(point) from PostGIS.

select a.* from indsolv.address As a where a.location = '93.2321, 21.0321';

Getting this error for the above query:

ERROR:  operator does not exist: point = unknown
LINE 1: ...ct a.* from indsolv.address As a where a.location = '93.2321...
                                                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
SQL state: 42883

Also tried:

select a.* from indsolv.address As a where a.location = point('93.2321,21.0321');

Still getting an error.


texts: Data scrambling for the development data

texts:
I have a lot of sensitive data in production like SSN, name, email, and phone number.

I have function to scramble this data using a random value hashmap technique. But in the case of SSN (social security number), we have different tables that contain the same SSN. This causes my data to be messed up because I need to keep keep the same scrambled value for every instance of the same SSN.

Currently, if I have a SSN 12345678 in both the person table and the elige table, then my function's logic causes the tables to have different SSN values after scrambling (i.e. 854612397 for person, 963258741 for elige table).

Desired output should be that the scrambled SSN is the same in both tables.


texts: Instead of C:\SQLDBA\ServerName\Instance rather C:\SQLDBA\ServerName_Instance

texts:
I am trying to create a folder based on the servername\instance to backup up to, i want the folder to create as servername_instance. Could you please point out where i am going wrong as it creates as servername\instance

IF OBJECT_ID('TempPSCreateFolder') IS NULL
BEGIN
CREATE TABLE [TempPSCreateFolder]
(QueryText nvarchar(max))
END

INSERT INTO [TempPSCreateFolder]

SELECT '$fso = new-object -ComObject scripting.filesystemobject

$fso.CreateFolder("C:\'+ STUFF((SELECT ',' + SUBSTRING(@@SERVERNAME, 1, CASE
WHEN CHARINDEX(',', @@SERVERNAME) > 0 THEN
CHARINDEX(',', @@ServerName)-1
                       ELSE LEN(@@ServerName)
                    END)

FOR XML PATH('')), 1, 1, '')+'")'

EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE

EXEC xp_cmdshell 'bcp master.dbo.TempPSCreateFolder out
"C:\SQLDBA\CreateFolder.ps1" -c -T -SServer\Instance'

EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE

DROP TABLE TempPSCreateFolder


texts: How do I move or redistribute FILESTREAM files between containers?

texts:
We have a FILESTREAM container with a few million files, and we believe it's the cause of our performance issues we're experiencing (abundant timeouts).

According to this blog about FILESTREAM best practices, each container shouldn't reach more than 300,000 files.

According to the accepted answer here, there is no way to accomplish it except by recreating the table to a new FILESTREAM location.

Is this true for my situation?
And if so, what is the recommended approach to ensuring that enough containers are created automatically to handle this without manual intervention every 300,000 files?

The structure of the table follows:

CREATE TABLE [dbo].[Documents](
    [ContentPath] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [FileContent] [varbinary](max) FILESTREAM  NULL,
 CONSTRAINT [UQ_IX_Documents_ContentPath] UNIQUE NONCLUSTERED
(
    [ContentPath] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [FSFileGroup_1]

We're using SQL Server 2012. Not Enterprise unfortunately (which we only recently realized supports multiple containers per filegroup).

Although we never do record updates, we do a lot of writes, probably as many as we do reads. The pattern is: just one at a time, reads by ContentPath, and writes in no specific container or order.


texts:
Is there any scenario where it is the best choice to make the Oracle installation/service account (oracle) an LDAP/AD account versus a local-only account?

More generally, is creating the Oracle account locally the best practice? I don't see this stated explicitly in their installation documentation.


texts: Does schema binding views affect performance?

texts: Does schemabinding views affect performance in SQL Server or is just to prevent accidental changes?


texts: Is a table without a primary key normalized?

texts:
In a lecture, my lecturer showed us a table without a primary key. Upon questioning, he said that in 3NF when you remove transitive dependencies it's ok to then have a table without a primary key.

However, no primary key implies there's no functional dependencies - but 3NF is the removal of transitive dependencies, and I was taught that each table needs to have a primary key for normalization as it's all about functional dependencies.

I know it's fully possible to create a table without a primary key, but is that database considered normalized if that table exists?

I should add, the table doesn't have any “unique key”, no primary, no composite, no foreign.

The table shown has three attributes with none of them labeled as primary or unique. I asked if it was a mistake and he said it's fine to not have one. I questioned the remark as none of the information in the table can be uniquely identified and he claimed it's ok to be like this. This goes against what I was taught about normalisation.


texts: Using RowLock to enforce Once-And-Only-Once

texts:
I have a Web Service that can fire off a chain of events for an order.  It does this when it gets a message telling it to (max of about 2 per second).

The problem I have is that I have more than one instance of the Web Service, and sometimes different parts of the order can reach each instance at the exact same time.  This causes the chain of events to fire off more than once for the order (bad).

So, I was thinking, I could make a table that is just the ID of the order and a flag saying if the "chain of events" has been fired off yet.

I could then start a transaction in my code.  Something like this:

mySqlConnection.BeginTransaction(); // C# Code

I could select the row for the order in question like this:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT  OrderId, IsChainFired
FROM    OrderedChainFlag WITH ( ROWLOCK, XLOCK, HOLDLOCK )
WHERE   OrderId = 123456

Then, if IsChainFired is false, fire off the "Chain of Events".  I would then run:

UPDATE  OrderedChainFlag
SET     IsChainFired = 1
WHERE   OrderId = 123456

and then run (even if IsChainFired is true):

myConnection.CommitTransaction(); // C# Code

This would prevent more than one instance of the service from reading that the chain is free to be run at the same time.

My worry (and my question) is lock escalation.  If this stays on row locks then it will be a fantastic solution to my problem.  But if the locks escalate to page or table, then I will have just created a bottleneck in my system.

So, is there anything I can do to ensure that this stays at the row lock level?  (or is it even a good idea to use the database locking system as a semaphore like this?)

NOTE:
OrderId would be the Primary Key, Clustered Index and Partitioning Index of the table.


texts: How to delete row from table later than date?

texts:
I have a simple table:

MyTable:
-Id int primary key
-Name varchar(MAX)
-Deleted bit

Now i want delete rows created later that 15.12.2014. But how can you see i don't have a any date columns in my table.
It's possible to do in MS SQL SERVER 2008 R2?


texts: Locally connecting to SQL Server 2012 using Windows Authentication

texts: I have a SQL Server 2012 cluster which I've updated with SP2, and since the update I can't locally connect to the server using Windows Authentication. Yet, I can connect using same credentials from any other computer. SQL Authentication works fine, locally and remote. Thanks for your help.


texts: Best approach to maintain many dump for a single structure

texts:
I have many database equal to each other with differences only in the data. I want to maintain a dump for each database for use in a installation process.

is recommended maintain it as a single file or split into many files?


texts:
According to https://stackoverflow.com/questions/4332982/do-clustered-indexes-have-to-be-unique

The answer is yes. In the sense that SQL Server will make it unique by appending 4-bytes to key. This makes sense given that clustered index is used for data addressing purposes (Correct?).

According to my research, it seems that SQL Server requires non-clustered indexes to be unique as well. And when non-unique column is used, SQL Server appends a 4-byte "uniquefier" value to make it unique. Is that correct?

Why does SQL Server require non-clustered index to use unique keys?


texts: To reduce recovery time (and save space): Is it possible to ignore some PostgreSQL 13 tables (or schemas) when backing up using Barman?

texts:
We have some large staging/scratch tables in one of our PostgreSQL databases. The data in them can be automatically regenerated from other databases after recovery of the database, and aren't business critical. Is there a way to ignore those tables when backing up using Barman? They are all in one schema.
This is to reduce recovery time and save space.
Maybe we can use some sort of persistent temporary tables? Tablespaces?
We have the tables because it is convenient when we do ETL. But aren't used by any applications or clients.
Are there other alternatives to do something similar?

Some documentation I've been reading through:
https://www.pgbarman.org/index.html
http://docs.pgbarman.org/release/2.12/


texts: When is the right time to use MariaDB instead of MySQL, and Why?

texts:
What we earn and what we lost with this migration?

What should I expect as drawbacks after the migration?

Is it really unnecessary to change the applications in any situation?


texts: MySQL Workbench model/source synchronize issue

texts:
I've been looking all over for any description of an issue like this and I haven't found anything so far.

I am trying to update my MySQL database using MySQL Workbench (5.2.40 CE). I am using the Synchronize Model option just as I have always done before to update the database with the changes I've made to the model. Main thing to note here is that the issue I'm going to describe happens even without any changes being made to the model.

The problem is when it gets to the point where it shows "Model and Database Differences", the fields which all normally line up between Model and Source are totally off.
I get everything doubled with the first set showing the Model names of my Schema and Tables with the Sources next to all of them showing N/A. Then below that it has all of the Source values filled in while the Model values all say N/A.

If I run the update as it is my database does get updated, but it also completely wipes out all the information on the database because it is dropping all the tables and re-entering them. Here is the picture of what the page looks like.

I have no idea if this is some kind of setting or bug. But on my test database after totally getting wiped out and trying it again, the Differences page shows up normally with everything 1:1 like it should, and it updated just fine.

Any clues on this would be appreciated.


texts: Left Join of a Subselect query very slow

texts:
I'm using mysql 5.5. and I have three tables:

newsletter_newsitem: contains a row per news item
newsletter_newsitem_areas: a many-to-many table that relates a news item to a region (a news item can have many regions)
newsletter_geoarea: contains a row per region.

I want to get all the countries a news item have so I crafted the following query which take less than a second to execute:

SELECT nlni_areas.newsitem_id,
       ifnull(GROUP_CONCAT(nlga.name ORDER BY nlga.name
                         SEPARATOR '|'), 'Worldwide') as name
FROM  newsletter_geoarea as nlga
    INNER JOIN newsletter_newsitem_areas as nlni_areas
        ON nlni_areas.geoarea_id = nlga.id
WHERE nlga.type = 2
GROUP BY nlni_areas.newsitem_id

This query returns a news item id and a concatenated list of countries. (one row per news item). However, I want to LEFT JOIN the result of the query to the newsletter_newsitem table to extract more data from each news item so I crafted the following query:

SELECT
    nlni.id as id,
    opp_country.name as countries
FROM
    newsletter_newsitem as nlni
    LEFT JOIN (
        SELECT nlni_areas.newsitem_id,
               ifnull(GROUP_CONCAT(nlga.name ORDER BY nlga.name
                              SEPARATOR '|'), 'Worldwide') as name
        FROM  newsletter_geoarea as nlga
            INNER JOIN newsletter_newsitem_areas as nlni_areas
                ON nlni_areas.geoarea_id = nlga.id
        WHERE nlga.type = 2
        GROUP BY nlni_areas.newsitem_id
    ) as opp_country
        ON opp_country.newsitem_id = nlni.id
WHERE
    nlni.archive_id IS NULL
    AND nlni.id IS NOT NULL

This query turns out to be extremely slow (take more than 5 min). I'm joining by columns that are indexed (nlni_areas.geoarea_id nlga.id, nlni.id).

I can't explain why joining the news item table with the subselect table takes that long given that both tables have only a single row per news item.

The EXPLAIN statement:

I could've joined the tables without using a subselect but since I need to perform other aggregations (similar to this one) I can't afford to have more than one row per news item or I'll have duplicates.


texts:
I have two tables trips and users. Whenever an user posts a trip, it stores id of the user as foreign key. And while fetching, I am joining those two tables the following way.
Query:
SELECT title, starting_point, ending_point, distance, trips.created_on
FROM trips
  JOIN users ON (users.id=trips.created_by);

Result:
{
            "title": "lorem ipsum",
            "starting_point": "Location A",
            "ending_point": "Location B",
            "distance": 20,
            "created_on": "2020-07-27T18:15:00.000Z",
            "author": "Test user"
        },

Result needed.
{
            "title": "lorem ipsum",
            "starting_point": "Location A",
            "ending_point": "Location B",
            "distance": 20,
            "created_on": "2020-07-27T18:15:00.000Z",
            "author": {
                       "id":1
                       "name": "Test user",
                       "role": "admin"
                      }
        },

In short, I want to join user table with trips table but I want to separate the content of user table in different alias in trip.


texts: IS-A relationship with only single sub entity

texts:
Hi I'm trying to design a database for a dictionary. The words has several fields like meaning, an example, and maybe a picture. but the words that are 'verbs' has three more special fields that the rest does not. So is this a Is-A relation ship? but I tried this:
I made a table for words with all sharing fields and a type field, and a table for verbs. these two tables has a one to one relation. the verb table just has the special fields and the foreign key. so if a user wants to only search for a verb he/she needs to search from all words that has the "type == verb". is this a good idea?
Please help me how to design this, Thanks in advance.


texts:
We have a 3.5 TB DB which is already involved in log shipping to two different servers, as one will be in restore sate only and one will be in stand by mode which helps for reporting needs. All three instances are SQL Server 2005.

Now we want to implement log shipping to third server (SQL Server 2008) without downtime. We restored a full backup of primary db on the 2008 server and it took 2 hours to restore and later on we took a differential back up to of primary (since we have too many log backups which run every 15 mins) and restored it and started log shipping. Here it fails by skipping the log file since it is looking for previous log file which is already shipped to other servers.

Finally we stopped log backups and followed the same procedure it succeeded but it took 3 hours downtime which we don’t want to do since it is a prod server. How can we implement log shipping in this scenario? Is it possible to implement with out any downtime.


texts: Is there a standard formula to calculate the optimal resource required by SQL Server base on the Ram size of the server

texts:
My server that SQL Server runs on has 8GB of Ram. Is there a standard formula that DBA use to gauge the minimum and maximum resource to be allocated for SQL Server base on the server Ram size?

I need to know how much of MB is optimal for these settings:

Minimum Server Memory(MB)
Maximum Server Memory(MB) and
Minimum memory per query.

My research get me to this link:Guideline

But I think the best solution is to know how he get to those figures.


texts:
I have a query where a row is selected when field1 <> 10.

When field1 is null, this predicate is false!

According to my primitive maths, if null <> 10 = false, then 10 = null.

What is the rational for this?


texts: How should I model a binary-tree like data using Adjacency List?

texts:
I'm making a system where a user can recruit others, resulting in a binary tree model of users. I have decided to use Adjacency List to model the data but I have some doubts about the number of fields in a table.

I made the users table, there are 24 fields including the node fields of a binary tree:

parent_id, side(left child or right child of its parent), indicated_id(the guy who recruited the user may be different of its parent in the binary tree) + 21 fields(email, name, password, tokens, phone, mobile, etc)

So my doubts are:

1)Should I make a "nodes" table to wrap the user attributes that are not related to the binary-tree like this:

parent_id, indicated_id, side, user_id(pointing to the users table)

Or put everything in the users table? I think if I put everything in the users table I will avoid JOINS and the performance would be better. But if I wrap in a nodes table I will be able to query for a sub-tree without read unrelated fields and Join only the resulting query, so the perfomance lost would be negligible?

Besides that, is there any problem to have a table with 20-30 fields that justify the use of another table for a 1-1 relationship?

2) Is there any better way to model an Adjacency List?


texts:
I am trying to set up an Oracle database in Docker, and once the database is up and running, I can exec into sqlplus just fine, but if I try to connect with another application like Oracle SQL Developer or SQuirreL, either it will stall out while trying to connect, or it will manage to connect and then all future queries will stall. What is wrong with this?

Here is how I set up the container:
I used the docker image available from Oracle on Docker Hub.

Here is the command I ran to create the container:

docker run --name someName -p 1521:1521 -p 5500:5500 -e ORACLE_SID=SOMESID -e ORACLE_PDB=SOMEPDBNAME -e ORACLE_PWD=12345 -it 12a359cd0528

Here are the commands I ran once the container was up and running:

docker exec -it npsIG /bin/bash
sqlplus / as sysdba
alter session set "_ORACLE_SCRIPT"=true;
create user username identified by 12345;
grant dba to username;

At this point, sometimes I can connect on the created user, sometimes it just stalls out. If I manage to connect on the created user, no further queries will process.


texts: PL-SQL "LAST_VALUE" function does not give the desired result

texts:
I have a table with below structure:
create table DOC_AMOUNT_DETAIL
(
  col_id         NUMBER,
  amount         NUMBER,
  operation_date DATE
)

and some example data for this table is :
col_id    |  amount   |  operation_date
---------------------------------------
  1       |  5000     |  11/1/2020
  2       |  1000     |  11/1/2020
  3       |  3000     |  11/1/2020
  1       |  1000     |  11/14/2020
  2       |   500     |  11/14/2020

I'm trying to use last_value function to have below output:
col_id    |  amount
--------------------
  1       |  1000
  2       |  500
  3       |  3000

and the problem is that the query does not give me the desired result :
select t.col_id,
       last_value(t.amount) over(partition by t.col_id order by t.operation_date
        ) amount
from doc_amount_detail t
where t.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy');

Instead, it gives me this :
col_id    |  amount
--------------------
  1       |  5000
  1       |  1000
  2       |  1000
  2       |  500
  3       |  3000

Everything seems fine with my query so can anybody please explain where the exact problem is?
Thanks in advance


texts: Is It Safe to Remove Local Service Accounts?

texts: I have changed my SQL Server services to use domain service account. The server still has NT Service\MSSQL$INSTANCENAME and NT Service\SQLAgent$INSTANCENAME listed under Security\Logins. They each have the sysadmin role granted to them. Since they are not used by the services, can I safely remove them from the Logins on the server? I have search a bit online but haven't found a good answer to this question. I just want to make sure these accounts aren't used for anything else.


texts: Cannot start SQL Server replication as already running

texts:
I'm trying to configure a sql replication on the sql server 2012 .

I am logged in as "sa", when I attempt to start replication i get the following error :

How can I stop this job and restart the replication ?


texts: SQL*Plus fail instead of prompting for substitution variable

texts:
Imagine a file test.sql with the following content
SELECT '&myvar' AS greeting FROM dual;

When we run that file in sqlplus. We will be prompted for the value of the variable &myvar since it's not defined, and that is normally a good thing. But when running automated tasks with non-interactive scripts, this can be a problem.
Is there any way for sqlplus to error out when trying to access an undefined variable instead of prompting for it?
This is how the script gets run
cat test.sql | sqlplus localhost@ORCL


texts: Table clustered-index and partitioning in innoDB

texts:
I have a table with 45 columns, 3 of which are relevant:

table Transactions(tr_id : int primary key, client : int, tr_date : datetime);

The inserts to the table are done client-by-client
The selects from the table are mostly done based on tr_date

As I read innoDB when inserting re-arranges the data on disk based on the primary key which is tr_id. This would mean that neither my inserts nor my selects are benefited by this arrangement.

Inserts are horribly slow and getting slower as the table grows (currently 34,000,000 rows - 36GB)

I want to explore the clustering and partitioning options, but innoDB clusters based on primary key and doesn't allow partitioning on columns that are not part of the primary key.

Is including tr_date in the primary key a correct approach? Is there another option I should explore?


texts: Oracle database - get two rows (A,B) for every (COD) row

texts:
I have a Oracle database and a variable that can have a value for A and a value for B like in this example:

cod   cat  v1   v2   v3
123   A    1    10   11
123   B    0    0    0
111   A    2    3    4
111   B    0    7    0

I have cod in a table RAP and the CAT is A or B if it has values v1,v2 or v3 in two different tables. For the upper result I must have

RAP:

id cod den
1  123 aaa
2  111 bbb

VAL1_A

fk_rap  v1  v2  v3
1       1   10  11
2       2   3   4

VAL1_B

fk_rap  v1  v2  v3
2       0   7   0

how can I write an script to do that so that if I don't have nothing in the VAL1_B to get an row with zero on B cat for the cod. So I have to get an CAT A and B for every COD. I don't have anywhere the value A or B, it's just a text to put in CAT for the values it has in VAL1_A and VAL1_B.

I'm making something like this:

        with cte as
            (select rap.cod,
                    case
                     when (coalesce(val1_a.v1,0)<>0 or
                           coalesce(val1_a.v2,0)<>0 or
                           coalesce(val1_a.v3,0)<>0)
                     then
                        'A'
                     when (coalesce(val1_b.v1,0)<>0 or
                           coalesce(val1_b.v2,0)<>0 or
                           coalesce(val1_b.v3,0)<>0)
                     then
                        'B'
                    end
                     as cat,
                   coalesce(val1_a.v1,0) as v1_a,
                   coalesce(val1_a.v2,0) as v2_a,
                   coalesce(val1_a.v3,0) as v3_a,
                   coalesce(val1_b.v1,0) as v1_b,
                   coalesce(val1_b.v2,0) as v2_b,
                   coalesce(val1_b.v3,0) as v3_b
             from rap
                 full outer join val1_a on val1_a.fk_rap=rap.id
                 full outer join val1_b on val1_b.fk_rap=rap.id
           )
     select cod,
            cat,
            case when cat='A' then v1_a
                 when cat='B' then v1_b
            end
              as v1,
            case when cat='A' then v2_a
                 when cat='B' then v2_b
            end
              as v2,
            case when cat='A' then v3_a
                 when cat='B' then v3_b
            end
              as v3
     from cte

but I have only a value for A or B... I want a value for A and one for B....
How can I do this?


texts: In which context does a procedure run when called from another context?

texts:
Envisage:

use databaseA;
go

exec databaseB.dbo.spProcedure();
go

Does the procedure execute against databaseA - the context from which it is called, or databaseB - the context in which it exists?

If the procedure had

insert into myTable(example) values ('example');

... and if both databases had identical tables myTable, which database will receive the new record?


texts: MySQL: Automated reinstall, export + import all, for a fresh start?

texts:
I have the suspiction that my MySQL installation and all databases are somehow borked. For a fresh start, I would like to create dumps from all the databases, uninstall MySQL, delete the data in /var/lib/mysql, reinstall MySQL, and then import all the dumps.

I know I could figure out the individual commands for each step.

But is there a more convenient, automated way to do this all at once, where I can leave the computer alone for a while, come back and it is finished?

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.19, for debian-linux-gnu (x86_64) using  EditLine wrapper


texts: Is it possible to change the MySQL server-id during a SQL session?

texts:
I have a MySQL 5.5 server with a server-id of 20. I want to execute a few queries on that server, but have them write to the binlog as a different server-id than 20. Is this possible?

I am aware set sql_log_bin is commonly used to turn binlogging on/off at-will, but I actually do want to execute these queries and have them written to the binlog, just as another server-id.

set server_id=21 errors saying it needs to be global, not a session variable, so I may already have my answer, but still, I have a feeling it is possible somehow. I mean, the mysql replication slave component can do it.


texts:
I have a MyISAM MySQL table that keeps crashing. The table has only 16 rows and is very rarely written to (maybe once a month). I have a medium-sized site that typically has about 500 pageviews per hour at peak times, and each pageview queries this table 1-2 times.

How can I figure out why this particular table keeps crashing, and how I can prevent it from crashing again in the future?  After it crashes, most of the pages on my site break.

I have access to the mysql logs, but I'm not sure what exactly to look for.

EDIT: here are a couple snippets from the error log:

120317 12:35:18 [ERROR] /usr/libexec/mysqld: Incorrect key file for table './db/tablename.MYI'; try to repair it

and then later:

120318  4:29:00 [ERROR] /usr/libexec/mysqld: Table './db/tablename' is marked as crashed and should be repaired


texts: Starting MySQL with SSL option using systemd

texts:
Can someone explain how to start a MySQL server with SSL options enabled using systemd? I've generated certificates and edited the my.cnf file to enable SSL but I'm not sure how to restart MySQL so that it allows SSL connections to be made with systemd.

This command did the trick before systemd but it now now longer works:

/etc/init.d/mysql start --ssl-ca=/etc/mysql/newcerts/ca.pem --ssl-cert=/etc/mysql/newcerts/server-cert.pem --ssl-key=/etc/mysql/newcerts/server-key.pem

Any help would be much appreciated.


texts: Bulk exporting SQL Server 7 data and bulk importing it to SQL Server 2008 R2

texts:
Like the title suggests, I want to bulk export data within an sql server 7.0 database into a file and then import it to an sql server 2008 r2 database, I did the following, I first typed this command on the windows 2000 shell:

C:> bcp sticom.dbo.societe format nul -c -f C:\test.fmt -t, -T

it was executed successfully, then I copied the C:\test.fmt in a network shared folder so I can access it from the other windows 7 system, I then copied on the desktop and tried to execute this command on the command prompt:

C:\ bcp sticom.dbo.societe in C:\test.bcp -f C:\test.fmt  -T

and I got this error:

  SQLState = 08001, NativeError = 2   Error = [Microsoft][SQL Server
  Native Client 10.0][SQL Native Client]An error has occurred while
  establishing a connection to the server. When connecting to SQL Server
  2005, this failure may be caused by the fact that under the default
  settings SQL Server does not allow remote connections. SQLState =
  S1T00, NativeError = 0 Error = [Microsoft][SQL Native Client]Login
  timeout expired

I checked my connection, I checked the services and the paths, everything was in place


texts: Delete records if not present in subquery

texts:
I want to delete records from a table that are not found in a subquery. But it's not having the desired result.

The table has multiple rows per SKU. Each price row has a valid_from and valid_to date fields. I want to erase all prices but keep those that are currently valid (where valid_to is greater or equal to current date). I want to maintain future prices (where valid_from is in the future), but one step at a time.

Here's what I tried:

delete from prices
    where prices.id <> (
        select distinct on (sku) prices.id
        from prices p2
            where
                p2.valid_to >= CURRENT_DATE
            and p2."type" = 'regular'
            and prices.id = p2.id
        order by sku, p2.valid_from desc, p2.inserted_at desc
    )

The table has around 500k rows. This subquery returns around 23k rows. I expect that my above query will delete everything but those 23k rows.

    select distinct on (sku) prices.id
    from prices
        where
            valid_to >= CURRENT_DATE
        and "type" = 'regular'
    order by sku, valid_from desc, inserted_at desc

But it's not deleting anything. Why does it not work the way I expect it to?

CREATE TABLE "public"."prices" (
    "id" Bigint DEFAULT nextval('prices_id_seq'::regclass) NOT NULL,
    "type" Character Varying( 255 ) NOT NULL,
    "unit_price" Numeric( 11, 3 ) NOT NULL,
    "wholesale_price" Numeric( 11, 3 ),
    "min_wholesale_quantity" Numeric( 11, 3 ),
    "valid_from" Date NOT NULL,
    "valid_to" Date NOT NULL,
    "product_cost" Numeric( 11, 3 ) NOT NULL,
    "tax_rate" Numeric( 11, 3 ) NOT NULL,
    "store_product_id" Bigint,
    "inserted_at" Timestamp Without Time Zone NOT NULL,
    "updated_at" Timestamp Without Time Zone NOT NULL,
    "sku" Character Varying( 2044 ),
    PRIMARY KEY ( "id" ) );

CREATE INDEX "prices_store_product_id_index" ON "public"."prices" USING btree( "store_product_id" Asc NULLS Last );

CREATE INDEX "index_sku" ON "public"."prices" USING btree( "sku" Asc NULLS Last );

ALTER TABLE "public"."prices"
    ADD CONSTRAINT "prices_store_product_id_fkey" FOREIGN KEY ( "store_product_id" )
    REFERENCES "public"."store_products" ( "id" ) MATCH SIMPLE
    ON DELETE Cascade
    ON UPDATE No Action;


texts: Find tables with multiple indexes on same column

texts:
I am having an issue with scaffolding PostgreSQL DB because npgsql does not handle tables with two index on the same column well. Here's a link to my project on github.

I am trying to find these indexes / tables so I can exclude them from my scaffolding command.

Example:

CREATE TABLE public.altreproc (
  apcod integer NOT NULL,
  aptip character(3),
  apcam character(3),
  CONSTRAINT altreproc_pkey PRIMARY KEY (apcod)
);

CREATE INDEX ialtreproc1
  ON public.altreproc
  USING btree
  (aptip COLLATE pg_catalog."default");

CREATE INDEX ialtreproc2
  ON public.altreproc
  USING btree
  (apcam COLLATE pg_catalog."default");

CREATE INDEX ialtreproc3
  ON public.altreproc
  USING btree
  (aptip COLLATE pg_catalog."default" DESC);

My problem is when a table has 2 different index with the same column (one ASC and one DESC);
I am trying to query tables / indexes like this in db which I am trying to scaffold.

My goal is to find tables which contain more then one index on same column.


texts: How to identify unused databases on my sql server

texts:
I am looking for the best practices or analysis someone would have followed to identify the unused or inactive databases on my SQL Server.

I went through many articles, which say possibly to go for user connections and activating traces to find the DB usage, but implementing those created more confusion.

Kindly help me in understanding if there is a better and safer way I can take my databases offline once identified.


texts: Linked servers on SQL Server 2008 R2 through linked tables in MS-Access?

texts:
We have a really old data source (file-based) in Visual FoxPro, that we currently use via linked table through an ODBC data source.  Recently, we started having an issue because the latest update to our Anti-virus Suite is stopping it from connecting in this manner.  There are other ODBC data sources which do not use the same driver which do not have this issue.

Also the Visual FoxPro driver ODBC driver is being shoehorned in using the registry because it's no longer supported by Windows 7.

Is adding my Visual FoxPro data source as a linked server in SQL server and then in the MS-Access database, relinking the effected tables to it a possible solution to this issue?  Note: the MS-Access databases use VBA and queries to retrieve and manipulate the data.


texts: Dataguard the datafiles are created in another diskgroup

texts:
I have a recently made physical dataguard configuration and everything works correctly so far, but I've noticed that the datafiles that are created in the primary are created in the standby in a different route, the environment is as follows:

Primary - db_name: oradb, db_unique_name: oradb. Oracle 11gR2. ASM storage. Diskgoups: SYS, SYS2 and DATA
Standby - db_name: oradb, db_unique_name: oradb2. Oracle 11gR2. ASM storage. Diskgoups: SYS, SYS2 and DATA.

When creating a tablespace in the primary, example:
CREATE TABLESPACE TBS_DATA DATAFILE
  '+DATA' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE 100M ...
the respective datafile is created in the diskgroup DATA ... +DATA/oradb/datafile/tbs_data...
all good, the doubt that I have is that I realized that in the Standby database the datafile is created in the diskgroup SYS +SYS/oradb2/datafile/tbs_data...
, what should I do to create the datafiles in the correct path, that is +DATA/oradb2/datafile/tbs_data...?
Thank you very much in advance


texts: Mapping 5 columns to 1 (ID, dimension) table without 5 joins?

texts:
I'm using survey monkey's API to dump survey data into a SQL data warehouse.

So I'm dumping response data into a table 'resonses' thusly:

survey_id, Q1, Q2, Q3, Q4, Q5

survey3944, 848948344, 430984384, 43094839, 238094983, 2380284

Then I have a dimensional table 'answer_key' that has the id/ text combos. Such as

ID, text

848948344, 5 - very satisfied

I'm wondering how I can join the 5 answer columns above into meaningful text (or at least the integer) without doing 5 joins.

I mean say I have the table 'responses' and the table 'answer_key'.

My initial thought would be go through the motion:

Select r.survey_id, a.text, a2.text, a3.text, a4.text, a5.text
from responses r
inner join answer_key a on r.q1 = a.id
inner join answer_key a2 on r.q2 = a2.id
inner join answer_key a3 on r.q3 = a3.id
inner join answer_key a4 on r.q4 = a4.id
inner join answer_key a5 on r.q5 = a5.id

Is that the best way to accomplish this? Just wondering. Especially if the survey is expanded to 10 questions, or what not.

I realize I can have a row for each question response rather than each survey for the table structure --- that's a possibility but I think it's a bit more difficult given the structure of the JSON, but maybe it's more logical.


texts: Defining a column as nullable in a view (but not in the referenced table)

texts:
My company is working on implementing a new HR application that will unify our existing HR processes. We've run into a bit of a sticking point, though; according to business rules, the EmployeeID and SupervisorID columns in the Employee table cannot be null. However, the application requires these columns to be nullable, and won't synchronize the columns while they're set to disallow null.

The proposed solution was to create a view where the schema appeared to allow null values in a column, while leaving the table alone to prevent them from being entered into the data. I'm not entirely convinced this is possible, and I haven't been able to find a way to do it. Is this possible? If not, what is the best way to handle this situation?


texts: SQL Server 2016 - Catastrophic linked server error

texts:
I have an issue with connecting to a linked server from SQL Server 2016 to an Ingres 10.1 database via an Ingres 64bit driver ODBC connection setup - ODBC connection succeeds and the linked server connection test succeeds.

T-SQL used for testing has been:

select *
from openquery("linked server", 'select * from "table"')

Some tables come back fine although most tables retrieve the columns but have no data throwing the error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "" reported an error. Provider caused a server fault in an external process.

Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server

If I change the select * to specific columns, I still get the errors. I have tried changing the provider settings to "Allow in process" on/off; both settings still do not work. If I turn the above setting on i get the error:

The provider reported an unexpected catastrophic failure.

Msg 7330, Level 16, State 2, Line 1
Also a stack dump is created within the SQL logs saying EXCEPTION_ACCESS_VIOLATION.

I have played with the security connection settings of the linked server but no luck.

Any ideas fellow DBAs?


texts: How to persistently set MySQL (Linux mysql command) session time zone to UTC?

texts:
I'm using MySQL on Linux (CentOS, recent release).  I want the MySQL system variable time_zone to reflect UTC.

When I start a MySQL session (using the command-line tool mysql) and check the time zone setting, I see this:

mysql> select @session.time_zone ;
+--------------------+
| @session.time_zone |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

I know (see MySQL Set UTC time as default timestamp) that I can configure the server to have UTC as its default timezone.  What I want is to set up a client session to have UTC as the default.

The page above says I can set a MySQL system variable in my SQL syntax, like this:

mysql> set @session.time_zone="+0:00";

This leads to having the session time zone to be the local (SYSTEM) time zone:

mysql> show variables like '%zone%' ;
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | PDT    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

What I'd like to do is to set the time zone as a kind of preference for a user, like a setting in .my.cnf.

Problem is, the MySQL 'mysql' command options don't list a 'time zone' option for .my.cnf, and (of course I had to try it anyway) putting

 time_zone="+0:00"

in my $HOME/.my.cnf file leads to the message

 mysql: unknown variable 'time_zone=+0.00'

I tried setting the TZ environment variable, and that has no impact.

As the StackExchange reference above says, the only effective way I've found is through issuing the SQL command:

mysql> select @session.time_zone ;
+--------------------+
| @session.time_zone |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

mysql> set @session.time_zone="+0:00";
Query OK, 0 rows affected (0.00 sec)

mysql> select @session.time_zone ;
+--------------------+
| @session.time_zone |
+--------------------+
| +0:00              |
+--------------------+
1 row in set (0.00 sec)

So...

My question then is, is there some other mechanism for setting MySQL session variables?  Or some other way to set the session time zone?


texts:
Lets say one of my items in a collection looks like that:

{
  User:123
  Grade : 100
}

I want to sum up the grades for each student so i have this output:

123: 100, 445: 30 etc

Im normal sql, its the equivalent of a group by + sum() query. How can i do it with mongo?


texts: How to store multiple data types for same column (for many columns)

texts:
To preface, similar questions have been asked, but I have not found any that address a table that has a large number of columns affected by this issue.  All the similar seem to address a table that has a small number of columns affected by this issue...

Proper way to store a value that could be multiple different types
Best way to store a value which could be multiple types
Best way to store different types of data in a single column, which should be query friendly - MySql
https://stackoverflow.com/questions/10455783/different-types-for-the-same-column
https://www.sqlservercentral.com/forums/topic/multiple-data-types-for-the-same-column

...but I have a MySQL database table that has many (e.g., 30) columns currently setup as the data type decimal(5,2).
Now, a new scenario has arisen where I need to enter a decimal(5,2) OR a char(2) (i.e., one or the other, but never both) for each of those 30 columns.  To give you context, I am entering the quantity of elements detected in a sample and, if the quantity of an element is not detected, then I must be able to identify that an element was tested, but not detected, such as "ND".  I also must know if the element was not yet tested.  For example, if the column is DEFAULT NULL, then it may indicate that I have not yet tested the respective element.  So, there are three possible situations related to this scenario:

A decimal(5,2) value indicates that the element was tested and detected.
A char(2) value indicates that the element was tested and not detected.
A NULL(...?) value indicates that the element was not tested.

So, my initial thought was to change the change columns' type to a varchar, in order for the columns to be able to store either a char(2) or decimal(5,2) value, but that seems be a dangerous path called Entity-Attribute_Value, which may require me to build the application layer more to accommodate the limitations caused by it.
So, I read chapter 6 of the book "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" (http://rads.stackoverflow.com/amzn/click/1934356557) and several other related posts, and the two most common answers seem to be:
1. Use a design that is based on the type of query you want to perform.
In my case, I want to perform multiple types of queries that compare column values of different rows, determine averages, and possibly unforeseen more.
2. Use the Class Table Inheritance design.
I would like to implement the Class Table Inheritance design, but that would require many more tables to be created for each column.  It seems like an overly complex solution, given the many columns that need only one of two types of data.
For example, if my understanding about Class Table Inheritance is correct, then for each of the 30 columns in my base table, I must create a single table for each subtype and make their primary key also serve as a foreign key to the base table, which means that I must create 60 tables.  It seems excessive to create 60 tables in order to deal with 30 columns that might have one of two data types.  Also, it seems that I would need to figure a way to manage those 30 pairs of tables so they refer to the same column, possibly in the application layer.
One possible solution that comes to mind before I end this post is: Use the Class Table Inheritance design, but instead of creating 30 pairs of tables for my two data types, create just 2 tables for the 2 data types and add a column that stores a value that relates each pair, such as the name of the base table column that they would relate to.  But this does not seem like good practice either.
Any suggestions?


texts: Nested select: insert values into table with two subqueries

texts:
I'm developing a Stored procedure for SQL Server 2016.

I have to do this (the following sql statement doesn't work):

Insert into AggregationChildren (AggregationId, AggregationChildrenId, Position)
SELECT Parent, Serial, Position
FROM
    OPENJSON (@json, '$.Aggregations')
    WITH (  Parent nvarchar(20) '$.Parent',
            Children nvarchar(max) AS JSON )
        CROSS APPLY
            OPENJSON (Children)
            WITH ( Serial nvarchar(20), Position int)

My problem is that AggregationId and AggregationChildrenId are integers and Parent and Serial are nvarchar(20).

There is a relationship between Parent and AggregationId, and between Serial and AggregationChildrenId. I can get it using Code table.

I know how can I do it when I have to insert values for one column:

Insert into AggregationChildren (AggregationId)
    Select CodeId from Code Where Serial = (SELECT Parent
    FROM
        OPENJSON (@json, '$.Aggregations')
        WITH (  Parent nvarchar(20) '$.Parent',
                Children nvarchar(max) AS JSON )
            CROSS APPLY
                OPENJSON (Children)
                WITH ( Serial nvarchar(20), Position int))

The data I'm trying to insert is:

{
    ...,
    "Aggregations": [{
        "Parent": "88962730000000004051",
        "Children": [{
            "Serial": "81861400000000020227",
            "Position": "1"
        }, {
            "Serial": "81861400000000033191",
            "Position": "2"
        }, {
            "Serial": "81861400000000046051",
            "Position": "3"
        },
        ...
        ]
    }, {
        "Parent": "88962730000000016653",
        "Children": [{
            "Serial": "81861400000001825849",
            "Position": "1"
        }, {
            "Serial": "81861400000001832643",
            "Position": "2"
        }, {
            "Serial": "81861400000001841911",
            "Position": "3"
        }, {
            "Serial": "81861400000001850803",
            "Position": "4"
        }, {
            "Serial": "81861400000001862474",
            "Position": "5"
        }, {
            "Serial": "81861400000001874774",
            "Position": "6"
        }, {
            "Serial": "81861400000001884159",
            "Position": "7"
        }, {
            "Serial": "81861400000001898352",
            "Position": "8"
        }, {
            "Serial": "81861400000001904764",
            "Position": "9"
        },
        ...
        ]
    }]
}

But, how can I do it if I have to use two Select CodeId from Code Where Serial = (SELECT ...?

I think I have to do something like this but without doing the same select three times:

 Insert into AggregationChildren (AggregationId, AggregationChildrenId, Position)
    Select CodeId from Code Where Serial = (SELECT Parent
    FROM
        OPENJSON (@json, '$.Aggregations')
        WITH (  Parent nvarchar(20) '$.Parent',
                Children nvarchar(max) AS JSON )
            CROSS APPLY
                OPENJSON (Children)
                WITH ( Serial nvarchar(20), Position int)),
    Select CodeId from Code Where Serial = (SELECT Serial
    FROM
        OPENJSON (@json, '$.Aggregations')
        WITH (  Parent nvarchar(20) '$.Parent',
                Children nvarchar(max) AS JSON )
            CROSS APPLY
                OPENJSON (Children)
                WITH ( Serial nvarchar(20), Position int)),
    SELECT Position
    FROM
        OPENJSON (@json, '$.Aggregations')
        WITH (  Parent nvarchar(20) '$.Parent',
                Children nvarchar(max) AS JSON )
            CROSS APPLY
                OPENJSON (Children)
                WITH ( Serial nvarchar(20), Position int)


texts: How to create an index used by a WHERE extractValue(…) LIKE '…'?

texts:
Setup

Let's say you have a table with some XML data. For the sake of argument, say:

CREATE TABLE t_xml (xml XMLTYPE NOT NULL);
INSERT INTO t_xml VALUES ('<name>Alice</name>');
INSERT INTO t_xml VALUES ('<name>Bob</name>');
INSERT INTO t_xml VALUES ('<name>Carol</name>');
INSERT INTO t_xml VALUES ('<name>Chuck</name>');

A function index works with =

Consider the following query:

SELECT * FROM t_xml WHERE lower(extractValue(xml, '/name')) = 'alice'

If I want this query to use an index, I can create a function index:

CREATE INDEX t_xml_f ON t_xml(lower(extractValue(xml, '/name')));

With this an explain plan tells me:

SELECT STATEMENT
 TABLE ACCESS BY INDEX ROWID T_XML"
  INDEX RANGE SCAN T_XML_F

So far so good.

What index to use with LIKE?

But instead of =, what I really want is a LIKE. What index can I create so that the following query doesn't do a full table scan?

SELECT * FROM t_xml WHERE lower(extractValue(xml, '/name')) LIKE '%a%'

Now, Oracle won't use the above index with a LIKE, and this for a reason. If I wasn't using an extractValue(), I could use a text index. But I am using an extractValue().


texts: Oracle 9i import error IMP-00017: following statement failed with ORACLE error 3113

texts:
I try to import a schema in my database 9i of copy which is a clone of the original from where I made the export 9i also and when trying to import the schema after many hours gives the following error:

  IMP-00017: following statement failed with ORACLE error 3113:
   "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := 'C20537'; SREC.MAXVA"
   "L := 'C23524'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STA"
   "TS.NUMARRAY(454,455,....

  IMP-00003: ORACLE error 3113 encountered
  ORA-03113: end-of-file on communication channel
  IMP-00000: Import terminated unsuccessfully

The exp command is:

exp system/nnnnn CONSISTENT=y OWNER=myschema FILE=/path/backup_schema.dmp log=/path/backup_schema.log BUFFER=30720

The imp command is:

imp system/nnnn FROMUSER=myschema TOUSER=myschema FILE=/ path/backup_schema.dmp LOG=/path/backup_schema.log BUFFER = 536870912

I have searched and can not find a solution to this problem.


texts: Help preparing for MySQL Master Slave Replication

texts:
I am not in the best situation. I inherited an Ubuntu 14.04 8 GB RAM, 8 CPU MySQL 5.5 database server with almost 400 GB of business-critical data (stored on external SSD) contained within several thousand different databases. My database administration skills and experience are nascent. I want to create a backup of this data to set up MySQL Replication, but I need to create the backup with minimized impact and downtime.

These databases are individually backed up with mysqldump about every four hours. This unfortunately means that I have no single, point-in-time, logical or raw backup of the entire database server and to top it off, binary logging is not enabled on that server. But I do have the capability to individually restore these backups.

In total, there about 250,000 tables in the database server. Of those tables, about 90,000 use the myisam engine and about 160,000 use the innodb engine.

I know there will be some downtime but I would really just like to avoid having downtime of an unknown duration during which I am obliged fully backup the data and deploy replication at the same time.

In testing, I've given thought to or tried various approaches:

using Percona Xtrabackup
using mysqldump with a single transaction (for innodb) and no locks for the myisam tables
rsync'ing the mysql data directory, then gracefully shutting down the MySQL server, and rsync'ing the flushed out changes
converting the myisam tables to innodb, then doing a mysqldump or using xtrabackup
using my existing backups to start replication, then letting the slave catch up
restoring my existing backups, then syncing the changes with pt-table-checksum and pt-table sync
and the list can go on...

Without me providing excessive detail about my testing methods and results, I would like to know how you would approach this situation.

EDIT: In essence, my question is: With the goal of minimal downtime and given my scenario, how would you create a backup of the database server in anticipation of setting up MySQL Replication?

I would appreciate any advice, opinions, services, or resources you may have. Thank you.


texts: Error when using DBCA to create database Oracle XE 18c

texts:
I am trying to create a new database using dbca.
Windows 10.
Please help me to find solution.

Here is the log


texts: For accessing Oracle 12c RAC database by JDBC to ensure high availability, should I use TAF or FCF?

texts:
The case I encounter is that if one of the database on RAC array is suddenly down, the transaction is rolled back and connections between JBoss and Database are broken.

What I want is to preserve the JDBC connections and redirect them to another oracle database on the array. Also, can transactions preserved?

In this case, should I use TAF or FCF?

Besides, if TAF is suggested to be used, is it true that thick driver has to be used in JBoss?


texts: Trouble connecting to Oracle database with sqlplus and OS authentication

texts:
I am a read-only user to an oracle database, and the DBA gave me two accounts, one with a specific user name and password, and another with OS authentication. The OS authentication is supposed to let me connect by authenticating with my active directory user account that I use to login to my laptop, where I am then running sqlplus.
I can connect and start querying just fine with this command, which shows it is picking up my tnsnames file:
sqlplus username/password@database

However, the version to connect with OS authentication doesn't work:
sqlplus /@database

ERROR: ORA-01017: invalid username/password; logon denied

Am I doing something obviously wrong? I checked in powershell that my user and domain name are the user and domain name that was set up for OS authentication for this database.
I did another test, this time using powershell to do the connection and again with my AD login that should work:
Add-Type -Path "C:\Windows\Microsoft.NET\Oracle.ManagedDataAccess\Oracle.ManagedDataAccess.dll"

#This connection string works for the normal authenticated account
$connectionString = "User Id=$username;Password=$password;Data Source=$datasource"

#But this one gives me ORA-01017 invalid username/password
$connectionString = "User Id=/;Data Source=$datasource"

$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
$connection.open()

I also made sure that SQLNET.AUTHENTICATION_SERVICES= (NTS) is in the sqlnet.ora file
Any thoughts?


texts:
OS is openSUSE Tumbleweed. Previously I had PostgreSQL 13 installed. I just removed it by:
> sudo zypper rm 'postgres*'

> sudo zypper rr PostgreSQL

> sudo rm -rf /usr/local/var/postgres

Then I install PostgreSQL 12 by instructions here:
> sudo zypper addrepo http://download.opensuse.org/repositories/server:database:postgresql/openSUSE_Tumbleweed/ PostgreSQL

> sudo zypper ref

> sudo zypper in -f postgresql postgresql-server postgresql-contrib

> sudo zypper in -f postgresql-plperl postgresql-plpython postgresql-pltlc

> sudo systemctl enable postgresql

> sudo systemctl start postgresql

But the database status is faulty:
> sudo systemctl status postgresql.service
[sudo] password for root:
● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
     Active: failed (Result: exit-code) since Thu 2021-05-27 23:08:43 EDT; 2h 10min ago
    Process: 1576 ExecStart=/usr/share/postgresql/postgresql-script start (code=exited, status=1/FAILURE)
        CPU: 21ms

May 27 23:08:43 localhost systemd[1]: Starting PostgreSQL database server...
May 27 23:08:43 localhost postgresql-script[1576]:  Your database files were created by PostgreSQL version 13.
May 27 23:08:43 localhost postgresql-script[1576]:  Could not find executables for this version.
May 27 23:08:43 localhost postgresql-script[1576]:  Please install the PostgreSQL server package for version 13.
May 27 23:08:43 localhost systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
May 27 23:08:43 localhost systemd[1]: postgresql.service: Failed with result 'exit-code'.
May 27 23:08:43 localhost systemd[1]: Failed to start PostgreSQL database server.

For some reason, whatever I try, I cannot get rid of these status errors:

Your database files were created by PostgreSQL version 13.
Could not find executables for this version.
Please install the PostgreSQL server package for version 13.

How can I install PostgreSQL 12 and get rid of PostgreSQL 13 database files?


texts:
I would like to know some details about oracle licensing.  What is oracle processor licences? How does it differ from the other?We are planning for a dual processor server, do we need to purchase 2 diff licences??
Thanks in advance. .


texts: mysql: show which users have permissions to a DB

texts:
Is there a way to show all users that have permissions on a database?

I'm looking for something like:

> show_permissions_for_db <DB>;
+----------+----------------------+
| User     | Permissions          |
+----------+----------------------+
| jeff     | read                 |
| jill     | read, write          |
| jack     | read, write, grant   |
+----------+----------------------+


texts: I'm building up a large MyISAM table from a few dumps. The first 21GB took less than 10 hrs total. The next 5GB is forecasted to take two months

texts:
I had a couple machines building up identical MyISAM tables.  I've dumped all of those tables to a few files, and am now trying to restore them all into a single table on my own machine.

The first four restores, done by pv'ing the dumpfile into mysql, took no more than 10 hrs in total (may have been as little as 5).  Those files were around 5-13 GB each.

The table is now about 60 million rows and 21 GB.  It is also hash-partitioned into 100 partitions.  However, I partitioned it when it was 20 million rows and got it up to 60 million without much problem.

Now I'm trying to dump another 5GB (maybe about 15 million rows) into the table, and pv is predicting it will take 50+ days. This doesn't seem to be a problem with pv, however, because the db's data directory is growing very slowly when I do the restore.

Additionally, I've looked at gnome-system-monitor, and the CPU and memory usage seems to be quite low.  Tried rebooting and attempting to restore again, no change.

This is actually my second attempt at building up the big table.  I tried once before over a longer period (waiting for the data to come in on the other machines), and suddenly I was getting this same problem.

This is a table with a single (natural) BIGINT primary key.  I don't know a whole lot about database internals, but I tried a query suggested in this other post: slow load speed of data from mysqldump

to get the size of the index.  Here are the results (reformatted for readability)

Storage Engine: MyISAM
Data Size: 19.706 GB
Index Size: 0.954 GB
Table Size: 20.660 GB

I would make sense that inserts would get slower as the table gets larger.  However... it doesn't make sense that going from 60GB to 80 or less should take 100 times as long as going from 0 to 60.

So, does anyone have any guess what might be causing this?

Thanks


texts:
I'm working on a project based on a CMS, where there is a built-in roles table.

However, the CMS allows a user to have one role only using role_id

And these are the requirements that struggling me:

A user can be a Freelancer and an Employer at the same time.
A user has a Freelancer Feedback and an Employer Feedback.

This is what I came up:

Users
id | role_id  | freelancer  | employer |  freelancer_feedback  | employer_feedback

freelancer, and employer will have an active or  inactive values.

And we will use the role_id for authorization purposes (if user is a user or moderator or developer, etc) only

Roles
role_id  | role_name  |  description | login_destination | deleted |  default_context

The thing is, I've never dealt with multi roles feature in a project before. I would love to hear from you, tips or suggestions.


texts: I have approx 90 GB sized database, and I need to schedule a mysqldump backup. How long will it take to perform a full hot backup approximately? I'm asking because I've never had to backup a database as big as this one yet.


texts: Postgresql 12.2 "Problem running post-install step. Installation may not complete correctly. The database cluster initialisation failed" on windows 10

texts:
I get this error whenever I try to install Postgresql 12.2

  "Problem running post-install step. Installation may not complete
  correctly. The database cluster initialisation failed".

I followed this post
Pg install: "The database cluster initialisation failed"

My comspec is fine and my region setting is fine. So I don't know what else to do. I'm running on Windows 10 and I had postgresql 11 on my laptop before but uninstalled it. I also tried installed with admin privileges on.


texts: Optimize query to minimize tmp copying time

texts:
I have a query when Checking the profile i get this :

I read in order to optimize this i'll have to work on my joins .

I also added index on the fields that i'm grouping by (date_seance , heure_debut) in the main table (seance_pratique)

Is there something i can do to make it faster ?

Update 1 (EXPLAIN QUERY):


texts: What is the timeout of a query in SQL Server 2008

texts:
I was running a query to delete in batches of a huge table. After say around 3 hrs sometimes 4 hrs I get a message saying

  Invalid object name '#table_name#'

After sometime is there a connection lost or something? What is the cause here?


texts: Can not drop table due to replication error, though table is not being published

texts:
I am attempting to drop a table in a database that is a publisher. The table in question is not being published. The error I get is:

Msg 15021, Level 16, State 1, Procedure sp_MStran_ddlrepl, Line 14 [Batch Start Line 1]
Invalid value given for parameter @procmapid. Specify a valid parameter value.
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.

I've checked sysarticles to see if there's an old or orphaned record, but the table doesn't appear. Any ideas as to what the issue is here?

Edit: SQL Server 2016 RTM


texts: Why do/should DBMS cache at the application layer?

texts:
This article describes DBMS as caching data at the application layer, which is why they often use Direct IO to bypass the file buffer cache.

My question is: what benefits are provided by the DBMS caching information at the application layer? How are these benefits affected when the DBMS is on a server that is remote to the application?


texts: How to pass a table-valued parameter to sp_execute_external_script?

texts:
I have a stored procedure that calls an external script via a SQL Server language extension. I would like for my stored procedure to use a caller supplied table valued parameter (TVP) to then submit input data to the external script:

ALTER procedure [dbo].[testTvp]
(
   @inputTvp dbo.myTvp READONLY
)
AS
BEGIN

   DECLARE @tSqlString nvarchar(100)=N'select * from @inputTvp'

   EXEC sp_execute_external_script
        @language = N'Java',
        @script = N'com.example.TestTvp',
        @input_data_1 = @tSqlString -- this doesn't work

END

When I execute this procedure:

DECLARE @myInput dbo.myTvp

INSERT INTO @myInput values ('a');
INSERT INTO @myInput values ('b');

EXECUTE [dbo].[testTvp] @myInput

I get the following error:

  Must declare the table variable "@inputTvp".

I suppose that means that @inputTvp is not visible (out of scope) to sp_execute_external_script.

Is there a way to pass in @inputTvp to sp_execute_external_script?

I know that I could store the @inputTvp data into a temp table and pass in a t-sql string to query from that temp table. But I'm trying to avoid using a temp table.


texts:
I've got this tab-delimited flat file with fifty columns and two million rows.  I am trying to import it into Sql Server 2017 Express.  (I first tried working with it in Excel... hopeless.)

I see two import paths available: SqlServer Management Studio's Import Flat File command, and the Import and Export Data wizard.  Both fail, but in different ways.

With SSMS, it seems to do a very good job of parsing the file and guessing the column types, but it bases the lengths on only the first few hundred rows.  One column is long text, so I've tried setting it to nvarchar(4000) or nvarchar(max) or ntext.  A few others I knew might be longer than the default guessed length of 50 so I bumped up the sizes.  But when I run the import, it says that some column would be truncated or otherwise fail to convert, and it refuses to say which column is the one having trouble.

With the import wizard, on the other hand, it doesn't guess column types at all, and I have to manually set the ones I know.  And thankfully, it has a setting that allows you to tell it per column whether to fail on conversion errors or ignore them.  But it ignores this setting -- it always fails no matter how you set the tolerance, per column or globally.

The good part is that it does identify a column name where it failed, namely the long text column.  But as best I have been able to measure it, there's nothing in it which should have failed.  I don't think any value is over 4000 characters (the longest I've located is 2027) and I'm pretty sure the character set is pure ascii-7.  In fact, it's been forced to all uppercase.

I also tried importing from the abortive Excel sheet I made.  This was thwarted because only the 32 bit importer could see Excel, and only the 64 bit one could see my database instance.

How can I get one or the other of these to allow the import to complete?


texts: TempDB size issue after applying Indexes

texts:
I added several indexes to a SQL Server 2012 database to help with performance a couple of days ago. Today our partition with TempDB on it ran out of space. TempDB went from less than 10 GB to 38 GB. after running :

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

it looks like I have about 22GB of free space in tempDB. I understand that TempDB was used to do all of the sorting that was done by SQL Server but now it is causing space issues. If TempDB has that much free space why is it complaining about running out of space on the server? What can I do to prevent this problem from happening again?


texts: Grouping data in many to many relationships to improve performance

texts:
I have a "classic" SQL Server three table many-to-many in the form

PC -< [PCHasSoftware] >- Software

I am having issues with data volumes and performance in a very large database scenario, so am exploring a further redesign to improve performance and cut storage, etc.

Many PCs will have the same software titles. So can we cut storage by somehow reorganizing that into groups of titles. Then instead of saying

"this PC has these 16 titles" - needing 16 rows per PC

we have instead:

"this PC has this group of 16 titles" needing 16 rows somewhere and only 1 row per PC.

So if 10,000 PCs have these 16 software titles, I'm storing 10,016 rows instead of 160,000 rows.

Two initial thoughts on that idea.

Build software to identify the 8 most commonly occurring groups, and then retain 8 bit flags on the PC table, and have a template PC which holds what those titles are in the same structure
Organize into groups

PC -< [PCHasSoftwareGroup] >- [Software Group] -< Software

or (edit)

    PC -< [PCHasSoftwareGroup] >- [Software Group] -< GroupHasSoftware >- Software

The difficulties with these models I perceive are:

How to identify which groups are most popular. Probably a chunk of SQL operations that runs in a batch job overnight to "groupize" the standard mode. I wondered if any clever "dba+maths" people have come up with any algorithms that could do this
When one of those 16 titles is moved off a PC, you suddenly have to remove the group relationships and insert the 15 rows as non-grouped detail row, so potentially a lot of traffic for one update
How to combine this with software which does not fall into any logical grouping. I'm not a fan of using union because "SQL" only seems guaranteed to generate good query plans when you keep things like that out, but that doesn't mean I won't use it if its the best solution!
There will be some data that is specific to this PC, like DateInstalled, DateLastSeen etc etc. So we'll still have to store some data at the PC-software level, but this could still reduce data volumes compared to the data model described at the top of this post

This may have other applications, and I imagine it would have been done before. e.g. in an order entry system, if its very common for a certain combination of products to appear on orders, you could represent that as a group as a single order line in the database and expand it during querying.

I'm just kicking around ideas here and would appreciate all thoughts. I wasn't even sure what to google for but I've tried for the last week to find other examples of this and failed.


texts: Is there a way to improve performance of this simple SELECT query?

texts:
I'm still relatively new to MySQL so please forgive any ignorance on my part. I've tried looking through other posts on here and StackExchange, but haven't had much luck in improving the performance of this simple SELECT query. Let me start with the table schema, the SELECT statement, and the EXPLAIN for it:

Table:

CREATE TABLE `Observations` (
  `InstrumentID` bigint(20) NOT NULL,
  `Epoch` bigint(20) NOT NULL,
  `EndingEpoch` bigint(20) DEFAULT NULL,
  `PhenomenonName` varchar(16) NOT NULL,
  `Value` decimal(18,5) DEFAULT NULL,
  `IsEstimated` bit(1) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`InstrumentID`,`PhenomenonName`,`Epoch`,`Value`),
  KEY `FK_31` (`PhenomenonName`),
  KEY `idx_Epoch` (`Epoch`),
  CONSTRAINT `FK_27` FOREIGN KEY (`InstrumentID`) REFERENCES `Instruments` (`InstrumentID`),
  CONSTRAINT `FK_31` FOREIGN KEY (`PhenomenonName`) REFERENCES `Phenomena` (`PhenomenonName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SELECT:

SELECT PhenomenonName, Value, Epoch
FROM Observations
WHERE InstrumentID=2
AND Epoch BETWEEN 1514782800 AND 1546318740
AND PhenomenonName IN ('demand')
ORDER BY Epoch

EXPLAIN:

{
    "id": 1,
    "select_type": "SIMPLE",
    "table": "Observations",
    "partitions": null,
    "type": "range",
    "possible_keys": "PRIMARY,FK_31,idx_Epoch",
    "key": "PRIMARY",
    "key_len": "34",
    "ref": null,
    "rows": 1042464,
    "filtered": 100,
    "Extra": "Using where"
}

Sorry the explain is in JSON, I know that's not typical but the network configuration needs to be updated for my company's VPN so I can't connect via Workbench right now.

So, this query is for 1 year of 1-minute data, which is ~525K rows out of a table of ~18M rows and it takes ~22 seconds. Originally the PRIMARY key had Epoch before PhenomenonName, which was forcing it to do an index_merge_intersect. But, fixing the order of the key (which as you can see leads to it just using the PRIMARY key now) only shaved off 1 second. I've also tried running ANALYZE on the table, and changing Epoch BETWEEN 1514782800 AND 1546318740 to Epoch >= 1514782800 AND Epoch <= 1546318740, both of which seemed to make no difference.

Granted the hardware right now is very small since we're still developing the server--only a db.t2.small, which is 1 vCPU and 2GB RAM. However, increasing it to a db.t2.large, 2 vCPU and 8GB RAM, brings it down to 13 seconds. And then increasing it again to a db.t2.2xlarge, 8 vCPU and 32GB RAM, doesn't seem to improve performance--it's still ~13 seconds. Also, the storage is 100 GiB SSD and the buffer pool size is 75% of total RAM as per AWS's default setting.

Is this just a hardware limitation? If so, that's fine, but I want to make sure I'm not missing any opportunities to optimize the table schema or select statement, especially since this is just a query for one instrument, and queries will regularly have to be made aggregating several of them together.

Thanks in advance, and please let me know if I can provide anymore information.

Edit: By the way, this is unrelated, but as you can see I have two foreign key restraints, but only one of them has created an actual key. Is there any reason for this? My understanding was that foreign key restraints automatically create a key.

Edit2: The query took ~23 seconds on the small instance, and since adding Value to the PRIMARY KEY as per @SQLRaptor's suggestion, it's down to ~21 seconds. Still about ~13 seconds on the db.t2.2xlarge.


texts: Normalization and Breaking down tables for InnoDB

texts:
I'm designing a web application, something small but deals with lots of read and inserts.
Let's say 60% read and 40% writes. My storage engine is InnoDB on MySQL. I have a table
called User which has a bunch of stuff in it. I broke it down into smaller tables such as
address table and bio table. Here is an example :

Original table

User table
----------
user_id   PK
user_email
user_pass
user_dob
user_gender
user_country
user_state
user_city

After breaking it down

User table
----------
user_id   PK
user_email
user_pass

Bio Table
----------
user_dob
user_gender

Address Table
-------------
user_country
user_state
user_city

So do you guys think, having smaller table but several of them is better for a high
traffic database or should I keep them all in one table ?


texts: Overhead of decomposing complex query into multiple simple queries?

texts:
What kind of overhead should I expect when replacing a single complex query with multiple simple queries?

My goal is to improve the readability and portability of all SQL code so I will be favoring simple constructs and replacing database-specific extensions with ANSI SQL whenever possible.

For example:

Imagine the client is invoking Dynamic SQL (as opposed to stored procedures)
Scenario 1: Client invokes: INSERT INTO employee SELECT name FROM user
Scenario 2: Client invokes:

Statement getNames = connection.createStatement();
try (ResultSet rs = getNames.executeQuery("SELECT name FROM user"))
{
  while (rs.next())
  {
    String name = result.getString(1);
    PreparedStatement prepared = connection.prepareStatement("INSERT INTO employee SET name = ?");
    prepared.setString(1, name);
    prepared.executeUpdate();
  }
}

Scenario 1 is not a complex query, but for the sake of argument let's pretend it is. Scenario 2 achieves the same result using multiple (simpler) queries. What kind of overhead can I expect for Scenario 2 versus Scenario 1? Is it something I should worry about or is it negligible?

UPDATE: https://stackoverflow.com/a/14408631/14731 makes a good point. Decomposing queries by hand hard-codes an execution plan instead of allowing the database's optimizer to choose. That being said, it's still not clear whether the overhead is meaningful.


texts:
Is it possible to create an index in MySQL that will help with a query like this:

SELECT a, b, c
FROM `table`
WHERE a + b + c > 0

I've tried adding a composite index on all a, b, and c to no avail. Is there any way to optimize a query like this where the condition is based on a mathematical calculation?


texts: MySQL does not follow Ubuntu System Time Zone

texts:
An Ubuntu 16.04 server has it's time zone set to America/New_York using the command

sudo timedatectl set-timezone America/New_York

This is confirmed by running the commands

timedatectl status | grep "Time zone"

 Time zone: America/New_York (EST, -0500)

date +%Z

EST

However MySQL thinks that the system's time zone is still UTC. MySQL 5.7 server (running within a docker container) has been restarted befpre querying for the MySQL timezone

Output of MySQL Query show global variables like '%time_zone'

system_time_zone UTC
time_zone SYSTEM

Did something go wrong?


texts: Is there a less manual way of changing database properties for all databases in an instance?

texts:
I've recently audited a SharePoint server and found configurations that are against best practice.

I'm hoping there is better and quicker way than right-clicking and changing these settings manually on over 100 database.

For instance, all the databases are set to autogrow by 10MB and the log files are set to autogrow by 1%.  I'd like for these to be a set number and not a percentage.

Any resources or recommendations would be greatly appreciated!


texts: In RDS digest function is undefined after creating pgcrypto extension

texts:
On an AWS RDS instance, we have run

CREATE EXTENSION pgcrypto;

The extension gets created.  Both queries:

SELECT digest('a', 'sha256') FROM table_name;
SELECT public.digest('a', 'sha256') FROM table_name;

Gives the error function public.digest(unknown, unknown) does not exist


texts: PHP-MYSQL messaging system performance problems

texts:
I want to create a messaging module for the social network web-site project (PHP & MySQL) I've been working on. What substructures do well-known sites use for modules like this?

I think, generating a new record on MySQL for each of new messages will slow down the system and cause problems after a while. What would you suggest me for saving the datas? SQLLite, file system or a high-performance dbms ? (exp: postgresql)

Thanks


texts: Is EXPDP the best backup approach when running Oracle on Windows with no RMAN?

texts:
Well, I'm the SQL Server DBA here, and we have a Oracle Senior Dba that manages Oracle Databases and users. I'm curious about how he is handling these servers/users.

What is the best approach, when running oracle, to make backups?

If I'm not mistaken, dumps are not backups. right?

What he does here is:

1)A script create a dynamic query with EXPDP for ALL users and sent it to a TXT file:

SELECT  'EXPDP parfile=export_options.par dumpfile='
        || to_char(sysdate, 'YYYYMMDD_')
        || username
        || '_ServerName.dmp log='
        || to_char(sysdate, 'YYYYMMDD_')
        || username
        || '_ServerName.log schemas='
        || USERNAME
        || ' directory=dumpdir compression=ALL schemas=' || USERNAME
        FROM dba_users
        where created > '01/01/2005' and username not in (Some users)
        order by created desc

2)then it creates a lot of EXPDPs inside a txt file:

EXPDP parfile=export_options.par dumpfile=20190221_USER_SERVER.dmp log=20190221_USER_SERVER.log schemas=USERdirectory=dumpdir compression=ALL schemas=USER

EXPDP parfile=export_options.par dumpfile=20190221_USER_SERVER.dmp log=20190221_USER_SERVER.log schemas=USERdirectory=dumpdir compression=ALL schemas=USER

EXPDP parfile=export_options.par dumpfile=20190221_USER_SERVER.dmp log=20190221_USER_SERVER.log schemas=USERdirectory=dumpdir compression=ALL schemas=USER

3)a WINDOWS TASK SCHEDULER runs this TXT running EXPDP on all users and sending them do a folder.

Is this something that you guys normally do on Oracle? I would like to now if this is something I could use as a best pratice.

Thanks.


texts: What is the impact of LC_CTYPE on a PostgreSQL database?

texts:
So, I've few Debian servers with PostgreSQL on it. Historically, those servers and PostgreSQL are localized with the Latin 9 charset and back then it was fine. Now we have to handle things like Polish, Greek or Chinese, so changing it become a growing issue.

When I tried to create an UTF8 database, I got the message:

  ERROR: encoding UTF8 does not match locale fr_FR Detail: The chosen LC_CTYPE setting requires encoding LATIN9.

Few times I made some research on the subject with my old pal Google, and all I could find was some over-complicated procedures like updating the Debian LANG, recompile PostgreSQL with the correct charset, editing all the LC_ system variables and other obscure solutions. So for the time being, we let this issue aside.

Recently, it came back again, the Greeks want the stuff and Latin 9 don't want to. And while I was looking into this issue again, one colleague come at me and said “Nah, it's easy, look.”

He edited nothing, didn't do magic tricks, he just make this SQL query :

CREATE DATABASE my_utf8_db
  WITH ENCODING='UTF8'
       OWNER=admin
       TEMPLATE=template0
       LC_COLLATE='C'
       LC_CTYPE='C'
       CONNECTION LIMIT=-1
       TABLESPACE=pg_default;

And it worked fine.

I actually didn't know about LC_CTYPE='C' and I was surprised that using this wasn't on the first solutions on Google and even on Stack Overflow. I looked around and I only found a mention on the PostgreSQL documentation.

  When LC_CTYPE is C or POSIX, any character set is allowed, but for other settings of LC_CTYPE there is only one character set that will work correctly. Since the LC_CTYPE setting is frozen by initdb, the apparent flexibility to use different encodings in different databases of a cluster is more theoretical than real, except when you select C or POSIX locale (thus disabling any real locale awareness).

So it made me wonder, this is too easy, too perfect, what are the downside? And I've a hard time finding an answer yet. So here I come posting here:

tl;dr: What are the downside of using LC_CTYPE='C' over a specific localization? Is it bad to do so? What should I expect to break?