texts: SQL Server 2017 crashes when backing up because filepath is wrong

texts:
I was trying to restore my database and SQL Server kept crashing. I would get a message in SSMS that said there was a network transport error (the connection dropped bc the crash). I checked the logs and found nothing more than SQL Server closed unexpectedly. I would then have to go and restart the service.

I narrowed the issue down to the script that the GUI was trying to run. The problem is when it goes to take a tail log backup, the path to the backup files is wrong. It should be D:\mapbenefits\...

BACKUP LOG [mapbenefits]
TO  DISK = N'D:mapbenefits_LogBackup_2019-02-21_13-58-24.bak'
WITH NOFORMAT, NOINIT,  NAME = N'mapbenefits_LogBackup_2019-02-21_13-58-24',
    NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5

I have two questions.

How do I fix this path? I tried going into the server settings and the backup path is D: with no slash. If I add the slash the gui removes it. This is SSMS v17.9.1. I can pick D:\mapbenefits\ and that works but I want D:\DATABASE\...
Is this a bug? Should SQL server crash just because a path is incorrectly typed? Once I fixed the file path it has no trouble. I can reproduce anytime just by mucking up the filepath.

If I run a query to check version I get CU13, but if I go into settings I see version 14.0.1000.169.

Looks like this is a bug and is reproducible so I posted it to here: https://feedback.azure.com/forums/908035-sql-server/suggestions/36920542-incorrect-filepath-with-backup-log-command-causes


texts: pg_dump custom archive and create backup date

texts:
I have archive file with custom format created by pg_dump.

Does it contain the backup creation date?

I can't create backups by myself. I receive the archived files over specific network channel. Backup files have a reception date timestamp, but not the date when it has been created on server.


texts: In Data Warehouse Foreign Key on Fact tables Necessary?

texts:
In DW, does ForeignKey have advantages other than data integrity?

For ongoing updates to DW, we have to enable and disable FKs and that causes locks and sometimes ends up causing deadlocks against select queries as victim.

Can someone please give some good scenarios of fks usage and how to handle the updates/insert against Datawarehouse ?


texts: Database Configuration In DB2 and Oracle

texts:
What is the equivalent in Oracle for these Db2 configuration parameters?

Interval for checking deadlock (ms)         (DLCHKTIME)
Lock timeout (sec)                        (LOCKTIMEOUT)


texts:
I have one production SQL Server 2008 Database with log shipping set up for that server in US.

I have a account in the log shipping database so I could sometimes run query on log shipping database.

Now I need to ship data back to Korea daily but I don't want to impact production performance. Can you suggest any technique to achieve this


texts: Calculating the percentage change from the previous row

texts:
I just entered the world of window functions. I have a similar problem to solve as in this question.

I have this table:

The last column (%Grown) is given by:

-(1 - price of row 2 / price of row 1) * 100

All the information I have found so far was related to sums, avg for all rows. I don't know how to bound two rows so I can calculate the percentage.

How do I translate this into SQL?


texts: Does SQL Server evaluate functions once for every row?

texts:
I have a query like this:

SELECT col1
FROM   MyTable
WHERE
    DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
       BETWEEN col2
       AND     col3
;

This gives a tooltip on the execution plan similar to this:

Does the dateadd part of the seek predicates get executed for every row in the query? Or does SQL Server calculate the value once for the whole query?


texts: Do not want to allow Queries to take more than x minutes?

texts: Do not want to allow Queries to take more than x minutes ! What System variable can I set to automatically terminate it.


texts: Getting error while creating a New Connection using Studio 3T for Mongo DB

texts:
I tried to create a new connection with Studio 3T for Mongo DB. I don't have a previous DB connection and this is the new one that I am trying to create it. I can't able to figure out why this is happening.

When tried to do so, I got an error stating:

Error Details :

  Database error (MongoSecurityException): Exception authenticating
  MongoCredential{mechanism=SCRAM-SHA-256,
  userName='{{my_db_username}}', source='admin', password=,
  mechanismProperties={}}.

This is StackTrace:

  Stacktrace: |_/ Database error (MongoSecurityException): Exception
  authenticating MongoCredential{mechanism=SCRAM-SHA-256,
  userName='sociofyDB_rifaye', source='admin', password=,
  mechanismProperties={}} |____/ Mongo Server error
  (MongoCommandException): Command failed with error 18
  (AuthenticationFailed): 'Authentication failed.' on server
  localhost:27017. |____... |____... The full response is: |____... {

  |____... "ok" : 0.0,
  |____... "errmsg" : "Authentication failed.",
  |____... "code" : NumberInt(18),
  |____... "codeName" : "AuthenticationFailed"
  |____... }

EDIT: I see this error majorly when I try to provide the authentications. This does not occur when I connect normally without the authentication(username/pwd).


texts: Creating a "database link" from Oracle to SQL Server

texts:
Is there a way for Oracle to read from SQL Server?

We are basically trying to read from a SQL Server table and update the information in Oracle. We used to have a database link in Oracle to another Oracle database.

Now the application is replaced by a new application that runs SQL Server, so we're trying to replicate the same process but now it's Oracle to SQL Server. Hope that makes sense.


texts:
I'm designing a simple database and have a question regarding a relationship. So far, I have users, books, highlights and bookmarks. The relationship rules are as follows:

Each user may 'subscribe' to a book.
Each book may have many users 'subscribed' to it.
Each user can have highlights and bookmarks for the book(s) he has 'subscribed' to.

The schema I have so far:

I'm having a problem with the last relation. What I have thought about so far was to use a single primary key instead of 2 keys acting as a composite key for the table book_users and then referencing that as a foreign key in the highlights and bookmarks table.

Another method that my friend recommended was to add another field called additions to the table book_users with a field of TEXT and storing the information in JSON format. The latter seems cleaner and I can't forsee any problems but I just want to confirm if this method is alright to implement.

I was wondering if there is a more efficient way to do this?

Thanks for your time.


texts: Parallelism not engaging on specific database

texts:
I have a few databases with identical schema, lets call:

DB_A
DB_B

I have a query that when run takes 11 seconds and returns 11K (11000) records on DB_A but when the exact same query is run on the identical DB_B it takes 40 seconds and returns 7K (7000) records.

The schema is identical, but when i run it on DB_B it runs on Degree of Parallelism 1, on DB_A it runs on 16.

I tried setting the cost of threshold to 0 to force parallelism but got the same result.

Why is that? How can the same query running on cloned databases behave so differently?

Any ideas are welcome. I'm using SQL 2017 Standard.


texts: skip columns while using LOAD command mysql

texts:
I have a csv file having 5 columns and i want to load only one out of that, is there a way i can do it. I want to use LOAD statement of MySQL 5.5
Any pointers are welcomed, help would be much appreciated


texts: Importing a large column (8000 chars) from Excel using SSIS

texts:
I'm trying to set up a regular import of an excel spreadsheet that we get from a vendor.  I'm using SQL 2008 R2 SSIS to import it into a table.  The problem connection manager is an OLE DB connection to the spreadsheet.  The spreadsheet is Excel 2005.  The database connection manager is using Native OLE DB\SQL Native Client.

The problem I'm getting is that the OLE DB Source keeps setting a couple of the excel columns to DT_WSTR with a 255 length.  I have 2 columns however that are 4000 and 8000 characters respectively.  I've gone into the Advanced Editor/Input and Output Properties tab for the OLE DB Source and changed the DataType/Length in the External Columns list and the Output Columns list.  Unfortunately when I leave the Advanced Editor an error appears in the error list.

Validation error. Data Flow Task: Data Flow Task: The output column "ColumnName" (226)
on the error output has properties that do not match the properties of its
corresponding data source column.

When I click on the Source again I get an option to "fix" the errors automatically.  I then select "yes" and "ok".  The error is now gone but when I go back to the External Columns the datatype/length settings are back to the original DT_WSTR/255.

I could manually change the spreadsheet into a delimited file to avoid the problem but would rather not add a manual step into the process.  Does anyone know of a way to get an Excel source to allow for a long column?


texts: Declarative partitioning with Postgres for integer column

texts:
I was reading this article:
https://blog.timescale.com/scaling-partitioning-data-postgresql-10-explained-cd48a712a9a1

the article lifts information directly from here:
https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

My question is - if we declaratively create a partitioned table like so:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY peaktemp;

and then let's say we insert into measurement, but with a peaktemp that is not covered by an existing partition.

Before we insert, do we need to manually create our own partition like so:

CREATE TABLE measurement_55 PARTITION OF measurement FOR VALUE 55;

..or will Postgres create that table for us if we insert into measurement with peaktemp as 55?

Sorry the syntax might be wrong but hopefully the question makes sense.
Basically I want to partition a table by an integer column, one partition for each value of the integer. And I am wondering what is automated and what remains manual in this case?


texts:
I have a table of about ~10M rows, each contains a (unique) sentence.
I'm looking for ways on how to be able to offer fast full-text search that's also supports substrings, prefixes and suffixes,
preferably something lightweight that I can run on the user's desktop.

I've looked at SQLite FTS4, SphinxSearch and other MySQL text searches, but they all seem to not be able to support substring search. Any ideas for good, lightweight solutions?

I'm doing a one-time insert (that can take as long as needed) and then just reading from the database (searches).

Thanks in advance!


texts: Troubleshooting a SQL Server instance that won't start

texts:
I've had a problem with one instance of SQL Server 2008 (the free version) not starting automatically. I always have to right click on the icon in the task bar and start it manually. It is set to "Automatic" start mode in the SQL Server Configuration Manager.
I have checked the error log text file and there is nothing that looks likely. I don't see any attempt at automatic startup, just the log of my manual startup.

I then dug into the Event Viewer and I see two warning entries, given around computer boot time:

Warning - SQLBrowser - The configuration of the SQL instance UPSWSDBSERVER is not valid.
Warning - MSSQL$UPSWSDBSERVER -

  The description for Event ID ( 19011 ) in Source ( MSSQL$UPSWSDBSERVER
  ) cannot be found. The local computer may not have the necessary
  registry information or message DLL files to display messages from a
  remote computer. You may be able to use the /AUXSOURCE= flag to
  retrieve this description; see Help and Support for details. The
  following information is part of the event: (SpnRegister) : Error
  1355.

UPSWSDBSERVER is the server instance for UPS WorldShip. This is NOT the instance that I am having trouble with. In fact I never have trouble with it... the UPS software works 100% reliably. The instance I am having trouble with is tied to a program called ShipWorks.

I have run the UPS Diagnostic tool which runs (what appears to be) a thorough check on its database, everything passes. I can not see the UPSWSDVSERVER instance in the Configuration Manager. Where the heck is it? The configuration manager only lists the ShipWorks instance.


texts: How to do Partition for MySQL 100GB table in zero downtime?

texts:
I have a Innodb engine table with 100GB,I have a planned to do partition the table.Can anyone tell me how to do it in zero downtime?

In 100GB table the records are increasing for each and every seconds.How can I do partition with out affecting the incoming records?

Using Percona-Mysql 5.5.30.Innodb-file-per-table was enabled.

Please tell me the steps.


texts: Where can I get information about AcquireConnection call?

texts:
I have a dtsx package that fails to run. When I turn on logging I get:

  The AcquireConnection method call to the connection manager "MY_DSN" failed with error code 0xC0202009.

MY_DSN is a legitimate DSN and my user has access to the database.

I don't see any messages in the SQL Profiler. I am tracing for the following:

Audit Login
Audit Logout
Existing sessions
PreConnect Complete
PreConnect Started.

What other diagnostic tools should I be using to be getting more useful error messages?


texts: Reference assembly in sqlproj not being deployed to server

texts:
I have a Visual Studio 2013 Database Project which takes a modified version of fastJSON as a reference. I selected Generate DDL in the reference properties as shown in this screenshot:

And in the .sqlproj msbuild file:

<Reference Include="fastJSON">
  <HintPath>..\..\fastjson\output\net40\fastJSON.dll</HintPath>
  <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
</Reference>

However bin/debug/Project.sql does not contain a line for CREATE ASSEMBLY fastJSON . . .. Adding the assembly manually works and my project will then deploy and run. What do I do to get Visual Studio to deploy my assembly?


texts: Tuning dedicated Percona Server with XtraDB for OLTP

texts:
Hardware specs for an OLTP DB

dual quad core xeon with hyperthreading
24GB ram
Hardware RAID10 BBU

Software specs

Standard install of Ubuntu 10.04LTS with default LVM partitioning
Installed Percona Server with XtraDB

My priority is data integrity/protection followed closely by performance.
QUESTIONS

Are there settings in the RAID card that should be or should not be set? Since I have BBU, write-back should be set right?
Is ext4 ok or should I create a separate xfs volume for the data?
What should the kernel scheduler be set to? noop? deadline?
Are there other settings that I should tweak at the Hardware/OS/Filesystem level?


texts: MySQL Partition by Substring of a Column

texts:
| columnName |
       --------------
            201
            202
            210
            211

I have 5 million records and I want to partition the table by the substring of it's column.

For example, I want all 20s, 21s, 22s, and so on to be on the same partition.

SUBSTRING(columnName, 1, 2)


texts: Import batches of rows from Excel in parallel

texts:
We receive an Excel file weekly currently containing about 250k rows. The file grows by about 300 rows each week.

In SSIS I've hooked up an Excel source to an OLE DB destination to import the file but it takes a long time.

I've looked at using the SQL Command to specify how to select part of the sheet but I can't see how to turn this in to a parallel operation.

Is it possible in SSIS to split the Excel file (by rows) and import it in parallel?


texts: What is the difference between Com_xxx and handler_xxx?

texts:
Initially my com_delete variable and handler_xxx variables are nil.

| Com_delete     | 0     |+
| Handler_delete | 0     |
+----------------+-------+

then I issued a delete query delete from gt where i=1;

when I checked both the variables it changed to 1.

My Question is what these variables differentiate ? I'm using MySQL 5.6


texts:
We have a table that we use to store customers and items. We need to be able to find customers that have bought one item and have not bought another item:

customer     item
Sally        1
Sally        2
Sally        3
John         1
John         2
Paul         2
Paul         3

We want to find users who bought "1" and not "3". In this case we only want "John".


texts:
I have a old DB with bunch of accounts. One of them is used for sure but for 5 of them or so there is no information.

Is there any info in the system table in Postgresql when was the last login or executed command for a user? Or may be who and when created the user.


texts: How to create test environment for lost transactions

texts:
I have a problem that I hope you can help me to solve. I'm using SQL Server 2008 R2.

I need to create a test environment in which I'll be able to artificially
'create' lost transactions. Due to network disconnects in the app I'm using, sometimes (very, very rarely) there are 'broken' transactions (some part of the data update goes to DB, but the rest is not which results in incomplete data in database). On my test machine I don't have a clue how to do it.

I know that's the problem with app design but I need to prove that this kind of behaviour is appearing so that the vendor of the app can fix this bug.

I really hope that you can help me out. I'm not a dba - I learn what I can to become one so if this question is 'elementary' please be understanding :)


texts: mysql : remove row if parent id match in primary key columan

texts:
I don't know that what should be the title of the question.

I have two table which schema define as below:

Table 1 (organization_master):

CREATE TABLE `organization_master` (  `organization_id` int(11) NOT NULL,  `parent_organization_id` int(11) DEFAULT NULL,  `organization_name` varchar(150) NOT NULL,  `c_user_id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `organization_master` (`organization_id`, `parent_organization_id`, `organization_name`, `c_user_id`) VALUES
(119, NULL, 'Patel-Apps', 220),
(183, 119, 'Patel-Apps sub 1', 220),
(184, 119, 'Patel-Apps sub 2', 220),
(250, 247, 'Patel-Apps2', 222);
ALTER TABLE `organization_master`  ADD PRIMARY KEY (`organization_id`),  ADD KEY `organization_name` (`organization_name`),  ADD KEY `c_user_id` (`c_user_id`);
ALTER TABLE `organization_master`  MODIFY `organization_id` int(11) NOT NULL AUTO_INCREMENT;

Table 2 (organization_assigned_user) :

CREATE TABLE `organization_assigned_user` (  `organization_assigned_user_id` int(11) NOT NULL,  `organization_id` int(11) DEFAULT NULL,  `user_id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `organization_assigned_user` (`organization_assigned_user_id`, `organization_id`, `user_id`) VALUES (29, 250, 219),(30, 250, 220);
ALTER TABLE `organization_assigned_user`  ADD PRIMARY KEY (`organization_assigned_user_id`),  ADD KEY `user_id` (`user_id`),  ADD KEY `organization_id` (`organization_id`);
ALTER TABLE `organization_assigned_user`  MODIFY `organization_assigned_user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

When I run below query:

SELECT    om.organization_id,
          om.parent_organization_id,
          om.organization_name
FROM      `organization_master` AS om
LEFT JOIN organization_assigned_user AS oau
ON        om.organization_id = oau.organization_id
WHERE     om.c_user_id =220
OR        oau.user_id =220
GROUP BY  om.organization_id;

It give me output as below:

organization_id parent_organization_id  organization_name
119             NULL                    Patel-Apps
183             119                     Patel-Apps sub 1
184             119                     Patel-Apps sub 2
250             247                     Patel-Apps2

Now, my question is that I don't want the rows which parent_organization_id exists in organization_id column. i.e 199 parent_organization_id  exists in organization_id. so my out put should be like like below:

organization_id parent_organization_id  organization_name
119             NULL                    Patel-Apps
250             247                     Patel-Apps2


texts: How to get full query text from performance_schema.threads?

texts:
According to documentation:
Access to the threads table does not require a mutex
and has minimal impact on server  performance. The other
sources have negative performance consequences because they require a mutex.

So yes, I want to use threads table to examine running queries. The trouble is, the PROCESSLIST_INFO column does not contain the full query. It just contains a truncated version of the query. There is a flag viz performance_schema_max_sql_text_length which controls how many bytes are allocated for the SQL query. But it seems to affect the events_statements_history_long table. There is no change in the thread table output. There is also the events_statement_current table. But it is not clear if accessing that table affects performance or not.


texts: Where can I find information on shredding xml with .nodes?

texts:
I recently saw this answer on SO which uses the "local-name" value while shredding xml using .nodes(). I've never seen this before:

select T.N.value('local-name(.)', 'varchar(128)') as Name,
       T.N.value('.', 'varchar(max)') as Value
from @xml.nodes('/data/*') as T(N);

Where can I find the definitive document on commands like .nodes() and .value()?


texts: PostgreSQL drop view in case of unknown whether materialized or not

texts:
I'm trying to drop a view, of which I cannot assure whether it is a MATERIALIZED VIEW or a VIEW.

DROP VIEW IF EXISTS myview;

throws an error, if myview is a MATERIALIZED VIEW and vice versa. What I am looking for is drop statement (or function) that does not care about if it has to drop a MATERIALIZED VIEW or a VIEW.

(hint: there seems to be neither drop nor drop-view tag, therefore drop-table)


texts: inconsistent type after querying search_condition column in all_constraints table Oracle

texts:
I'm trying to get the not null constraints in my tables, so I'm using the following sql request :

    select * from all_constraints
    where owner='userx'
    and constraint_type='C'
    and search_condition='%IS NOT NULL';

The problem is that I'm getting the ORA-00997: illegal use of LONG datatype exception
When looking on the Oracle 9i documentation I realize that search_condition column is of LONG datatype.

How to make use of this column ?


texts: What is the difference in performance between a two single-field indexes and one compound index?

texts:
Suppose you have Users with an email and name, and you want to search by both email and name.

What is the difference in performance between having two indexes (on email and name) and having one index on both fields?

I read the following but did not find the answer:

Multicolumn Indexes (PostgreSQL documentation)
Multicolumn index and performance (DBA Q & A)
PostgreSQL single vs. multi-column indexing for optimal SELECT performance (Stack Overflow)
Multiple column index vs multiple indexes (Percona)

I am interested in the difference in performance for Postgres and MongoDB.


texts: Unusual error message - sql server the row object is inconsistent. please rerun the query

texts:
I'm reviewing the error logs for an instance of SQL Server 2012 and found the following error in the log (error 669):

The row object is inconsistent. please rerun the query

I googled and the only answers seem to be generic listings of sys.messages showing the error number and error description.

Has anyone come across this error before? My thoughts so far is it indicates some kind of data corruption, but the error message doesn't say which database is affected.


texts:
When I execute this SQL:

USE ASPState
GO
IF NOT EXISTS(SELECT * FROM sys.sysusers WHERE NAME = 'R2Server\AAOUser')
CREATE USER [R2Server\AAOUser] FOR LOGIN [R2Server\AAOUser];
GO

I get the following error:

  The login already has an account under a different user name.

How do I know what this different user name is for my login account?


texts:
I was just checking out the propaganda page for PostgreSQL and I found this little piece of art:

It is the "Turtle Style" PostgreSQL logo. How old is this, and did PostgreSQL ever officially use a turtle instead of an elephant? What is the story here?


texts: sliding window partitioning on date field

texts:
I need to apply Sliding Window Partitioning on a table that contains 1B of records.

CREATE TABLE table_1(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [log_date] datetime NULL,
    [field1] [bigint] NULL,
    [field2] [int] NULL,
    [field3] varchar(20) NULL,

 CONSTRAINT [PK_table_1] 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

I want to do the partitioning on log_date field (1 partition per day) , knowing that my queries will not use log_date field , and only Id will be used.

I'm confused here if I have to add log_date to my clustered index , or Id field alone will be alone.
Also if I added log_date to the clustered index , can I still query on the table while filtering only on the Id ?

Edit:

sample of query I'm using :

declare @i int = 1000000
select * from table_1 where id>=@i and id<@i + 200000 order by id


texts:
I have a few "gaps" in my understanding of external memory pressure.  I've found quite a bit of helpful information online, including this info from SQLSkills.com.

  SQL Server has a built-in component of the SQLOS, the Resource
  Monitor, that monitors the QueryMemoryResourceNotification Windows
  Server API to get status about the OS level memory availability on the
  server.  If the Windows OS is under memory pressure, it will set a low
  memory notification that the Resource Monitor thread will detect and
  force the external clock hands on the caches internally to begin
  sweeps of the caches to cleanup and reduce memory usage allowing the
  process to free memory back to the OS.

I have inferred that the Windows OS does not actively "push" the OS
level memory availability status to SQL Server.  Rather, the SQLOS
Resource Monitor must actively ask for it via the Win API
function.
This would seem to be an asynchronous operation.  Am I interpreting
this correctly?
If my reasoning for #1 is sound, what causes the Resource Monitor to get the OS level memory availability status?  Is is performed on a schedule?  Event driven?  How much time might pass from the moment there is low memory at the OS level to the moment the SQL OS Resource Monitor becomes aware of it?
When SQL Server reduces memory usage to free memory back to the OS, just how bad is that?  Is this an "expensive" operation that should be avoided at all costs?


texts: postgresql: make objects owned by parent role

texts:
I have a set of apps that produce data. Each app has its own credentials to access the DB, and they all inherit from a common role (producer).

I'd like to set default privileges to grant access to objects created by a producer, but it does not work because objects are owned by each particular app's login, not by producer.

So: is there a way to have all tables created by these apps to be owned by producer not by their particular login ? Or, is there a way to write these default privileges differently ?

EDIT: and I'd like to avoid changing all apps to make them do a SET ROLE...

EDIT2: and... I'm not the only one: Automatically invoke `SET ROLE` when connecting to PostgreSQL, Inherit default privileges: every table created by *any member of* role A is readable by role B


texts: Data Schema For Stock Control / Multi Source Inventory

texts:
I'm working on a project that involves stock control with multiple stock sources and sales channels. The overall hierarchy I've got so far looks like this;
Sales Channels <---- Allocated Stock Sources <---- Stock Locations (warehouses) <--- Stock Sub Locations <---- Shelf / Bin Locations

As far as rules go for how these entities relate to each other I've come up with this;

The system must have one or more sales channels, each sales channel
must have 1 or more stock sources, a stock source must have 1 or more
stock locations (warehouses / buildings / distribution centres ), a
stock location may have 1 or more Bin/Shelf locations.
A product may have 1 or more stock locations, may have one or more sub
locations in those stock locations and may have one or more Shelf / Bin
location.

First off, is this a solved problem where some reference schema exists I could utilise and save myself some headaches?
If there isn't a reference design for this situation, Am I best to build a 1 to Many relationship to assign stock to a sub location(s) and another 1 to Many relationship for Shelf Locations (if exists)
This is the ERD I've come up with so far;


texts: SQL Server 2012 - Can't detach database on mirror server

texts:
I'm running SQL Server 2012 in a mirror configuration and I need to move the mirror's transaction files.

I deleted the mirror setup on the primary but I can't detach the databases on the mirror, the option is grayed out.

I used activity monitor and the database files are NOT being used so I should be able to detach the databases. Does anybody have any clues as to what the hold up is?

Thank you.


texts: SQLCMD - add date to CSV. When run inside Command Prompt it works, when in Job then %date

texts:
I've been battling for hours to add a date to a CSV name:
For brevity, Im just taking the first character of the date here.

In SQL Command, this works:

sqlcmd -S localhost -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "C:\sqlcmd%date:~1,1%.csv"

But when I try to use that exact same code inside the step of a job, the filename is

sqlcmd_%date

I cant run xp_commandShell, is there a way to get the dynamic date working in SQLCmd please?
I've read through so many articles, eg.


texts: Transfer selected information from a SQL Server business server to a MySQL web server?

texts:
I have a SQL Server dedicated server used for business information on service users etc, and a VPN with MySQL for the main website operation. I wish to transfer some selected tables from SQL Server to MySQL in order to provide statistics on the website, and reduce load on the business server. Primary keys etc are not really important for the transfer - just the main data.

Is it as straight forward as finding a PHP script and setting up a cronjob on the web server to run every hour or so? Really I am unsure about the most practical/safest method to do this?


texts: Database Mirroring and Replication in SQL Server 2008 R2

texts:
I have configured the mirroring and replication between 4 servers (A,B,C,D).i.e, Mirroring between A to B and C to D, Replication between A and C. The configuration was a success and I am able test the replication(B to C) when I have failed over the mirroring dbs(A to B). The replication works fine after the mirroring fail over but I am not able to check its status in the Replication monitor. When I am having any insert in a table which is replicated in B, it is reflected back to C..it means the replication is working fine.

Any thought on how I can view the status of replication from mirrored server. I tried adding the publisher in the monitor, but no luck. If I check the snapshot agent status, it says could not retrieve the info, same with the log reader agent status.

Any suggestions on this please.


texts:
I had these two things running in parallel in the same database -

BCP "SELECT * FROM [ServerName].[DatabaseName].[dbo].[TableName] WITH (NOLOCK)" queryout "\\LocationToMyOutputFile\TableName.csv" -c -S "ServerName" -T

A BCP out command on a PowerShell prompt which looked like above.
A stored procedure which was using the same database. The stored procedure was executing in the same database as the BCP out command (but was not using the same table). The SP drops a table and inserts records into it from a CTE (the CTE is made from tables of same database in question).

BCP out has 1.16 Billion records to output and it took 3565 seconds (~1 hour) to finish.
When the BCP out command was executing the SP kicked in and it took roughly 7 hours to finish. In general, the particular SP finishes in 15-20 minutes.

Here are a few things which I noticed while both were running in parallel -

The wait type for the BCP out command as seen from SQL Monitor was PAGEIOLATCH_SH.
The wait type for the SP were PAGEIOLATCH_SH and PAGEIOLATCH_EX.
While both of them were running in parallel the SP went into SUSPENDED state as seen when executing sp_who2.
After the BCP out command finished executing in about 1 hour, the SP continued to be in SUSPENDED state with very high DiskIO(which was continuously increasing) for another few hours.

After observing the above, these are the questions which come to my mind -

AFAIK, BCP out command doesn't lock the source table and should not affect anything, but seeing the wait types I can infer that BCP Out first loaded the entire 1.16 Billion records from the disk to memory and then output(read outputted) it to flat-file. This caused the SP to go into SUSPENDED state since memory was not available for it to load records from the disk. Is my understanding correct?
Secondly, when the BCP out finished executing, why didn't the SP change its state from SUSPENDED, since all resources would have been free. There was nothing else executing in the database other than the culprit SP. Is it because even after the BCP out finished it still had records in the memory which didn't get flush automatically?

Please let me know your thoughts as I am confused here whether my understanding is correct.

SQL Server version is Microsoft SQL Server 2014 (SP2-CU17-GDR) (KB4505419) - 12.0.5659.1 (X64).


texts: Vertical / Horizontal Scaling Disk Size MySQL or MariaDB

texts:
I have been looking into this for a while and cannot find a suitable solution so would like to know if someone here can please help.

I am trying to set up database back-end that will assist with doing analytics. I need to stick with MySQL or MariaDB as this is what we currently use on all projects and queries.

My problem is that I have access to a few virtual machines. Most of them pretty decent, but they all have a range and capacity of 1-2TB. They cannot be increased in disk size as they have been provisioned and in use for other purposes as well.

As they are only used infrequently and sitting idle for large amounts of time I am trying to set it up as a cluster for a database.

Now, what I would like to know is, how do I enable these machines to be scaled such that I can use the hard drive space as a total.

I.e. I have Server 1 (1.5TB), Server 2 (1.0TB), Server 3 (1.8TB), Server 4 (1.5TB), Server 5 (1.2TB). I would like to shard/cluster them all together to add all the space together so that I can utilise the 7TB in total.

The reason for this is that the data being analysed is generally larger than what 1 of the servers can hold. I am not to worried about replication/consistency and systems being down (As it;s not business critical and any lost data can be recovered and re-imported easily). Although I am happy to use a Master/Slave arrangement if needed (In case something goes wrong).

I have look into the following and haven't found any one thing that answers my question...

MaxScale
Percona XtraDB Cluster
Galera Cluster

I have tried with the above packages, but most of the resources I can find tend to only talk about multi master or master slave systems. Or horizontal scaling for maximum read/write access.

I need a solution for vertically scaling by adding multiple machines together. Mostly for total disk space, less important for CPU, RAM etc.


texts: Is it viable to attribute permissions to SQL Server virtual accounts (NT Service\SQLSERVERAgent, etc)?

texts:
Using MS SQL Server 2012 in Microsoft Windows Server 2008.

I am somewhat confused by the New Account Types Available with Windows 7 and Windows Server 2008 R2 virtual Windows accounts [NT SERVICE]\<SERVICENAME> like NT SERVICE\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT, etc.

Is it viable of daring to give permissions to, for example, [NT Service\SQLSERVERAGENT]' to access a shared resource (or local file or directory)?
And, how to do this?
For example, to attribute permissions to a Windows file share to run psexec ?

While browsing  (or pressing button Find< or adding group and users to a file share) the available in domain and/or  local/remote computer, there is no such accounts available and entering it manually give an error:

  "An object (User or Built-in security principal) with the following
  cannot be found..."

Related (though different) question that provoked this one: How to copy bak files to remote share (without AD/domain accounts involvement)?

UPDATE, answering @Jon-Siegel's comment to my deleted answer:
there is no error in my screenshot, sometimes virtual accounts detection happens, sometimes fails. Really the last 2 days I cannot reproduce it.

Can it be explained that I do not have proper permissions?
When I try to open SQL Sever Configuration Manager, I am always getting:

---------------------------
SQL Server Configuration Manager
 ---------------------------
Cannot connect to WMI provider.
You do not have permission or the server is unreachable.
Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid class [0x80041010]
 ---------------------------
OK
---------------------------

Also, in eventvwr.msc I observe a lot of permission-related errors (under System):

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{FDC3723D-1588-4BA3-92D4-42C430735D7D}
 and APPID
{83B33982-693D-4824-B42E-7196AE61BB05}
 to the user NT SERVICE\SQLSERVERAGENT SID (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) from address LocalHost (Using LRPC).
This security permission can be modified using the Component Services administrative tool

.

I am not sure whether I should open another question or stick with this one...
The follow-up questions are:
1)
what are permissions for reading virtual accounts?

I cannot list anything except local computer in "Select Users, Computers, Service Accounts, Cgoups or Built-in security principals" - the entries are visible but on opening them there are no a single sub-entries visible to me:

2)
How can I enter the NT SERVICE\SQLSERVERAGENT (by typing in it manually) on another computer?

Currently I can choose it only on local computer:

by choosing the name of local computer through pressing "Locations..." button, then choosing top entry in the tree list - see the scrrenshot above;
then typing "NT Service\SQLSERVERAGENT" in "Enter the object names to select" listbox

which is after pressing "Check names" button:

That is, there is no hint how to type it.
Pressing "Advanced..." button, then "Find Now") shows a list where virtual accounts are absent:


texts: Help in designing mysql table for large project

texts:
I am planning to create a website which allows different Companies to register in it. It will have Employees module. Each company will have a CompanyId. How do I maintain the Employees table, since in near future data sets may go extremely large. Kindly suggest which is appropriate. I am listing my intended designs, kindly suggest a better one .

Note : The Employees of one company has no relation with employees of other companies.

    Companies Table

    CompanyId CompanyName
    1       A
    2       B
    3       C

Employees Table

DESIGN 1:

DB : Companymanagement
Table Name : Employee

  Here the employees for a company are identified from the companyId

    EmployeeId      CompanyId   EmployeeName
    1       1(from companies table) Employee-A
    2       2(from companies table) Employee-B
    3       2(from companies table) Employee-C

DESIGN 2:

DB : Companymanagement
Table Name : Employee_1

    EmployeeId      EmployeeName
    1               Employee-A
    2               Employee-B
    3               Employee-C

    Table Name : Employee_2
    EmployeeId      EmployeeName
    1       Employee-A
    2       Employee-B
    3       Employee-C

  Here the employees for a company are stored in individual tables with
  companyid in table name ex Employee_1

DESIGN 3:

DB : Companymanagement_1
Table Name : Employee

    EmployeeId      EmployeeName
    1       Employee-A
    2       Employee-B
    3       Employee-C

DB : Companymanagement_2
Table Name : Employee

    EmployeeId      EmployeeName
        1           Employee-A
        2           Employee-B
        3           Employee-C

  Here, the employees for a company are stored in individual databases
  with companyid in database name ex Companymanagement_1

Kindly suggest me the right direction to manage datas. Sorry if I am vague.


texts: Is it possible to set timezone for a schema - Postgresql

texts:
Is it possible to set the timezone for one schema? Do not want to set Postgresql(v.12) server level.

Please suggest if any ways to this.


texts: Can't remove active node from SQL Server 2008 R2 Clustered Instance

texts:
I am trying to remove the active and only node from a SQL Server 2008 R2 failover cluster.

Everything checks out and then I get this error

  Final result:                  Failed: see details below
  Exit code (Decimal):           -568706566
  Exit facility code:            1562
  Exit error code:               14842
  Exit message:                  Cannot create a file when that file already exists. (Exception from HRESULT: 0x800700B7)
  Start time:                    2015-05-30 16:09:38
  End time:                      2015-05-30 16:10:28
  Requested action:              RemoveNode

2015-05-30 18:47:12 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Wow6432Node\Microsoft\MSSQLServer to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20150530_184438\Registry_SOFTWARE_Wow6432Node_Microsoft_MSSQLServer.reg_
2015-05-30 18:47:13 Slp: Cannot create a file when that file already exists. (Exception from HRESULT: 0x800700B7)
2015-05-30 18:47:13 Slp: Watson bucket for exception based failure has been created

2015-05-30 16:10:24 Slp: Error: Action "Microsoft.SqlServer.Configuration.SetupExtension.ValidateFeatureSettingsAction" threw an exception during execution.
2015-05-30 16:10:24 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: Cannot create a file when that file already exists. (Exception from HRESULT: 0x800700B7) ---> System.Runtime.InteropServices.COMException (0x800700B7): Cannot create a file when that file already exists. (Exception from HRESULT: 0x800700B7)
2

Any thoughts on what is causing this error?


texts: Oracle - clean LOB files - recovering disk space

texts:
I have a friend who has a website and asked me for help.
I often use MySQL databases but never Oracle databases.
And unfortunately he has an Oracle database, so I can't find a solution.

The available disk space is slowly decreasing... I delete a lot of lines from the table but that doesn't solve his problem.
The database continues to take up disk space slowly.

I read that LOB files do not return disk space, even if you delete data.

How can I reorganize LOB files easily with a simple request?
(or/and) How can I recover disk space on Oracle?

SELECT DISTINCT VERSION FROM PRODUCT_COMPONENT_VERSION

12.1.0.1.0


texts:
In using Oracle Edition Based redefinition, I am receiving the error:

ORA-38802: edition does not exist

when issuing the call:

DBMS_SESSION.SET_EDITION_DEFERRED("my_edition")

I know the edition exists because I can query the DBA_EDITIONS view and I can see the edition.  The problem must be that the user must not have been granted use privs on the edition.  I'd like to be able to verify which users have been granted use privileges on which editions.  How is this done?


texts: Designing database containing code metadata

texts:
I'm working on a program that disassembles programs and analyses the results.  I decided to use SQLite to store the data, but I'm having trouble deciding on how to organize the data.

The design I've thought of so far involves placing the code data (comment, line number, opcode, etc) in one table, and then placing data about the code like symbolic names for memory addresses in other separate tables.  There could potentially be millions of lines of code, so I might need to handle millions of columns in the disassembly table.

The program using this database will be a simple text view (for now).  It will fetch enough lines of code from the code table to fit the screen, and the program will look up data about the code from the other tables.  Say that I have a memory address referenced at some point.  My program will recognize that, and look it up in the symbol name table to see if we could find a human readable name for it.  In the future, I might also add editing for individual lines, which could invalidate data stored about a line of code.

Does this seem somewhat sane?  How else might I go about it?  Assuming this rough idea makes sense, what things should I keep in mind for making it fast?  Any suggestions or resources to look at for designing this database would be much obliged.


texts: SSDT publish multiple referenced database projects to new instance

texts:
I am going through the process of importing our existing SQL Server databases into SQL Server Data Tools in Visual Studio 2015 with the aim of putting them under version control.

All the database projects in my solution build correctly after splitting databases with circular references into composite projects. My problem is now trying to deploy them all to a new instance, the publishing is failing because the databases being referenced don't exist e.g.

  SQL72014: .Net SqlClient Data Provider: Msg 208, Level 16, State 1,
  Procedure Foo, Line 13 Invalid object name
  'DB1.dbo.Bar'.

If I try to deploy DB1 then it's same thing as that also references another database which hasn't been created yet

  SQL72014: .Net SqlClient Data Provider: Msg 208, Level 16, State 1,
  Procedure Bar, Line 64 Invalid object name
  'DB2.dbo.Foo'

How do I resolve this so I can publish all database projects in my solution to a new instance?


texts: Can I use unique index in GROUP BY clause instead of column in mysql

texts:
I have a table shift_wise_traffic with following columns.

toll        date            shift      car      truck      bus
 1          2016-09-01        1        10        20        30
 1          2016-09-01        2        15        25        35
 1          2016-09-01        3        20        30        40

 2          2016-09-01        1        5         10        15
 2          2016-09-01        2        20        25        30
 2          2016-09-01        3        35        40        50

 1          2016-09-02        1        10        20        30
 1          2016-09-02        2        10        20        30
 1          2016-09-02        3        10        20        30

 2          2016-09-02        1        10        20        30
 2          2016-09-02        2        10        20        30
 2          2016-09-02        3        10        20        30

Now I want to produce the output from shift_wise_traffic table group by
toll, date. And I have created a unique-index named shift_unique_index having two columns toll and date.

so instead of writing following query,

SELECT toll, date, SUM(car), SUM(truck), SUM(bus) FROM shift_wise_traffic
GROUP BY toll, date;

Can I use this query using shift_unique_index,

SELECT toll, date, SUM(car), SUM(truck), SUM(bus) FROM shift_wise_traffic
GROUP BY shift_unique_index;


texts: Backup & restore to different servers different locations

texts:
I need to back up hundreds of databases and restore them to 20 different servers, and different data file locations for destination servers.

Are there any tool that allows me to copy one database/all databases easily?


texts:
I've read tons of questions and answers about my error but none of them helped me ( from PINAL website, microsoft links and everything ). questions from 9 years ago to today.
I created a Linked server with proper user/pass to read info from our AD.
but it doesn't matter what user I use ( we tried everything, even with the most powerfull user we have in the network that can make it rain if it wants ) and I still get the same error:
Msg 7399, Level 16, State 1, Line 16
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.

i tried to run SSMS with the user with no success. I set the secutiry tab to run with the specific user that can read the AD.
these are the queries i'm trying :
SELECT top 100 * FROM OpenQuery (
ADSI, 'SELECT displayname FROM
''LDAP://myDomain.local/OU=Usuarios'''
)

SELECT TOP 100 * FROM OPENQUERY
(ADSI,'SELECT displayname FROM
''LDAP://myDomain.local/OU=USUARIOS,DC=MyDomain,DC=LOCAL'''
)

SELECT * FROM OpenQuery (
ADSI,
'SELECT *
FROM ''LDAP://myDomain.local/DC=MyDomain,DC=local''
WHERE objectClass = ''User''
')

I opened management studio with this powerfull user and it still doesnt work.  what can be the error here?
I'm querying the AD server remotely from my machine from SSMS.


texts:
Got an interesting mirroring issue.  I am using SQL Server 2008 R2.

Server A mirrors 8 databases to Server B ideally.  Right now the 8 databases are principal on Server B (otherwise healthy).  When I try to manually fail a database over to Server A, it fails over, then a few seconds later it fails back to Server B.

Error logs don't indicate anything other than reporting the failover and (unwanted) failback.  Only way I can think of to keep the databases live on Server A is to manually stop Server B, which would kill HA entirely.

Anybody seen this happen to them?


texts: Remove specific duplicates (all but latest)

texts:
When removing duplicate rows, using the below query from this tutorial, how would I go about forcing which of the found duplicates to remove?

DELETE FROM dbo.ATTENDANCE
    WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
FROM dbo.ATTENDANCE
    GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

And this works great. The reason why I am using this one is because the only unique id available is one in the IDENTITY column. And to determine if there are duplicate rows I have to look at a combination of multiple columns.

But if I have a set of duplicate rows how do I / how does SQL Server decide which to remove? And how would I force it to remove all but the one with the highest IDENTITY value?

EMPLOYEE_ID     ATTENDANCE_DATE     AUTOID
A001            2011-01-01          1
A001            2011-01-01          2

If I would run the query now it happens to remove the second one, with AUTOID 2. But I am trying to remove all but this one (because this is the one latest added).


texts: automatic transactional replication failover to noce2 with AG failover

texts:
I am working on to set up high availability group like
SQL01,SQL02,SQL03(DR) and SQLRPT01 as the reporting server which has our reporting DB.
* From SQL01 to RPT01, I am running transnational replication.
*As of now replication works good,if SQL01 goes of AG immediately goes to SQL02 and comes in good health asap.

Question is once I have fail over from sql01 to sql02, do we have the procedure to fail-over the replication agents to SQL02 and replication resume automatically.(without Using fail-over cluster instance, because we don't have any shared storage planning for this.)
I am not sure how this sp_redirect_publisher etc. is going to work.
Do we have a step by step procedure to set up this configuration??


texts:
I can't find the solution and searching for info doesn't give desired results

Environment:

Server: W2K8 EE 64 bit + MSSQL SE 2K8 R2 64 bit
Client: Windows XP Prof 32 bit

Using SSMS on client machine I get the following error:

TITLE: Connect to Server
------------------------------

Cannot connect to <<server-name>>.

------------------------------
ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------

A security package specific error occurred. (Exception from HRESULT: 0x80070721) (Microsoft.SqlServer.DTSRuntimeWrap)

------------------------------

A security package specific error occurred. (Exception from HRESULT: 0x80070721) (Microsoft.SqlServer.DTSRuntimeWrap)

------------------------------
BUTTONS:

OK
------------------------------

When I connect from another 64bit W2K8 server using SSMS it works correctly.

Is there some workaround to make it work? For now I'm using SSMS on remote 64-bit machine, but would like to use it on local 32-bit PC.


texts: Building a query to copy user security settings to another user

texts:
I am trying to write a query to look at a current users security settings, and then copy those settings over to another user. My data is as follows:

 Logon_user  Logon_level   Form
 ABOND        1            COST
 ABOND        0            PRODUCTION
 ADOND        0            ACCOUNTING
 ATEMPLE      0            COST
 ATEMPLE      0            PRODUCTION
 ATEMPLE      0            ACCOUNTING

I would like to look at the settings for ABOND and copy the logon_level values to ATEMPLE.


texts:
I have been looking at poorly performing stored procedure that performs well for larger customer databases.  I believe the reason is that SQL Server has not had time to properly create good statistics on the queries.

I used the database tuning advisor to recommend several statistics, and after applying those in test environment with a restored backup of the database in question, performance was significantly better.

My question is this.  If I create a script (Maintenance Script as we call them in house) for support to run on customers who may also be experiencing this issue. Would it be a potential bottle neck on performance if a statistic had already been created automatically for the same column(s) by SQL Sever and my script creates a duplicate statistic?

Auto create and update statistics are set to ON in case you are wondering.  We support customers who run SQL 2005 (unfortunately) or higher.


texts: Mysql search matching multiple values in multiple columns

texts:
I need to speed up a search.
Currently we let our users input a name: John Doe

Then in Java we split that string to create multiple values (or "terms") ["John", "Doe"] and loop through the terms provided doing a select in the database looking for that term in a number of relevant columns:

// For each term in the string[]
SELECT *
  FROM Person p
 WHERE (p.idNumber = #{term}
          OR LOWER(p.firstName) LIKE LOWER(CONCAT('%', #{term}, '%'))
          OR LOWER(p.lastName) LIKE LOWER(CONCAT('%', #{term}, '%'))
          OR LOWER(p.companyName) LIKE LOWER(CONCAT('%', #{term}, '%'))
          OR p.phone = #{term});

at the end of the loop we use Java to do an intersection of the matched rows so that all matches are unique.

persons = (List<Person>)CollectionUtils.intersection(persons, tmpPersons);

I'm planning on changing this to a dynamic SQL statement to match all terms provided in one sql statement and removing the CollectionUtils.intersection but before I go through that process is there another way to do this that produce a faster search?

We're using Mysql 5.5 and an InnoDB table.


texts: Is there any way to trace optimizer's work in DB2?

texts: Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query, is there anything in DB2 through which I can get the information like, which plans the optimizer considers, the cost of each plan?


texts: Transactional replication upgrade from SQL Server 2017

texts:
We would like to inplace upgrade of transactional replication topology from SQL Server 2017 to SQL2019 with minimal downtime. The following is the setup. All the all current instances are on SQL Server 2017 enterprise

Server A has 2 databases serving as publisher
Server B has 2 databases serving as subscriber
Server C is the remote distributor.

From inplace upgrade perspective, we have to first upgrade distributor --->publisher--->subscriber.
I would like to know the rollback steps for distributor database if inplace upgrade fails to SQL Server 2019 and we have to revert to SQL Server 2017 with minimal possible downtimes.
Requesting some insights on procedure to do the same.
Kevin


texts:
Possible Duplicate:
  Is it common practice to mix InnoDB and MyISAM tables on same server?

I have a movie clips and photos table in an application; these tables contain a tag column, which allows users to search for the movies/photos by tag; however, the whole database is designed using InnoDB tables specifically for data integrity.

Since the tables are not MyISAM, I cannot use the full-text feature (which sucks).  I was thinking of changing those 2 tables into MyISAM tables; however, I read somewhere that combining MyISAM and InnoDB tables is a very bad idea.

I really would want to use the fulltext feature on those tables.

My questions are these:

Is it really a bad idea (performance-wise, data integrity-wise, etc) to combine those 2 tables?
Is there another way of achieving the same result (the results obtained using fulltext feature) in InnoDB tables?


texts: what is the difference between first and second execution of a query?

texts:
I notice that when I am executing queries the first time (i.e., just after mysqld_safe --user=mysql &), the queries are slower than when they are executed second time. When the query (source) is:
select
    s_name, s_address
from
    SUPPLIER, NATION
where
    s_suppkey in (
        select
            ps_suppkey
        from
            PARTSUPP
        where
            ps_partkey in (
                select
                    p_partkey
                from
                    PART
                where
                    p_name
                like
                    'green%'
            ) and
            ps_availqty > (
                select
                    0.5 * sum(l_quantity)
                from
                    LINEITEM
                where
                    l_partkey = ps_partkey and
                    l_suppkey = ps_suppkey and
                    l_shipdate >= date '1993-01-01' and
                    l_shipdate < date '1993-01-01' + interval '1' year
            )
    ) and
    s_nationkey = n_nationkey and
    n_name = 'ALGERIA'
order by
    s_name;

And I got:
> SELECT .....;
>   41.255s  -------- first time after startup
> SELECT .....;
>   6.242s   -------- second time after startup
> SELECT .....;
>   6.104s   -------- third time after startup

However, in some other times, the difference does not exist. For example, this query(source):
SELECT
    s_name, count(*) as numwait
FROM
    SUPPLIER, LINEITEM l1, ORDERS, NATION
WHERE
    s_suppkey = l1.l_suppkey and
    o_orderkey = l1.l_orderkey and
    o_orderstatus = 'F' and
    l1.l_receiptdate > l1.l_commitdate and
    exists (
        SELECT *
        FROM LINEITEM l2
        WHERE
            l2.l_orderkey = l1.l_orderkey and
            l2.l_suppkey <> l1.l_suppkey
    ) and
    not exists (
        SELECT *
        FROM LINEITEM l3
        WHERE
            l3.l_orderkey = l1.l_orderkey and
            l3.l_suppkey <> l1.l_suppkey and
            l3.l_receiptdate > l3.l_commitdate
    ) and
    s_nationkey = n_nationkey and
    n_name = 'EGYPT'
GROUP BY
    s_name
ORDER BY
    numwait desc, s_name
LIMIT 100;

I got:
> SELECT .....;
>   3m9.264s -------- first time after startup
> SELECT .....;
>   3m9.377s -------- second time after startup
> SELECT .....;
>   3m7.287s -------- third time after startup
>
> ------ This time, the difference do not exist.

MySQL version is 8.0.22
All configurations are default.
Why it is the case?
Thanks.


texts: Transaction log for standalone DB vs AlwaysON availability Group

texts:
I have this question on the generation of transaction logs. For a high transaction active DB what is the ratio of log generation if it is a standalone DB and when it is part of SQL Alwayson availability group(synchronous commit).

I am pretty sure it will be high, but how much higher. Rather than doing a test on my own any answer or reference will be helpful.

Note: The perfmon counter do show the numbers for transactions/logs flushed which seems to be really in case of AG. I want to understand if there is a way to limit it as well.


texts:
Are there any upper limits on the amount of terms that could be between the parenthesis on an IN operator?

For example, the query would look something like:

SELECT * FROM table WHERE number IN (1,2,3...n)

I'm curious if there's any upper limit for 'n'.


texts: How can I define a one to many constraint between columns of the same table?

texts:
I'm creating an IDs table in PostgreSQL to represent the relationship between multiple foreign IDs - call them a, b and c.
I happen to know that the foreign IDs have one-to-many relationships between them:
a 1..* b
b 1..* c
I'd like to represent them as a single table (see below), but enforce the one-to-many invariant between a & b, & between b and c.
Is that possible?
Examples:
The following insertions would be illegal:

pk
a
b
c
reason

p15
a1
b11
c111
c111 already exists

p16
a1
b21
c213
b21 already exists for a2

p17
a2
b11
c214
b11 already exists for a1

Given the following legal table:

pk
a
b
c

p1
a1
b11
c111

p2
a1
b11
c112

p3
a1
b12
c121

p4
a1
b12
c122

p5
a2
b21
c211

p6
a2
b21
c212

p7
a2
b22
c221

p8
a2
b22
c222

p9
null
b31
c311

p10
a3
null
c312

p11
a3
b31
null

p12
null
null
c314

p13
null
b31
null

p14
a3
null
null


texts: What is the minimum input length required for sql injection?

texts:
Perhaps this belongs on the 'golf code' stack or elsewhere, if so, apologies and I'll remove it.

I have been working on a older application and re-writing SQL statements as postgres functions to help avoid exposure to SQL injection. I got to thinking, that if something was in the form of

SELECT * FROM Whatever WHERE id = user_input_here

then what would be the shortest user_input_here that could allow Bobby Tables or one of his friends to do their dirty work? If the user_input string had already been constrained to a very few characters, presumably no mischief could be done. (Perhaps we know the 'id' is always exactly 6 characters.) Presumably you'd want at least one dummy character for the original query, then a semicolon before your injected code, what could be the shortest valid statement after that?

Understand that I am not advocating this as a protection method, I'm just curious.


texts: Random selection of a row within a function

texts:
I am trying to write a function so it can be used as a default value for a column in a table. It exists as a procedure at the moment (which cannot be used as a column default) and uses ORDER BY newid() several times. Apparently this cannot be used in a function.

Is there anyway of randomly selecting a row in a user defined function?


texts: Using MySQL/PHP PDO, is there a 'simulate' or dry run option?

texts:
Does MySQL (or, ideally, PHP PDO) have some way of simulating transactions without actually committing them? (Can I get MySQL or PDO to act as if data is inserted/deleted/updated, without actually committing any INSERT, DELETE or UPDATE statements?)

'Inserted' data (people IDs, business IDs, phone number Ids, phone type IDs, etc.) should be stored somewhere, so that they can be retrieved if need be. Data shouldn't be written to the main database during testing/development, only when the script is complete and ready for production use.

The reason for asking is this: In developing a data-copying/transferring script, it's inconvenient to delete the test data out of the tables and reset AUTO_INCREMENT before running the changed import script.

Alternately, should I ...

Create my tables in memory at the start of the script and delete them at the end?
Replicate the database (create a slave) for use in development and replace the slave with each modification to the script?

(There will be about 1000-2000 people imported, with up to 15 phone numbers for each person, 1 business for each person and up to 10 roles for each person.)


texts: Blocking in AG that causes app not to launch

texts:
If lag between primary and secondary is huge, then can it cause persistent blocking on app sessions? Coz recently we had an issue where blocking sessions were having wait type as HADR_SYNC_COMMIT and there was lag for one of the DB in AG.

So we failover to Secondary even though the lag was pretty high. After DB coming online on new Primary, issue got resolved.

But today the issue happened again. Can you please help.


texts: Large transactional log won't shrink after transactional backup

texts:
I'll start by saying our databases use full recovery mode and we run SQL Server 2014.

Over the last few days our backups appeared to spiral out of control to the point that our full/differential backups were failing and only extremely large transactional backups were getting created. This of course caused a disk space issue and required a fair bit of manual intervention to restore order.

I manually ran a full backup on each database and then tried to follow up by running a transaction backup. During this period, one database's log file was increasing in size to the point that it ran out of disk space. Usually the log file would be around 1 GB, it is now sitting at about 30 GB.

Running a transactional backup did not reduce the size of the log file and I was stuck in a position where the log file was full and there was no room left on the disk. As a last ditch effort, I ran the SHRINKFILE( dbname, 4096) which instantly reduced the size and all appeared to be normal.

After doing so, I noticed my backups were no longer working and I was receiving an error:

Data error (cyclic redundancy check) failed.
I tried running CheckDB and was presented with the following message:

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

Given I was unable to back the database up, I decided to try and restore from earlier in the day off a full backup and 1 single transactional backup I had created. Both these files were large. The reason I only had 1 transactional backup was because I had disabled the job from running to avoid things spiralling further out of control.

New DB is up and running and backups work fine, but the issue of the large log file still prevails. My transactional backups are very small now but I can't seem to shrink the DB.

Anyone have any ideas? Alternatively, if there's a way to resolve the redundancy issue on the original database that would be a preferable solution as I'd have no data loss then.

No open transactions. The log_reuse_wait_desc is "LOG_BACKUP" which my understanding means that it is waiting for a log backup to run. This is madness because I have done several ;) dbcc sqlperf(logspace) is claiming I'm using 92% space.

Log backups run every 15 minutes in normal circumstances, but like I mentioned, these had been failing over the weekend. I've turned them off for the time being to stop the drive filling up and have just been manually running transactional backups. The database is not live at the moment so no additional data is going in there, I've run maybe 4 transactional backups as a test in the last hour or so.

None of replication, mirroring, or AOAG is configured here.


texts: Buffer pool extensions disabled for no apparent reason

texts:
I've tested Buffer Pool Extensions on an Amazon EC2 virtual machine. The VM has a single non-persistent high-performance SSD. On VM shutdown the SSD contents are lost, so it can't be used to store business data.

In order to leverage the SSD, both tempdb and buffer pool extensions were configured to use it, not unlike what was done on Azure (save the Task Scheduler-Powershell hoop jumping). Some 100 GB BPE was allocated, tempdb data files and logs are currently around 20 GB.

The system has been running nicely for about a week. Yesterday, for no apparent reason, buffer pool extensions were disabled. None of the admins admit changing the configuration.

It looks like the default trace doesn't record BPE changes, ditto for the system_health Extended Events. Unless I missed something, would there be a way to see what triggered the change?

All the Application log and Sql Server's log contain is an entry that states the pool was disabled:

  Buffer pool extension has been disabled successfully. Deleting buffer pool extension "x:\sqli001.bpe".

Are there any other probable causes for disabling the BPE than human error?

There is a KB article about I/O errors that might cause loss of BPE, but the current patchlevel should include the fix, and there are no log entries about I/O errors anyway. The tempdb files have been autogrowing around the same time, but there was plenty of free space on the SSD.

The server is up-to-date with OS (Windows Server 2012 R2) and Sql Server patches (SQL Server 2014 SP1-CU6 X64 Enterprise Edition).


texts:
I have been asked to outline the sequence of commands using low-level operators (join/project/select) that are used in the following SQL statement;

SELECT d.name, k.city
FROM DOG d, KENNEL k
WHERE d.kennel_name = "Holiday Kennels"
AND d.kennel_name = k.name;

My understanding would be you would project d.name and k.city like this;

PROJECT d.name, k.city

However beyond this I am unsure how you would use join and select to get the clauses?

Any help would be great, thanks.


texts: Postgres how to drop table owned by another user without mirroring that user's Permissions

texts:
User A owns all of our tables and has access to all of our schemas

User B needs full access including Drop and Alter permissions to some of those tables and schemas but not all of them.

As is well documented, Drops and Alters are not possible because User B does not own the tables.

UserB => DROP TABLE userATable;
ERROR:  must be owner of relation test

The recommendation in this case seems to be to make Role B a member of Role A.

grant UserB to UserA;

But we cannot do this because then UserB will have access to schemas for which it should not.  What are recommended strategies to handle this?


texts: How to extract and store errors from Pentaho PDI

texts:
I'm using Pentaho PDI 'Spoon' version 7.1 to build an ETL package.

The existing version of the package (built in SSIS) uses the built in variable @[System::ErrorDescription] to store an error message in the event of a failure.

Does PDI contain anything similar to this? I want to wrap up whatever error message shows up in the logs and insert it into a table, but I can't find a way to reference the error.


texts: Execution Plan Basics -- Hash Match Confusion

texts:
I am starting to learn execution plans and am confused about how exactly a hash match works and why it would be used in a simple join:

select Posts.Title, Users.DisplayName
From Posts JOIN Users on
Posts.OwnerUserId = Users.Id
OPTION (MAXDOP 1)

As I understand it the results of the Top index scan become the hash able and each row in the bottom Index clustered scan is looked up. I understand how hash tables work to at least some degree, but I am confused about which values exactly get hashed in an example like this.

What would make sense me is the the common field between them, the id, is hashed -- but if this is the case, why hash a number?


texts: Dynamic paths for postgres file_fdw (csv)

texts:
I've got a postgres database which relies on the use of foreign tables to support the loading of large data sets based on user-provided (system sanitized) tables. I'd use COPY, but the tables loaded on a recurring (but non-daily) cycle and have complex load parameters which we've built into functions (using a combination of UPDATE WHERE, DELETE WHERE, TRUNCATE AND INSERT, INSERT, ON CONFLICT)

The process works well, and is stable.  While I'm generally happy with it, I realize that it opens up the possibility that at some point the file paths specified in the CREATE FOREIGN TABLE command will change (e.g. from /home/postgres/path_primary/table.csv to /home/postgres/path_secondary/table.csv).

My question: Is there a way to point the base of the file path to a non-static value to abstract that change? Or would I be better off writing a script to collect the list of foreign tables, drop them, and rebuild the DDL with new filepath arguments?


texts: COALESCE returning NULL when there are no records

texts:
When running the following query on a device_id that has no entries in the inv_zfs_replication table the result is a NULL value. How can I make the 2nd sub-query return a zero if there aren't any records?

SELECT
    (
        (
            SELECT
                SUM(s.quota)
            FROM
                `inv_zfs_share` s
            JOIN `inv_zfs_project` p2 ON s.project_id = p2.id
            WHERE
                p2.device_id = p.device_id
            GROUP BY
                p2.pool_id
        ) + (
            SELECT
                COALESCE (SUM(quota), 0)
            FROM
                inv_zfs_replication
            WHERE
                device_id = p.device_id
            GROUP BY
                pool_id
        )
    ) AS 'Quota Total'
FROM
    inv_zfs_pool p
WHERE
    p.device_id = 626


texts: Does mysql "order by" guarantees to give the same order for the same query every time if the sort key is equal?

texts: I'm planning to write a query that sorts the result by a value s and then paginate the result. Let's say I have ten items that match the query and all the items have the same s. In the first query, I sort the value by s and get the first five items. Then in the next query, I sort the value by s and get the sixth to tenth items. Is it possible that the items that appear on my first query will appear again in my second query, and that some items will not show up in either query?


texts: How do I detach all other users from a postgres database?

texts:
I need exclusive access to a database. Is it possible using an SQL command to "detach" all other users from a postgres database. Or maybe closing all other connections and then gaining exclusive access.

This is for unit testing, and tests are only run manually, so there is no danger involved. Only old dead connections will be affected.

There are no other users connecting to these unittest databases.

The old dead connections come from developing. This happens all the time when a test that is being written or fails does not exit clean.

If someone also needs to keep locked out other users for a while after disconnecting them in a production scenario, see Scott Marlowe's answer below: https://dba.stackexchange.com/a/6184/2024

See also this similar question on dba: How to drop all connections to a specific database without stopping the server?


texts: SQL Server Express 2008: Can I store database files (.mdf, .ldf) in directory other than the default DATA directory?

texts:
I have a SQL Server Express 2008 instance of which I am attaching databases from development systems quite often. Typically I transfer the .MDF and .LDF files to the instance's DATA folder and reattach, and everything is fine. In the past when we used Access instead of SQL Server Express, I would keep the database file on a separate drive (SSD) which tended to increase performance, as well as let me use BitLocker drive encryption on the whole SSD for easy encryption and whole drive backups. I would like to do the same with SQL Server Express.

Can I put the .mdf and .ldf files on a separate drive (not in the instances DATA folder) without any ill effects? Do I need to change settings through the Management Studio to do this? Can I still use BitLocker Encryption?

Thanks very much!


texts: More tables or More records performance wise

texts:
i am into a scenario that upon looking at the our project database contains Identical tables with the schema described below:
  id
  url
  region_1
  region_2
  region_3
  region_4
  date_created

My colleague creates a table in each category a url appears so the database looks like this
angular
   id
   url
   region_1
   region_2
   region_3
   region_4
   date_created

 react
   id
   url
   region_1
   region_2
   region_3
   region_4
   date_created

 asp

Same schema and it goes on and on.....
my proposed schema will look like this
sites
   id
   url
   region_1
   region_2
   region_3
   region_4
   date_created
   tech_category

This would satisfy the ORMs (Dapper/EF) on the application level to be more maintainable and will just use a single table. What is the right approach? Is it more favorable to create a single table to hold the data or is multiple tables with smaller sets of data?


texts: Integrating intranet web logins with AD emails

texts:
For various reasons, one of our corporate intranet sites in development cannot use Active Directory groups or passwords. Users logins will be stored within a MSSQL database instead.

However, we do want the ability to send email notifications to these users using their AD registered email address. All users will exist in AD.

So my general question is what is the better approach:

Use SQL to query the AD for emails when the web application wants to notify selected user(s)
Store the email addresses in the MSSQL database and update records via a batch process each night?

The first seems to have less duplication, but performance could be slower (and vice-versa). Is there another method?

Edit

To answer your questions, the emails will generally be initiated by other users. For instance, after modifying a document the editor will have the option to send a notification to be sent immediately. The plan was to have the ASP.NET application handle sending mail. In terms of volume, peak usage would be around 200-300 a day.


texts: Error: Could not read Block X of relation base/Y/Z

texts:
We have some serious issues with our PostgreSQL-Server (Version 8.4). Our webapplication uses jdbc to connect to the PostgreSQL Server. Suddenly our webapplication cant connect to the PostgreSQL Server. We get the PSQLException: Broken Pipe. Cause of this, i tried to connect via pg_admin. This works, but i'm getting errors like this:

ERROR:  could not read block 32570 of relation base/16390/2663: read
only 0 of 8192 bytes

I tried to make a dump to backup the data and this doesnt work too:
(Look on the edit at the bottom of the side)

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: could not read block 32570 of relation
base/16390/2663: read only 0 of
8192 bytes
pg_dump: The command was: LOCK TABLE
public.results_233_top100_disease_state_karyotype IN ACCESS SHARE MODE

I tried to get some information with
SELECT oid, relname FROM pg_class WHERE oid=2663

The result was this:

2663 ; "pg_class_relname_nsp_index"

During my effort of collecting some information, i read, that recreating that specific index could help. So reindexed it with the following command:
REINDEX INDEX pg_class_relname_nsp_index

This didnt helped at all, and now i'm pretty helpless. Has somebody an idea what i can do ? Another point is, that we do weekly backups. Is it possible to overwrite the data folder: /var/lib/postgresql/8.4./main with our data from the backup?
EDIT: I fixed the problem with reindexing the table that had the error. Now i'm getting another error:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: invalid page header in block 1047 of relation base/16390/16398
pg_dump: The command was: COPY public.data_1 (sampleid, feature, value) TO stdout;


texts: Strange behaviour of a query with order by and rownum on Oracle's Exadata server

texts:
I am executing the following query on two different database servers:

SELECT start_date,
  end_date,
  resort_id
FROM employee_activity
WHERE employee_id = 27
  AND start_date < to_date('2012-11-09', 'YYYY-MM-DD')
  AND is_overridden = 1
  AND ROWNUM = 1
ORDER BY start_date DESC;

This query returns different records on Exadata than on the other server.
On Exadata, it first selects the first row and then does the ordering.
On the other server, it first does the ordering and then returns the first row.

We are using Oracle 11g on both servers.

Now the issue we are facing is that we have upgraded our database servers to Exadata and we have used such queries in a lot of  places in our codebase. So can we overcome this issue?


texts: Stopping multiple server side SQL Server traces immediately

texts: A mistake in an audit script generated more than 100 traces. Is there a way I can stop all of them?


texts: Is shrinking a transaction log OK to do on a live database?

texts: In Microsoft SQL Server 2005, is it OK to shrink the log file of an online database, or will it cause an interruption of service?


texts:
I have a table with 4.7 million rows and I am executing this query on this table:

SELECT * FROM students WHERE `eid` IN
(SELECT DISTINCT `eid` FROM students WHERE code IN ('120', '330'));

This is working fine but it slows down the performance. How can I boost up performance by changing this query or how can I do the same work with some other optimized query (in better way)?


texts: MySQL How to get the nearest child for every parent in a single query?

texts:
MySQL, I need to get the nearest location for each organization. Between organizations and locations is a one-to-many relation. One organization has many locations. The GPS coordinates are stored in the locations table.

I am using this query to get minimum distance for every location. But I still need the closest location name and other fields. I also have to but a radius condition.

    SELECT
        `organizations`.`id`,
        `organizations`.`id` AS `organization_id`,
        `organizations`.`name` AS `organization_name`,
        COUNT(DISTINCT `locations`.`id`) AS locations_count,
        `locations`.`name` AS location_name,
        CONCAT(`locations`.`street`, ', ',`locations`.`city`, ', ', `locations`.`state`) as location_address,
        MIN(ROUND(ACOS(SIN(RADIANS(45.537463)) * SIN(RADIANS(locations.latitude)) +
                COS(RADIANS(45.537463)) * COS(RADIANS(locations.latitude)) *
                COS(RADIANS(locations.longitude) - RADIANS(- 122.616951))) * 3959,
            2)) AS location_distance
    FROM
        `organizations`
            INNER JOIN
        `locations` ON `locations`.`organization_id` = `organizations`.`id`

    GROUP BY `organizations`.`id`
    HAVING `location_distance` < '150'
    ORDER BY `organizations`.`name`

For now I have to make another query to get the closest location for each organization by organizations.id.

Do you think is possible to it in a single query without subqueries in the SELECT statement. It will really slow the query.
Thanks!


texts: Error when restoring Postgres Database on RDS

texts:
I am following this to restore an externally backed up Postgres DB onto my RDS instance - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html

However, when I try to restore I get the following errors -

CREATE EXTENSION
psql:database.sql:2615: ERROR:  must be owner of extension plpgsql
CREATE EXTENSION
psql:database.sql:2629: ERROR:  must be owner of extension pg_trgm

I believe this has to do with assigning superuser privileges to the user. However, I know this is not possible to do in RDS. How can I workaround this?


texts: Why does MongoDB 4.0 require journaling for replica set members using WiredTiger?

texts:
This is a very interesting change in version 4.0 since checkpoints seem to serve a very similar role to journaling.

  The checkpoint ensures that the data files are consistent up to and including the last checkpoint; i.e. checkpoints can act as recovery points.

OK so checkpoints give me a durable view of data at that last checkpoint. I lose any data after the last checkpoint in the instance of a crash.

  The WiredTiger journal persists all data modifications between checkpoints. If MongoDB exits between checkpoints, it uses the journal to replay all data modified since the last checkpoint.

Which is a little bit of extra durability seeing as the Journal tracks all operations. Its like a list of what has been done that we can reapply after failure.

  The journal record includes any internal write operations caused by the initial write. For example, an update to a document in a collection may result in modifications to the indexes; WiredTiger creates a single journal record that includes both the update operation and its associated index modifications.

Seems like the point of checkpoints was to move fast, but at the risk of losing a minute of data at a time. Whereas the journal is all about accounting for everything. So why have both? Seems like having the Journal kind of overrides any gains that Checkpoints were supposed to give.


texts: How to create columns with Count and percentage using MS Access Query

texts:
I have a data table below, I would like to create a query to produce a result table like below, with count and percentage, using MS Access Query. I am unable to create this.

Name
Alex
Alex
Bob
Bob
Roger
Tim

Below result table

 Name    Count Percentage
 Alex    2     33%
 Bob     2     33%
 Roger   1     16.6%
 Tim     1     16.6%


texts:
I want to get a table of

[file], [folder], [DateAdded], [DateRemoved]

I have a log table of changes that has

[file], [ChangeDate], [OldFolder], [NewFolder]

Files have moved in and out of folders multiple time so for the records for a file and folder I need to join the lowest date to the next highest date. I am not exactly sure how to write the SQL Query to do this.


texts: Reclaiming space from a large partitioned database

texts:
We have a database where one of the business group decided to remove several millions of rows as part of their upgrade process. (we removed for them..).. This database is partitioned based on a date and removing all these rows would help us gain around 30% of the DB size back. But obviously, if we run the shrink command, there is a possibility of the database getting fragmented and running rebuild obvious increases the space of the database. Is there any better way of reclaiming the space other than moving to a different file group as suggested by Paul Randall?.
If we go via the traditional shrink and rebuild index route to reclaim the space on several of these partitions, we will be having to put the db in simple (bulk did not help much with reindex ) to avoid the transaction log from getting filled. This obviously will break the log shipping and we will have to setup the logshipping on this VLDB database back again which would be another tedious work. For a VLDB, what would be the best way to reclaim space for few of these tables without breaking the logshipping and using the existing commands?

Thanks for your help with this


texts:
I have a table with operations that can be deleted by users.

What happens, is that I want to store deleted rows for some time, to serve as recover in case any client regrets the deletion.

One of the columns is id_user, so I had two ideas:

create another table equal to the first one to store the deleted rows (the problems is to keep them equal, so any changes in the first one I have to remember to do on the second);

or

update the rows setting id_user=-id_user, so that the records are not listed to this user but they are still related and I could recover them if I wanted (the problem is to store different stuff [valid and backup records] in the same table);

What is the best aproach?

PS: These records are called in a huge bunch of places in my application, adding another column implies in changing my aplication and that is out of question.
If that ended up being the only solution I'd rather not to add this 'disaster recovery feature'.


texts: How to convert a table into second normal form

texts:
I am really struggling to convert the table into second normal form.
I've tried examples which are much easier, however I can't seem to be able to identify the keys for this one and work my way forward. I have converted that to 1st normal form by duplicating the module information.


texts: Is the eager spool operator useful for this delete from a clustered columnstore?

texts:
I'm testing deleting data from a clustered columnstore index.

I noticed that there is a large eager spool operator in the execution plan:

This completes with the following characteristics:

60 million rows deleted
1.9 GiB TempDB used
14 minutes execution time
Serial plan
1 rebind on spool
Estimated cost for scan: 364.821

If I trick the estimator into underestimating, I get a faster plan that avoids use of TempDB:

Estimated cost of scan: 56.901

(This is an estimated plan, but the numbers in comments are correct.)

Interestingly, the spool disappears again if I flush the delta stores by running the following:

ALTER INDEX IX_Clustered ON Fact.RecordedMetricsDetail REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

The spool appears to only be introduced when there are more than some threshold of pages in the delta stores.

To check the size of the delta stores, I'm running the following query to check for in-row pages for the table:

SELECT
  SUM([in_row_used_page_count]) AS in_row_used_pages,
  SUM(in_row_data_page_count) AS in_row_data_pages
FROM sys.[dm_db_partition_stats] as pstats
JOIN sys.partitions AS p
ON pstats.partition_id = p.partition_id
WHERE p.[object_id] = OBJECT_ID('Fact.RecordedMetricsDetail');

Is there any plausible benefit to the spool iterator in the first plan? I have to assume it is intended as a performance enhancement and not for halloween protection because its presence is not consistent.

I'm testing this on 2016 CTP 3.1, but I see the same behavior on 2014 SP1 CU3.

I've posted a script that generates schema and data and walks you through demonstrating the problem here.

The question is mostly out of curiosity about the optimizer's behavior at this point as I have a workaround for the issue that prompted the question (a large spool filled TempDB). I'm now deleting by using partition switching instead.


texts: Moving the data between partitions in a table

texts:
There is one table with partition, where partition function and scheme is defined like below:

 CREATE PARTITION FUNCTION DateRangePF (CHAR(8))
  AS RANGE RIGHT FOR VALUES ('20180101','20190101')

 CREATE PARTITION SCHEME DateRangePS
  AS PARTITION DateRangePF TO (Y2018FG, Y2019FG);

There are lot of data into the partition with boundary value '20190101' and we decided to partition it into 4 parts with following boundary values and associated file groups:

'20190101' with file group Y012019FG,
'20190401' with file group Y042019FG,
'20190701' with file group Y072019FG,
'20191001' with file group Y102019FG.

Now, what could be the best strategy to switch the data from older single partition with boundary value '20190101' to these four partitions?


texts: Why does innodb_flush_method = O_DSYNC provide such a performance boost with SAN-backed storage?

texts:
I've seen some people suggest that O_DSYNC should be used with a SAN.

The MySQL docs have this to say about O_DSYNC in general:

  In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() call (which InnoDB uses by default) and other similar methods is surprisingly slow. If you are dissatisfied with database write performance, you might try setting the innodb_flush_method parameter to O_DSYNC. The O_DSYNC flush method seems to perform slower on most systems, but yours might not be one of them.

And specifically with regard to use with a SAN:

  On some systems where InnoDB data and log files are located on a SAN, the default value or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. Always test this parameter with the same type of hardware and workload that reflects your production environment.

Although they suggest one should test, the wording is so wishy-washy (i.e. "some systems, "might be") that one could be forgiven if they didn't even bother testing and stuck with the default (fdatasync). But in my (unscientific) tests I have found O_DSYNC to provide enormous performance benefit - at least with some troublesome SELECT queries.

I was wondering if anyone could explain in greater detail why this option can provide such a benefit when using a SAN. The good book of course discusses this option but not in the context of a SAN.

I'm using:

MySQL 5.1 with the InnoDB plugin on RHEL 6 (x86_64)
Virtualized environment (vSphere); SAN with FC interconnect is provided by our IaaS provider
Ext4 file-system with barrier=0 mount option
I/O Scheduler is deadline


texts: Is there a way to generate an execution plan for a stored procedure before executing it?

texts:
We have a very large (many millions of rows) table that contains some (few million) rows with invalid values in particular columns.

A stored procedure exists to correct the invalid records but it takes a really long time to run the first time. It is significantly faster after that, as you would expect once the procedure has been compiled and the plan generated and cached. Unfortunately the procedure will probably only be executed once so we don't get the benefit of any optimization the server does during the first execution.

Is there some way to tell SQL Server to compile/generate/estimate an execution plan for a stored procedure before running the procedure?


texts: Postgres Index scan forward vs backward = speed difference of 357X slower

texts:
PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-51), 64-bit

Dedicated DB server

4GB ram
Shared_Buffers = 1 GB
Effective_cache_size = 3GB
Work_mem = 32MB

Analyze done

Queries ran multiple times, same differences/results

Default Statistics = 1000

Query (5366ms) :

explain analyze
select
    initcap (fullname)
  , initcap(issuer)
  , upper(rsymbol)
  , initcap(industry)
  , activity
  , to_char(shareschange,'FM9,999,999,999,999,999')
  , sharespchange || + E'\%'
from changes
where activity in (4,5) and mfiled >= (select max(mfiled) from changes)
order by shareschange asc
limit 15

Slow Ascending explain Analyze:

http://explain.depesz.com/s/zFz

Query (15ms) :

explain analyze
select
    initcap (fullname)
  , initcap(issuer)
  , upper(rsymbol)
  , initcap(industry)
  , activity
  , to_char(shareschange,'FM9,999,999,999,999,999')
  , sharespchange ||+ E'\%'
from changes
where activity in (4,5) and mfiled >= (select max(mfiled) from changes)
order by shareschange desc limit 15

Fast descending explain analyze:

http://explain.depesz.com/s/OP7

The index: changes_shareschange is a btree index created with default
ascending order. The is index size is 32mb

The query plan and estimates are exactly the same, except desc has index
scan backwards instead of index scan for changes_shareschange.

Yet, actual runtime performance is different by 357x slower for the
ascending version instead of descending.

Why and how do I fix it?


texts:
I have the following configuration information:

audit_sys_operations
TRUE

audit_file_dest
D:\ORACLE\ADMIN\DBSERVER\ADUMP

audit_trail
DB

SQL> spool off;

This tells me that audit data for the SYS user is being saved in a database file. If the SYS user has full access to DB, does this not defeat the purpose of audit SYS files being stored on the DB server?


texts:
My default schema dbo.

I not give my users permission to create table.

User authority ; datareader,datawriter

CREATE TABLE dbo.newtable ...

  CREATE TABLE permission denied in database 'DB'

But, my users i want to be able to create a table in the other schema. How can I do that?

CREATE TABLE support.newtable ...

Thanks for your help.


texts: mysql import failed caused by overgrown general log file

texts:
I have a table about 70G in size. I took a backup using mysqldump.

In the new server, I performed a restoration.

time mysql -uroot -p TDB < TDB.sql
Enter password:
ERROR 3 (HY000) at line 58155: Error writing file '/app/mysql/admin/tmp/MLLVx92e'
(Errcode: 28 - No space left on device)

Further checked, showed that the general log file in /app has increased tremendously.

show variables 'general_log_file'
general_log_file                        | /app/mysql/admin/mysql.log

Does importing using mysql causes general log file to grow exponentially?


texts:
Not an SQL admin and new to ola-hallengren great scripts.

Just wondering if people are mostly doing a blanket user database backup with his full/diff script regardless on if a DB is in Simple or Full recovery mode, or is it preferred that Simple mode DBs and backup up separately etc.

Or, does it not even matter??


texts:
I have a filtered index like this:
CREATE INDEX idx1
   ON public.tbl1 USING btree
    (col1 COLLATE pg_catalog."default" ASC NULLS LAST, col2 ASC NULLS LAST)
    WHERE col3 IS NULL;

and a query like:
SELECT * FROM tb1 WHERE col3 is null and col4 = 12

This query scan idx1 which is fine as this index is only a few rows (max 400 or so) the underlying table is millions of rows.
The first time this query runs, it runs painfully slow (almost a minute) but the seconds time it runs it does so in 20ms.
Can someone help me understand what is happening? The index gets rebuilt every evening but seems to bloat (gets to a few hundred megs) during the day. From the behaviour it feels like it has to load data into memory and the next time its faster because the data is cached. The database is quite busy 2k tupples in and 4k tuples out.
QUERY PLAN
Index Scan using unsubmittedmsgs_1 on tbl1 s  (cost=0.38..1.88 rows=1 width=315) (actual time=145.335..39385.943 rows=101 loops=1)
  Index Cond: (created_dow = 1)
  Filter: (machine_id = 20)
  Rows Removed by Filter: 155
  Buffers: shared hit=803132 read=25838 dirtied=85580
Planning:
  Buffers: shared hit=255 dirtied=3
Planning Time: 1.759 ms
Execution Time: 39386.110 ms


texts: What is the best practice when an index with key/include columns are inversed for a second index on the same table

texts:
I have an index on a table of the form:

NONCLUSTERED INDEX [IDX1] ON [TABLE]
(
    [COL_ID_A] ASC
)
INCLUDE
(
    [COL_ID_B]
)

For a different query on the same table MSSMS proposes the following index where the key and include columns are reversed:

NONCLUSTERED INDEX [IDX2] ON [TABLE]
(
    [COL_ID_B] ASC
)
INCLUDE
(
    [COL_ID_A]
)

What is the best practice (and why)?:

one index with keys and indexes combined or
two separate indexes