texts:
I am in need of a way to return all records from both tables BUT have a where condition on ZT1 I have tried both queries below, however Query 1 ignores any row from @TTBL that does not exist in ZT1, while Query 2 ignores the WHERE condition I want to put on the table ZT1 (bc it was changed to an or)

How should my query be written in order to accomplish my desired result of all data from @TTBL AND only data from ZT1 that fall between the date criteria

    Query 1
SELECT
    Employee = COALESCE(ZT1.Employee,Z.Name),
    COUNT(ZT1.sales) AS Salecount,
    SUM(ISNULL(ZT1.[SaleTotal],0)) AS SaleTotal,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)/NULLIF(ZT1.[SaleTotal],0)) AS ActualSale,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)) AS ProfitMargin,
    ISNULL(Z.SaleTotal0,0) AS SaleTotal0,
    ISNULL(Z.SaleTotal1,0) AS SaleTotal1
FROM dbo.holdings AS ZT1
FULL OUTER JOIN @TTBL AS Z ON Z.Name = ZT1.Employee
WHERE ZT1.[SaleDate] >= '20170601'
AND ZT1.[SaleDate] <= '20170625'
GROUP BY ZT1.Employee,Z.SaleTotal0,Z.SaleTotal1,Z.Name

--Query 2
SELECT
    Employee = COALESCE(ZT1.Employee,Z.Name),
    COUNT(ZT1.sales) AS Salecount,
    SUM(ISNULL(ZT1.[SaleTotal],0)) AS SaleTotal,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)/NULLIF(ZT1.[SaleTotal],0)) AS ActualSale,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)) AS ProfitMargin,
    ISNULL(Z.SaleTotal0,0) AS SaleTotal0,
    ISNULL(Z.SaleTotal1,0) AS SaleTotal1
FROM dbo.holdings AS ZT1
FULL OUTER JOIN @TTBL AS Z ON Z.Name = ZT1.Employee
OR ZT1.[SaleDate] >= '20170601'
AND ZT1.[SaleDate] <= '20170625'
GROUP BY ZT1.Employee,Z.SaleTotal0,Z.SaleTotal1,Z.Name


texts: Use CTE to fetch most recent row for each ID

texts:
I just recently started learning about the wonders of CTEs and how they can be used, instead of WHILE loops, to optimize performance.
For context, I am automating the process of updating firmware on devices in my company. The devices send UDP data to the server and store the messages into a single table, so the table is quite large
There are 2 modules on each device, high and low frequency and the messages are very similar
Previously, I had temp tables, counters, and a while loop to get the most recent firmware message for each device on each module. I have since used the following CTE to extract the same information (only high frequency code shown, table and column names changed for confidentiality)
;WITH HFCTE (DIdentifier, CachedHFFWVersion, CurrentHFFWVersion, HFFWMessage, HFFWMessageDate) AS
(
    --Get all HF Firmware Messages for each device
    SELECT  udp.DIdentifier,
            LEFT(RIGHT(UDPMessage,7),3),
            RIGHT(UDPMessage, 3),
            UDPMessage,
            DateRecorded
        FROM UDPTable udp WITH (NOLOCK)
            INNER JOIN FirmwareInfoTable fw ON udp.DIdentifier = fw.DIdentifier
        WHERE UDPMessage LIKE '%FWMessage:%'
            AND UDPMessage NOT LIKE '%FWMessage:LF%'
            AND fw.Status = 'HF Pending'
)

-- Get the most recent HF Firmware Message for each device
SELECT cte.DIdentifier,
cte.CachedHFFWVersion,
cte.CurrentHFFWVersion,
cte.HFFWMessage,
cte.HFFWMessageDate
FROM HFCTE cte
    INNER JOIN (
                SELECT DIdentifier,
                    MAX(HFFWMessageDate) AS MaxDate
                FROM HFCTE
                GROUP BY DIdentifier
               ) AS gcte
        ON cte.DIdentifier= gcte.DIdentifierAND cte.HFFWMessageDate = gcte.MaxDate
ORDER BY cte.DIdentifier DESC

The main problem is that before picking out the most recent firmware message using the last SELECT statement, the CTE has to get all FWMessage records for each device, which could be very bad for performance during peak hours.
Is there any way to only pull the one record I need for each device in my initial WITH CTE query? Would it even optimize the overall performance of the query? Should I be using a different method entirely?
P.S. DIdentifier is a PRIMARY_KEY in the FirmwareInfo table and a FOREIGN_KEY (Not to the Firmware table but to the table that holds devices) in the UDP table. The UDP table also has 2 relevant indexes, both non-clustered & non-unique. One includes DIdentifier and UDPMessage, the other includes DIdentifier and DateRecorded. I cannot create a new index for UDP table due to performance constraints


texts: Doing a select based on a count(), a date range, and string match

texts:
I'm going to do my best here.

I Have a list of citations that were written and a and some information about those citations. I need to select the citations that have been voided in a given date range and where the person who wrote it also wrote over 5 citation in that same date range.

I want a result that shows all the citations that person 'x' wrote
citation 1 ... info,
citation 2 ... info,
citation 3 ... info,
citation 4 ... info,
citation 5 ... info,... etc

DECLARE @BeginDate Date = '3/15/2015'
DECLARE @EndDate Date = '4/1/2015'
DECLARE @MinVoids INT = 5

SELECT d.CreatorUserId

FROM [ECITATION].[dbo].[tblDocument] AS d
    JOIN [ARK].[dbo].[DocStatus] AS ds
    ON  d.DocStatus = ds.StatusNum AND (ds.StatusName LIKE 'void%')

WHERE DateModified BETWEEN @BeginDate AND @EndDate
--DateModified is when the document was voided--

GROUP BY d.CreatorUserId
HAVING COUNT(d.CreatorUserId) >= @MinVoids

This gives me the right guids but not all of them. I cannot figure out for the life of me why it's not grabbing all of them. (Should be 16 and I only get 11). I have a feeling that COUNT isn't working how I want it to.

And just because im paranoid about sounding ignorant around SQL gurus, I just started learning SQL about two weeks ago.

SOLUTION
There was some information that I left out that could have helped but the answer still helped a lot for getting it solved.

DECLARE @BeginDate Date = '3/15/2015'
DECLARE @EndDate Date = '4/1/2015'
DECLARE @MinVoids INT = 5

SELECT d.CreatorFirstName + ' ' + d.CreatorLastName as name
    ,ds.StatusName

FROM [ECITATION].[dbo].[tblDocument] AS d
    JOIN [ARK].[dbo].[DocStatus] AS ds
    ON  d.DocStatus = ds.StatusNum

WHERE DateModified BETWEEN @BeginDate AND @EndDate
    AND (ds.StatusName LIKE 'void%')
    AND ((ds.StatusNum BETWEEN 1 AND 20) OR (ds.StatusNum BETWEEN 100 AND 120))
    AND ( SELECT COUNT(*)
        FROM [ECITATION].[dbo].[tblDocument] AS dc
            JOIN [ARK].[dbo].[DocStatus] AS dst
            ON  dc.DocStatus = dst.StatusNum
        WHERE dc.CreatorUserId = d.CreatorUserId
            AND (dst.StatusName LIKE 'void%')
            AND dc.DateCreated BETWEEN @BeginDate AND @EndDate
            AND ((dst.StatusNum BETWEEN 1 AND 20) OR (dst.StatusNum BETWEEN 100 AND 120))
      ) >= @MinVoids
ORDER BY name


texts: SQL Server: are backups between same major/minor version but different build compatible among them?

texts:
For example a backup executed on SQL Server 10.50.6000.34 can always be restored on SQL Server 10.50.4000.0 or 10.50.2500.0. All of them indicates an SQL Server 2008 R2 (10.50.xxxx.xx) but different build (service pack 2,3 etc).

From some tests that I did seems that there are no problems but I want to know if this is always possible.


texts: postgres_fdw and postgres version upgrade

texts:
Case explained

I'm facing difficulties concerning performance of queries using a Postgres 9.5.4 database set up on a different server connecting via Foreign Data Wrapper to another instance of Postgres 9.5.4.

I've already tried enabling use_remote_estimate setting via ALTER SERVER but this seems not to fix it as much as I thought it would.

Regular queries with joins tend to take >3X longer when issued via FDW database.

I've investigated data transfers, gave more RAM and CPU to utilize on FDW but with no success. Anyway it seems like it's not even using that much resources.

Possible improvement

I've read about join and aggregate pushdowns for FDW introduced in Postgres 9.6 which seems great and should improve response time, but I'm unsure whether or not it's enough to just upgrade the FDW Postgres server. Do I need to upgrade the main server I'm connecting to as well?

Example

Simple example of a query that I think could benefit a lot from upgrading Postgres to 9.6 would be:

db=# EXPLAIN VERBOSE select max(add_date) from mytable;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate  (cost=2084783.99..2084784.00 rows=1 width=8)
   Output: max(add_date)
   ->  Foreign Scan on public.mytable  (cost=100.00..1963010.07 rows=48709569 width=8)
         Output: id, id_a, id_b, id_c, allowed, mod_date, add_date, idv
         Remote SQL: SELECT add_date FROM public.mytable

There is no index, and won't be, on production, but it takes around 10s to complete on master server (50M rows), while on FDW server it goes as high as 250s, which I believe is due to the fact that remote SQL executed is sending all the data to the server in order to allow it to aggregate.

Back to question

Again, to benefit from what 9.6 has to offer do I only need to upgrade Postgres instance on server that has the FDW Server and foreign tables, or also the master server?


texts: Installing SQL Server TDE on a Windows Cluster

texts:
i'm going to be upgrading two Instances to Enterprise and thenI'm going to be installing TDE on two sides of a Windows Cluster. All of the databases are on one storage device and whichever Instance is active accesses the databases from that server.

After I install TDE and encrypt the databases my users will test. If successful, I'll fail over to the secondary and upgrade it to Enterprise and then install the certificate and key.

My question is, will there be a problem with that secondary Instance since it won't have Enterprise installed yet or the certificate and key?

I hope I've provided enough information and thank you all in advance for any help you can provide.

Thomas


texts: Override default SERVICE_NAME in init.ora

texts:
I'd like to define several service names for my instance in such a way that users should not know the actual instance name. This is what I have done in my init.ora:

db_domain='company.com'
db_name='tdbket'
instance_name='tdbket'
service_names=tedab+ket.company.com, tedab.company.com, ket.company.com

By definition, the default value DB_UNIQUE_NAME.DB_DOMAIN of the service_names attribute should be overridden.

Though, the Listener still lists this default value:

Dienst "ket.company.com" hat 1 Instanzen.
  Instanz "tdbket", Status READY, hat 6 Handler für diesen Dienst...
Dienst "tdbket.company.com" hat 1 Instanzen.
  Instanz "tdbket", Status READY, hat 6 Handler für diesen Dienst...
Dienst "tedab+ket.company.com" hat 1 Instanzen.
  Instanz "tdbket", Status READY, hat 6 Handler für diesen Dienst...
Dienst "tedab.company.com" hat 1 Instanzen.
  Instanz "tdbket", Status READY, hat 6 Handler für diesen Dienst...
Der Befehl wurde erfolgreich ausgeführt.

Why?


texts: How to change the display of an empty string in mysql command line tool?

texts:
I'm maintaining a database with InnoDB tables.
These tables have some columns of type (from show create table):

`val0` varchar(30) default NULL,
`val1` varchar(30) default NULL,
etc...

From the mysql command line I am searching for NULL entries in these tables.

Now I SELECTed some rows where val0 IS NULL AND val1 = '' (empty string), but the mysql command line shows both values as

+------+------+
| val0 | val1 |
+------+------+
| NULL | NULL |
+------+------+

Apparently this is confusing me. Is this behavior intentional? Is there a method to define how empty strings are displayed?

EDIT :

I should mention that STRICT MODE (see the docs) seems to be enabled.

The query

SELECT * FROM table WHERE val0 IS NULL AND val1 = '';

finds the row above. The query

SELECT * FROM table WHERE val0 IS NULL AND val1 IS NULL;

doesn't find that row. Still the '' is displayed as 'NULL'. How do I change that?

Server version is "5.0.22-log".

EDIT 2 :

Here is the full (anonymized) SHOW CREATE TABLE mytable:

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL,
  `time` int(20) NOT NULL,
  `val0` varchar(30) default NULL,
  `val1` varchar(30) default NULL,
  `val2` varchar(30) default NULL,
  `val3` varchar(30) default NULL,
  `val4` varchar(30) default NULL,
  `val5` varchar(30) default NULL,
  `val6` varchar(30) default NULL,
  `val7` varchar(30) default NULL,
  `val8` varchar(30) default NULL,
  `val9` varchar(30) default NULL,
  `status` tinyint(4) default '0',
  UNIQUE KEY `idtime` (`id`,`time`),
  KEY `status` (`status`),
  KEY `id` (`id`),
  KEY `time` (`time`),
  CONSTRAINT `mytable_ibfk_1` FOREIGN KEY (`id`) REFERENCES `idtable` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Here are my queries:

select * from mytable where val1 is null limit 1;

-> no result

select * from mytable where val1 = '' limit 1;

result:

+-----+------------+------+------+------+------+------+------+------+------+------+------+--------+
| id  | time       | val0 | val1 | val2 | val3 | val4 | val5 | val6 | val7 | val8 | val9 | status |
+-----+------------+------+------+------+------+------+------+------+------+------+------+--------+
| 300 | 1346855716 | 0    | NULL | 42   | 4711 | blib | blub | NULL | NULL | NULL | NULL |      5 |
+-----+------------+------+------+------+------+------+------+------+------+------+------+--------+

fun fact: These last four NULLs are actually the string "NULL". (I'm only maintaining this system, I didn't build it...)


texts: Generating a password that matches the Password Validation Component

texts:
MySQL 8 has a pretty sophisticated "Password Validation Component" (this replaces the validate_password plugin). It has a lot of options,

validate_password.number_count
validate_password.mixed_case_count
validate_password.special_char_count
validate_password.dictionary_file
validate_password.length

Given that these can all be set by the user, is there mechanism guaranteed to generate a random password that satisfies this? Is the only way to write a UI that changes the password to suspend the validation component when you set it?


texts: Debugging Postgres error: "could not send data to client: Broken pipe"

texts:
I'm running a Django 1.8 site, served by nginx and Gunicorn, with a Postgres 9.4 backend.

When I make lots of data-intensive calls to the site's API in succession, I've noticed about 1 in 100 queries failing, and serving up nginx 404 pages.

My first assumption was that the Postgres database was creaking under the load, but the Postgres logs in fact show me this:

2015-08-06 14:33:43 UTC:127.0.0.1(42287):dbuser@dbname:[3737]: LOG:  could not send data to client: Broken pipe
2015-08-06 14:33:43 UTC:127.0.0.1(42287):dbuser@dbname:[3737]: STATEMENT:  SELECT ... (psql statement)
2015-08-06 14:33:43 UTC:127.0.0.1(42287):dbuser@dbname:[3737]: FATAL:  connection to client lost
2015-08-06 14:33:43 UTC:127.0.0.1(42287):dbuser@dbname:[3737]: STATEMENT:  SELECT ... (psql statement)

I think what this is saying is that Postgres is actually running the queries OK, but that it can't send data through to the client.

So I think to debug this problem, I should actually look at Django/Gunicorn/nginx instead.

Have I understood this correctly?


texts: Must be superuser after GRANT SELECT ON pg_settings(PostgreSQL)

texts:
PostgreSQL - 9.6

I connect as postgres to userdb, add grant select pg_settings for user.

➤ psql://postgres@postgresql-96.local:5432/postgres

# \c userdb
You are now connected to database "userdb" as user "postgres".

➤ psql://postgres@postgresql-96.local:5432/userdb

# GRANT SELECT ON pg_settings TO user;
GRANT
Time: 7.728 ms

➤ psql://postgres@postgresql-96.local:5432/userdb

# \q

After i connect as user to userdb and try get hba_file

psql -h postgresql-96.local -p 5432 -U user -d userdb
Password for user user:
psql (9.6.6)
Type "help" for help.

userdb=> SHOW hba_file;
ERROR:  must be superuser to examine "hba_file"
userdb=>

userdb=> select setting from pg_settings where name='hba_file';
 setting
---------
(0 rows)

But i cannot show, get, view hba_file.
How get hba_file as user?
Thank you


texts:
I took the backup of mysql database using mydumper command:

  mydumper -B mydb -o /backup/oct3 -s 500000 -c -m -S /tmp/mysql.sock -u
  root -p password@123 -v 3

Now, I am restoring mysql database backup with the myloader command:

  myloader -d /backup/oct3 -S /tmp/mysql.sock -u root -p password@123 -v
  3

ERROR:

  CRITICAL **: Error restoring mydb.tabe1 from file mydb.table1.sql.gz:
  Table 'mydb.table1' doesn't exist
  ** Message: Thread 2 shutting down

I believe the table should be created while restoring the database.

But I could not figure out what I missed. The mydumper version is 0.6.2.

My.cnf file:

innodb_buffer_pool_size = 2308M
innodb_data_home_dir = /database
innodb_data_file_path = ibdata1:1G;ibdata2:1G;
innodb_log_file_size=1073741824
innodb_log_files_in_group = 3
innodb_flush_method=O_DSYNC
innodb_file_per_table = 0

The dumped .gz files are from MySQL 5.5 and I am restoring to MySQL 5.6.

Does it matter of version incompatibility?

Any suggestions will be appreciated. Thanks.


texts:
How can I solve this live without losing database information? There should be no harm to the databases in it. I tried many methods but I could not get results.
mariadb.service - MariaDB 10.1.44 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Wed 2020-07-01 15:10:38 UTC; 3s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 15200 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 15079 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exi
  Process: 15073 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 15062 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
 Main PID: 15200 (code=exited, status=1/FAILURE)
   Status: "MariaDB server is down"

Jul 01 15:10:38 89-252-131-111.plesk.page systemd[1]: Starting MariaDB 10.1.44 database server...
Jul 01 15:10:38 89-252-131-111.plesk.page mysqld[15200]: 2020-07-01 15:10:38 140368360356992 [Note] /usr/sbin/mysqld (mysqld 10.1.44-MariaDB-0ubuntu0.18.04.1) starting as process 15200 ...
Jul 01 15:10:38 89-252-131-111.plesk.page mysqld[15200]: 2020-07-01 15:10:38 140368360356992 [Warning] An old style --language or -lc-message-dir value with language specific part detected: /usr/share/mysql/Eng
Jul 01 15:10:38 89-252-131-111.plesk.page mysqld[15200]: 2020-07-01 15:10:38 140368360356992 [Warning] Use --lc-messages-dir without language specific part instead.
Jul 01 15:10:38 89-252-131-111.plesk.page mysqld[15200]: 2020-07-01 15:10:38 140368360356992 [ERROR] Can't read from messagefile '/usr/share/mysql/English/errmsg.sys'
Jul 01 15:10:38 89-252-131-111.plesk.page mysqld[15200]: 2020-07-01 15:10:38 140368360356992 [ERROR] Aborting
Jul 01 15:10:38 89-252-131-111.plesk.page systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Jul 01 15:10:38 89-252-131-111.plesk.page systemd[1]: mariadb.service: Failed with result 'exit-code'.
Jul 01 15:10:38 89-252-131-111.plesk.page systemd[1]: Failed to start MariaDB 10.1.44 database server.


texts:
I have a table for author names with two fields, first_name & last_name, both varchar(55), both nullable (for crazy business logic reasons) although in reality last_name is unlikely to ever be null.

My where clause for the search screen contains:

WHERE CONCAT(a.first_name, " " , a.last_name) LIKE "%twain%"

so that "Twain" or "Mark Twain" can be searched on. The table has about 15,000 rows & is expected to gradually grow, but won't ever be more than double that, and not for years.

I understand that there are many other parts of my query that will affect this, but given just that information, how might I best index this?

If it would make a great difference in performance, making last_name not nullable is an option, but not first_name

TIA!


texts: MySQL how to migrate to new server without data lost?

texts:
I'm using AWS RDS, with 1 master & 1 slave.

Just now I alter 1 of the table and hangup halfway, so I force stop the process. Now the database got some problem.

Let say I wanted to instantiate a new RDS instance and migrate the whole DB over (around 1TB++), how can I fully migrate, because every seconds got new data comes in.

The problem now is because if want to copy the DB over, estimated around 30 minutes. What about the data in this 30 minutes during the migration?

Any idea?


texts: executing shell (bash) commands from within the mongo client

texts:
How can I execute shell (bash) commands from within the MongoDB client?

With MySQL client:

  system    (!) Execute a system shell command.

Is there something similar for MongoDB?


texts: Is it possible to automatically create a database after a postgres one click install (from Enterprise DB)?


texts: combine rows from table with NUM columns

texts:
I have this table:

Product | Qtdy | Price
A       | NULL | 123.00
A       | 23   | NULL

How Can I represent convert like that?

Product | Qtdy | Price
A       | 23   | 123.00

Which means group by product using Null to return value print in the next line?

Thank you


texts: How do I query for system views in a SQL Server database?

texts:
I have some queries that run against Query Store, the view sys.query_store_wait_stats is not in SQL Server 2016, but it is in SQL Server 2017.  I want my queries to work in SQL Server 2016 and SQL Server 2017+.

To accomplish this I used IF EXISTS as below

EXEC sp_query_store_flush_db;

SELECT * INTO Admin.dbo.query_store_runtime_stats FROM sys.query_store_runtime_stats;
SELECT * INTO Admin.dbo.query_store_runtime_stats_interval FROM sys.query_store_runtime_stats_interval;
SELECT * INTO Admin.dbo.query_store_plan FROM sys.query_store_plan;
SELECT * INTO Admin.dbo.query_store_query FROM sys.query_store_query;
SELECT * INTO Admin.dbo.query_store_query_text FROM sys.query_store_query_text;
IF EXISTS(select * FROM sys.views where name = 'query_store_wait_stats') -- View not in SQL Server 2016
    Begin
    SELECT * INTO Admin.dbo.query_store_wait_stats FROM sys.query_store_wait_stats;
    End
SELECT * INTO Admin.dbo.query_context_settings FROM sys.query_context_settings;

Which would seem like a good solution, EXCEPT that, the below does not return any results on SQL Server 2017 database with Query Store active

select * FROM sys.views where name = 'sys.query_store_wait_stats'

Microsoft has this answer: How do I find all views in a database?

USE <database_name>;
GO
SELECT name AS view_name
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,OBJECTPROPERTYEX(object_id,'IsIndexed') AS IsIndexed
  ,OBJECTPROPERTYEX(object_id,'IsIndexable') AS IsIndexable
  ,create_date
  ,modify_date
FROM sys.views;

But it only returns user views, NOT system views.

A couple of simple tests also do not return the system view, which is present. You can try them without a WHERE and not get any system views.

select * FROM sys.views where name = 'sys.query_store_wait_stats'

SELECT *
   -- DISTINCT NAME
FROM SYS.OBJECTS
WHERE TYPE IN ('U','V')
AND NAME= 'query_store_wait_stats'

I could test for SQL Server version, but If the view gets added to SQL Server 2016 in a Service Pack, I want my query to have only checked for the presence of the view.

How do I find (or test for) the presence of a system view?


texts: Check 1 Table If No Data Check Second Table

texts:
I am passing into a stored procedure two variables @begindate & @enddate.  I need to check firsttabletocheck and get a count of where csdate is between @begindate and @enddate.

I then need to check and see if any of the dates between @begindate & @enddate Not Exists  in firsttabletocheck I need to query secondtabletocheck to get a count from that.

I can write my code to do 1 table, but not quiet sure how to write it to do the other.  For example this is my current syntax that only checks 1 table, how can I add in the check for the second table?

Declare @d1 datetime, @d2 datetime, @numreturned int
Set @d1 = '01/01/2015'
Set @d2 = '01/12/2015'
Set @numreturned = 0

Set @numreturned = (Select COUNT(*)
                    FROM firsttabletocheck
                    where csdate BETWEEN @begindate and @enddate)

How can I add my not exist here to check if the date does not exist in the above table then check this table?

For example let's say that all dates in the date range exist in firsttabletocheck EXCEPT 01/11/2015 then I need to query the second table to check if 01/11/2015 exists in that table.


texts: Why is ADD CONSTRAINT ... NOT VALID taking a long time?

texts:
I added NOT NULL constraints to some columns like this, expecting the command to be very fast because of NOT VALID. However, it took about 60-100 seconds, and seemed to lock the table for the whole time (based on other things failing during that time). This is on a table with about 8,600,000 rows. Why did this happen? Would it have been better to do 4 commands instead of 1?

Postgres 12

ALTER TABLE my_table
  ADD CONSTRAINT my_table_column1_not_null CHECK (column1 IS NOT NULL) NOT VALID,
  ADD CONSTRAINT my_table_column2_not_null CHECK (column2 IS NOT NULL) NOT VALID,
  ADD CONSTRAINT my_table_column3_not_null CHECK (column3 IS NOT NULL) NOT VALID,
  ADD CONSTRAINT my_table_column4_not_null CHECK (column4 IS NOT NULL) NOT VALID;


texts:
Is using XLOCK (Exclusive Lock) in SELECT statements considered bad practice?

Let's assume the simple scenario where a customer's account balance is $40. Two concurrent $20 puchase requests arrive. Transaction includes:

Read balance
If customer has enough money, deduct the price of the product from the balance

So without XLOCK:

T1(Transaction1) reads $40.
T2 reads $40.
T1 updates it to $20.
T2 updates it to $20.

But there should be $0 left in the account.

Is there a way to prevent this without the use of XLOCK? What are the alternatives?


texts: Two Intel E5-2643 (4 cores each) or single E5-2690 (8 cores) for Oracle 11.2.0.3 on Oracle Linux

texts:
I have quite specific question about Oracle database  (11.2.0.3) and Intel processors. We just upgraded our main database server to big Linux box with 192GB of RAM (150 GB dedicated for Oracle). After the upgrade we found that the system is now CPU constrained (it was IO constrained before).

The machine is DELL R620 with single Intel Xeon E5-2643 processor and 192GB or RAM.

We want to go to 8 cores, so now we have two options:
1. We can buy second E5-2643
2. We can buy a single 8 core processor, e.g. E5-2690.

We don't have experience with multi cpu systems, so decision is not easy for us.

After analysis we found that second E5-2643 could be better because:

it is faster (3.3GHz vs 2.9GHZ of E5-2690)
we get two memory controllers - so we double the theoretical memory bandwidth

E5-2690 could be better because:

we have a single processor, so all memory would be local, so access to memory would be equally fast for all requests, whereas in case of two E5-2643 we would have to split memory between two processors (NUMA nodes), so for each processor half of memory would be remote, so access to this memory would be slower (please keep in mind that our SGA is 140GB, so it would be located on both NUMA nodes).

Do you have any suggestions, which configuration should behave better with Oracle (the price of processor or power consumption does not matter).


texts: Does shrinking a SQL Server transaction log remove transactions from the log?

texts:
I know shrinking the log is counter-indicated and has been covered many times, but I just encountered the following, baffling scenario:

We had a run-away query attempt to delete so many records that the transaction log file grew to 30GB on a Windows Server 2008-R2 machine running SQL Server 2008 with the database set to FULL recovery mode. My boss ran a script to backup the database once and then shrink the transaction log to the point where transaction log file was just 1024K. He says the size of the transaction log did not shrink by executing the backup and had the shrink run in a loop to decrease the unused space. How can it reduce the size of the file 30,000 times by just cleaning up unused space? Are we losing useful data by shrinking the log?


texts: MySQL condition filtering estimate while planning includes `A.col op B.col` but excludes `A.col op val or B.col op val`

texts:
In the condition filtering feature that MySQL introduced from version 5.7, has filter estimates for various condition formats in the join order, to estimate the number of rows that enter the join order pipe line after joins.
Assume a query joining three tables A, B and C, and the criteria is A.col_a > B.col_b and C.col_c > 10.
If the join order for this query is A->B->C then, the query plan, attaches the heuristic guesstimate of 33.33 percent after both A and B are in the plan at the stage of A->B.
But if a query joining the same three tables A, B and C, had criteria such that (A.col_a = 3 or B.col_b = 17) and C.col_c > 10, the combined criteria (A.col_a = 3 or B.col_b = 17) is added to the attached conditions at the stage in join order when A->B but the filter condition percent is just 100. I suspected it to be following the probability theory and a filtering percentage of 55.55.
P(A.col_a = 3 or B.col_b = 17) = P(A.col_a = 3) + P(B.col_b = 17) - P(A.col_a = 3) * P(B.col_b = 17)
P(A.col_a = 3 or B.col_b = 17) = 0.3333 + 0.3333 - 0.1111
P(A.col_a = 3 or B.col_b = 17) = 0.5555

Why is this not applied in the planning? I see the documentation says:

A condition contributes to the filtering estimate only if:

It refers to the current table.

It depends on a constant value or values from earlier tables in the join sequence.

It was not already taken into account by the access method.

Why have they not included criteria that also refers to tables that are already in the join order also? Why is the case A.col_a op B.col_b case included but not A.col_a op val or B.col_b op val not included in the rows estimation method?


texts: Using a trigger to check if a row exists in mysql -- why so slow?

texts:
I am using a trigger to check if a row exists. The code below works, but it is very very slow. I suspect that it has to do with the way I am handling NULLs.
Any suggestion to optimize it?
DELIMITER $$
CREATE TRIGGER Before_Update
BEFORE UPDATE ON mytable
FOR EACH ROW
BEGIN
  IF (EXISTS(SELECT 1 FROM mytable WHERE
      (Area = NEW.Area)
      and (Variavel = NEW.Variavel)
      and (`Data` = NEW.`Data`)
      and (Safra = NEW.Safra or (Safra is Null and NEW.Safra is Null))
      and (Futura = NEW.Futura or (Futura is Null and NEW.Futura is Null))
      and (Fonte = NEW.Fonte or (Fonte is Null and NEW.Fonte is Null))
      and (Localidade = NEW.Localidade or (Localidade is Null and NEW.Localidade is Null))
      and id != NEW.id
  ))
  THEN
    SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'ERROR! ROW EXISTS';
  END IF;
END$$


texts: Running out of disk space, start saving new data to different disk

texts:
I'm currently at around 7 billion rows on one table and approaching the max disk space for the entire database.

I need to start saving new data on the one large table to a new disk.

I've looked at partitioning by datetime, which seems the obvious choice
This is an interim solution before we get a new server in 6 months, but need a "now" solution.

How would I achieve this? Can I implement manual partitioning so all new data goes to the new disk?

Everything I've seen requires creating something like a monthly option, whereas I just want to put all new stuff on a new disk.

One option is to split the existing data by month, and then manually move the partitions around.

I'm not even sure if this is possible, so would appreciate some feedback on the best solution to achieve the above.

SQL Server 2017 Enterprise Edition.


texts: Recent patches for vulnerability attacks for SQL Server 2014 SP2 version

texts:
Keeping in mind the recent vulnerability attacks patches like Spectre and Meltdown , I would like to patch my SQL Server 2014 SP2 (12.0.5000)

1.Description of the security update for SQL Server 2014 SP2 CU10: January 16, 2018 - SP2 CU10   KB4057117  12.0.5571.0
2.Description of the security update for SQL Server 2014 SP2 GDR: January 16, 2018 - SP2 GDR   KB4057120  12.0.5214.6

Can anyone help me for below questions:

Recently we patched our Windows 2012 R2 Standard servers . Is OS Patching different from SQL Sever patching or these patches are already included during monthly OS patching installed ?
Since we have both CU10 and GDR , should I apply both ?
Which one should I need to first apply GDR or CU 10 ?


texts: Getting two or more column values through a Cursor SQL Server

texts:
I have two tables like this:
Table Experiment  (I will just use one roadID (rID) for now)

rID | latBegin |longBegin |latEnd   |longEnd
041 | 23.875464|-80.456798|30.549879|-83.5465521
041 | 33.776563|-81.157526|33.248261|-81.2468425

For each of the above rows I want the cursor to get the rID, latBegin,longBegin and check the distance from the lat and long in
Table ExperimentDetails

rID | lat      |long      |temp |direction|tension
041 | 33.665632|-81.137952|56.23|L        |54.54
041 | 23.245632|-80.127952|56.23|L        |23.54
041 | 23.454555|-80.232456|66.23|L        |13.54
041 | 23.568763|-80.346586|26.23|L        |63.54
041 | 23.796545|-80.446586|86.23|L        |83.54
041 | 23.996545|-80.946586|96.23|L        |22.54

The steps for the query are:
Step 1: Get latBegin and longBegin for a specific rID from Table Experiment
Step 2: Go to ExperimentDetails and run this calculation to get the distance difference for each lat and long in the Details table

Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000

Step 3: Get the minimum value for this new associated column in Details Table
Step 4: Get the associated temp,direction, and tension values for that minimum value and place it in a new table (Not done yet> Help needed)
Step 5: Get the average of temp, tension for the previous 30 feet or 360 inches from the min value and place it in another table (columns: rID, latBegin,longBegin,avgTension,avgtemp Not done yet>Help needed)

This is my code so far:

      DECLARE @rID int,@latBegin decimal(15,10),@longBegin decimal(15,10),@minV decimal(15,10),@temp decimal(15,10)

    DECLARE MY_CURSOR CURSOR
      LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
    SELECT DISTINCT rid,latbeg,longbeg
    FROM dbo.Experiment
    WHERE rID='041'

    OPEN MY_CURSOR
    FETCH NEXT FROM MY_CURSOR INTO @rID,@latBegin,@longBegin
    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @minV=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) over (Partition by rID Order by rID)
, @temp=temp

        FROM dbo.ExperimentDetails
            WHERE rid='041'

        Insert into dbo.Test(rid,lat,lon,min,temp) values   (@rid,@latBegin,@longBegin,@minV,@temp)

        FETCH NEXT FROM MY_CURSOR INTO @rID,@latBegin,@longBegin
    END

    CLOSE MY_CURSOR
    DEALLOCATE MY_CURSOR

The Test table looks like this:

rID|lat      |long      |min |temp
041|23.875464|-80.456798|12.1|96.23
041|33.776563|-81.157526|11.0|96.23

The temp data is repeated for the last row, which is not correct

It should look like this:

rID|lat      |long      |min |temp
041|23.875464|-80.456798|12.1|83.54
041|33.776563|-81.157526|11.0|54.54

I have given as much information as a I could, but if you need more please ask, would appreciate any help, been banging my head on this since yesterday. Currently I'm trying to bring in temp, direction, and tension in addition to @minV, but it does not let me do it

For Step 5:
The calculation in the cursor looks like this in theory for Details table:

rID |lat       |long      |temp |DISTDIFF
041 | 23.245632|-80.127952|56.23|372.12
041 | 23.454555|-80.232456|66.23|300.22
041 | 23.568763|-80.346586|26.23|50.48
041 | 23.796545|-80.446586|86.23|12.10

Then when inserting I check the minimum value i.e. 12.10 and insert the associated values with that row.
For step 5, I need to find the value closest to 360 BEFORE the minimum value not after (there might be other values closer after), which in this case is 372.12 and then take the average of temp like this:

rID |minlat    |minlong   |lastlat  |lastlong  |Avgtemp
041 | 23.796545|-80.446586|23.245632|-80.127952|58.73

Using Sql Server 2014


texts:
I have this query

INSERT INTO interactions(
    user_id, external_object_id, origin, interaction_type,
    interaction_sub_type, published_at, origin_id, created_at, updated_at)

SELECT find_user(id), find_external_object(external_object_id), 'facebook' as origin,
    interaction_type, interaction_sub_type, published_at, origin_id,
    current_date, current_date
from legacy_table

The legacy_table have around 9 million records, and it is running for around 20 hours now and find_user and find_external_object are functions that look for the return the id of an user and an external object (of course) based on id from legacy table, like this

SELECT legacy_user.uid into uid
    from legacy_user
    where legacy_user.id = $1;

SELECT users.id into user_id
    from users
    where users.uid = uid;

RETURN user_id;

What can I do to improve the query?


texts: Building test site, update dates (back date records)

texts:
I have 3 tables that all connect and I need to back date them in an MS SQL 2008 setting.
I was thinking about how to do this and not sure if I am on the correct path.  The big problem I have is I have columns that are null, and I want to keep them null if they are.  Otherwise I want to adjust the date by 1,2,3 days depending on value....
  Here is what I am trying, it adjusts the 2nd and 3rd tables, but the first I assume is failing and I am not understanding why.

    BEGIN TRANSACTION
declare @NumberOfDays int = -1
declare @Subject varchar(70) = 'Daily Nurse Rounding -1'
declare @newSubject varchar(70) = 'Daily Nurse Rounding'
UPDATE hdIssues
SET hdIssues.IssueDate = IsNull(hdIssues.IssueDate,DATEADD(day,@NumberOfDays,hdIssues.IssueDate)),
hdIssues.StartDate = IsNull(hdIssues.StartDate, DATEADD(day,@NumberOfDays,hdIssues.StartDate )),
hdIssues.ResolvedDate = IsNull(hdIssues.ResolvedDate, DATEADD(day,@NumberOfDays,hdIssues.StartDate )),
hdIssues.LastUpdated = IsNull(hdIssues.LastUpdated, DATEADD(day,@NumberOfDays,hdIssues.StartDate )),
hdIssues.DueDate = IsNull(hdIssues.DueDate, DATEADD(day,@NumberOfDays,hdIssues.StartDate )),
hdIssues.AcknowledgeDate = IsNull(hdIssues.AcknowledgeDate, DATEADD(day,@NumberOfDays,hdIssues.StartDate ))
Where
hdIssues.Subject = @Subject

UPDATE hdComments
SET hdComments.CommentDate = DATEADD(day,@NumberOfDays,hdComments.CommentDate)
WHERE hdComments.IssueID in(select hdIssues.IssueID from hdIssues where hdIssues.Subject = @Subject)

UPDATE hdStatusChangeHistory
SET hdStatusChangeHistory.ModifiedDateTime = DATEADD(day,@NumberOfDays,hdStatusChangeHistory.ModifiedDateTime)
WHERE hdStatusChangeHistory.IssueID in(select hdIssues.IssueID from hdIssues where hdIssues.Subject = @Subject)

UPDATE hdIssues
SET hdIssues.Subject = @newSubject
where hdIssues.Subject = @Subject


texts:
As part of a manual replication process between databases with different but related schemas, for each table, we identify new and modified records in the source database and feed them via a table valued parameter to a stored procedure in the SQL Server destination database. The source database is not SQL Server and is in a different data center.

For each row to be upserted, the stored procedure queries for the primary keys of the related rows already in the destination database.

Currently, we do a single SELECT to get the primary keys, followed by a single MERGE to perform the upsert. There are two aspects of this approach that I believe may not be as efficient as possible.

An implicit transaction unnecessarily wraps the MERGE. The database would remain consistent even with each row being upserted one at a time. If an row's upsert fails, we want the remaining rows to proceed.
MERGE interleaves inserts and sets as it goes through the rows, which is fine, but we don't need this. It would be acceptable to set all the modified rows and subsequently insert all the new rows.

Based on the flexibility we have, the MERGE performance tip to use UPDATE and INSERT seems to apply to our case:

  When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements.

Do I understand that right? Am I better off with a separate UPDATE and INSERT? And what about the implicit transaction? Is performing a single SELECT, UPDATE, and INSERT over a large batch of rows most efficient, or is it better to take advantage of the ability to do one row at time by using a FOR loop? Or something else?

In general, what is the most efficient way to upsert a large batch or rows to a SQL Server table in which the rows are not transactionally related and sets and inserts need not be interleaved?


texts: DB2 ROLLFORWARD option "TO END OF LOGS" - does it read archive logs?

texts:
I read about ROLLFORWARD option on link http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001978.html?cp=SSEPGG_9.7.0%2F3-6-2-4-106 but I could not find answer to my issue.

I have database in Archive mode.

I am using DISK option in LOGARCHMETH1

I made full ONLINE backup at Monday at 23:59

I have archive logs for Tuesday, Wednesday and Thursday.
Let's say just for example one for each day: S0000001.LOG and S0000002.LOG and S0000003.LOG.

In Friday I have active log S0000004.LOG.

Let's say just for testing purpose I want to do the restore of Online backup made at Monday and all transactions committed during  Tuesday, Wednesday,Thursday and Friday. So I want to restore everything from Monday to Friday

I thought by reading all manuals that I just need to perform after restore ROLLFORWARD TO END OF LOGS

db2 rollforward db maxdb75 to end of logs and complete;

I thought that this command to end of logs it will include Archive Logs(S0000001.LOG + S0000002.LOG + S0000003.LOG.)+Active Logs(S0000004.LOG)

But one good database expert told me that: "When rolling forward to end of logs without specifying overflow log path only active logs will be read"

So I have 2 questions:

What in my example will be restored in case of "to end of logs" command
Is this the command if after the restore I want to rollforward everything from Monday To Friday in my exampe: db2 rollforward db maxdb75 to end of logs and complete overflow log path "(/data/archiveLogsPath)";

Thank you in advance!!!!


texts:
Is it possible to exclude the master data from a mysql dump? From the manual it seems like the only options are commented out or present. I don't need the data at all because it is going to a static system. Commented out will work but just wondering if 0 or some other value would make it not present?

  Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating after you load the dump file into the slave.

  If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.

-https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_master-data

My plan was to use:

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=0 ...

but from the above entry that would put the CHANGE MASTER TO as an uncommented command.


texts: Evenly select Records on categorical column with Repeating Sequence and pagination in Postgres

texts:
Database: Postgres
I have a product(id, title, source, ...) table which contains almost 500K records.
An example of data is:
| Id | title    | source   |
|:---|---------:|:--------:|
| 1  | product1 | source1  |
| 2  | product2 | source1  |
| 3  | product3 | source1  |
| 4  | product4 | source1  |
| .  | ........ | source1  |
| .  | ........ | source2  |
| x  | productx | source2  |
|x+n |productX+n| sourceN  |

There are are 5 distinct source values. And all records have source values random.
I need to get paginated results in such a way that:
If I need to select 20 products then the results set should contain results equally distributed based on source and should be in a repeating sequence. 2 products from each source till the last source and again next 2 products from each source.
For example:
| #  | title    | source   |
|:---|---------:|:--------:|
| 1  | product1 | source1  |
| 2  | product2 | source1  |
| 3  | product3 | source2  |
| 4  | product4 | source2  |
| 5  | product5 | source3  |
| 6  | product6 | source3  |
| 7  | product7 | source4  |
| 8  | product8 | source4  |
| 9  | product9 | source5  |
| 10 |product10 | source5  |
| 11 | ........ | source1  |
| 12 | ........ | source1  |
| 13 | ........ | source2  |
| 14 | ........ | source2  |
| .. | ........ | .......  |
| 20 | ........ | source5  |

What is the optimized PgSql query to achieve the above scenario considering the LIMIT, OFFSET, sources can increase or decrease?


texts:
So I have a ton of transaction logs in pipe-separated format spread across a few dozen files. Concatenated into one file, it's around 2.4 million lines.

Each transaction has a store number and a company name and ~30 fields total. Right now, I have all 2.4m records in ONE table with no indexes. A simple query takes like 30 seconds to run and it's honestly unacceptable to me.

The thing is, there are no unique pieces of data that I can create primary keys with. Each store can have multiple transactions and each transaction number can have multiple lines (multiple products, returns, etc). There are 300+ stores and so a table for each store doesn't really seem realistic since I would have to join all of these just to get the data I need. What can I do?


texts: Mysql Slave doesn't find binary log from slave

texts:
I have to setup a MySQL slave on an other infrasctructure. It will become the Master once that other infrasctructure is ready.

Mysql 5.6.29
Slave A in old infrasctructure
Slave B in new one

I dumped the Slave A database by performing a STOP SLAVE;, FLUSH TABLES WITH READ LOCK then the mysqldump and restarted the slave with  START SLAVE.
I've now a perfect dump of my slave A with the master binlog and its position.

I dumped the dump into the Slave B and setup the correct binlog and position for the replication.
Once started, the Slave B couldn't find the binlog. But they are all in the right /var/lib/mysql folder on the Slave A. When I say they are all there I mean I put them myself in the folder in fact, otherwise they are deleted (I don't know why).

I really don't understand what I'm missing here.

log-slave-updates is ON on the Slave A


texts: Mapping and Inserting Values into a Specialized table

texts:
For a system i'm designing,i have one parent entity,Computer

Computer(computer_id,computer_processor,computer_hddrive,computer_memory);

A computer can be of two types,Desktop and Laptop,each having their own specific attributes.

Laptop(laptop_id(Foreign key of computer_id),laptop_color,laptop_battery);

and similarly a table for Desktop.

Now the entries to these tables is via a end-user form,my main question is,
what would the exact INSERT queries be for adding say to the inventory,a laptop if i were to have 3 tables,one superclass and two subclass tables?
I understand i would first how to make an entry to the computer table and then laptop,but how will this be carried out automatically?


texts: How to change the datatype of column without deleting it indexes;

texts:
I am looking for changing the datatype of a field from nvchar(20) to nvchar(MAX), but it retains the below message.
my questions here:
Deleting the index will affect on the data?
Can I restore the index after deleting it and changing the data type?
Is The index in the screenshot below the only index that will be deleted?
I will be very appreciated for any help :)
Best,
Lubna


texts:
Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?

The naive approach would be to inspect each record, executing a regular expression or partial string match on the field of interest, but I have a feeling (hope) that these systems do something smarter.


texts: PostgreSQL IN query performance on a larger array

texts:
I'm fairly new to PostgreSQL and have a question about query performance.
I get a set of values by parsing a file and want to count how many of them already exist in a specific column in my database (PostgreSQL). Currently I do this by parsing the whole file, storing its values in an array and then executing an IN query as follows:
SELECT COUNT(DISTINCT(id)) FROM "account"
WHERE name IN ('name1', 'name2', 'name3');

The column "name" which is being queried has a unique index on it.
I am wondering if this approach would be optimal if the array grows larger (20.000+ values) and/or I execute the query on a table with 40.000+ existing rows. Or is there a better approach?


texts:
Every time a new row is added to the table below, I want the sequence (Import Permit No) to be increased by 1....20160001, 20160002, etc and reset to 20170001, 20170002, etc in a new year.

CREATE TABLE [dbo].[tblPermits](
[ImportPermitID] [int] IDENTITY(1,1) NOT NULL,
[ImportPermitNo] [nchar](20) NULL,
[ImporterName] [int] NULL,
[Province] [varchar](50) NULL,
[LodgementDate] [datetime] NULL,
[PortofEntry] [int] NOT NULL,
[EstDateofArrival] [datetime] NULL,
[ConsignmentInvoicePONo] [varchar](50) NULL,
[OtherImportConditions] [varchar](400) NULL,
[Supplier] [int] NOT NULL,
[SupplierCountry] [varchar](50) NULL,
[CountryofOrigion] [int] NOT NULL,
CONSTRAINT [PK_tblPermits] PRIMARY KEY CLUSTERED
(
[ImportPermitID] 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

Currently what I have is a trigger as shown below.

ALTER TRIGGER [dbo].[trgPermitsInsertImportPermitNo]
ON [dbo].[tblPermits] FOR INSERT
AS
UPDATE dbo.tblPermits
SET ImportPermitNo = 'IP' + CAST(YEAR(i.LodgementDate) AS CHAR(4)) + RIGHT('000000' + CAST(i.ImportPermitID AS VARCHAR(6)), 6)
FROM dbo.tblPermits p
INNER JOIN INSERTED i ON p.ImportPermitID = i.ImportPermitID

But I couldn't get the sequence in the trigger above to reset to 0001 in a new year.

How do I modify the trigger to reset the sequence in a new year?

Rows can be deleted from this table. Other rows would not get renumbered in that case.
The number of new rows in a year is expected to be less than 5000, but in any case the number of prefix zeros could be expanded in the final design to accommodate a larger range.
Lodgement Date is a normal datetime field to be entered by the user.
All entries are made immediately when received from the client. Dates from previous years turning up late will not be an issue.
Duplicate ImportPermitNo values are not allowed.


texts: Postgres 9.6.5 - Can I use a query to calculate a value from two columns and insert the calculated value in a new column in the same table?

texts:
I am running the following version of Postgres on CentOS 7:

PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

I am new to database operations and need some help understanding if/how I should do the following:

I have a single table "Table1" that has three columns of data:

| timestamp | Value1 | Value2 |

The Value1 and Value2 columns contain decimal values like the below:

| 1.6732 | 2.78093 |

I have around a million rows of data in this table.

I would like to be able to deduct Value1 from Value2 and insert the result of this calculation in a new column in the same table, so that the table permanently stores the computed value and ends up looking like the below.

timestamp | Value1 | Value2 | Value3

I am not sure if I can or should do this, and if it is possible, how I would create  a query to make this happen.

Please can anyone give any advice, examples or pointers on how I should go about doing this or point me in the right direction of how I can learn to do this myself?

Thank you...


texts: Are there security vunerabilites/implications with SQL Mail, currently using SQL Server 2008 R2

texts:
I am an accidental DBA and responsible for series of data load jobs. I would like to use SQL Mail for altering me to job failures. SQL Mail is not currently turned on are there security concerns in setting it up?

I am using SQL Server 2008.


texts: mysql large number of opened tables -- what does this mean and how can I reduce?

texts:
I have 300 databases with a maximum of 59 tables each. My hosting provider says I have a lot of opened tables. I am not having any performance issues; but it is causing issues with backup (I use rackspace's cloud databases which uses Percona XtraBackup)

What can cause a lot of open tables and how can I decrease this? I don't get too much traffic and performance is good. I am not sure I understand exactly what opened tables is either.

I do have table_definition_cache set to 4096 as I wasn't able to run queries on views until I increased it.

mysql> SHOW GLOBAL STATUS LIKE 'Open_%';
mysql> +--------------------------+----------+
    -> | Variable_name            | Value    |
    -> +--------------------------+----------+
    -> | Open_files               | 7        |
    -> | Open_streams             | 0        |
    -> | Open_table_definitions   | 4102     |
    -> | Open_tables              | 4096     |
    -> | Opened_files             | 44025318 |
    -> | Opened_table_definitions | 1660409  |
    -> | Opened_tables            | 1857375  |
    -> +--------------------------+----------+

Here are the commands requested:

https://gist.github.com/blasto333/aa4241a4e37447961188356719ea6984


texts: Agent Job Running as Computer Domain Account Rather than Service account

texts:
I have two SQL Servers as follows

Microsoft SQL Server 2016 (RTM-CU9-GDR) (KB4058559) - 13.0.2218.0 (X64)   Dec 27 2017 17:10:01   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )

Microsoft SQL Server 2014 (SP3-CU4) (KB4500181) - 12.0.6329.1 (X64)   Jul 20 2019 21:42:29   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Both are running the SQL Server Agent service as NT Service\SQLSERVERAGENT

If I create a test job as follows

USE [msdb]
GO

/****** Object:  Job [TEST]    Script Date: 13/08/2019 14:47:44 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 13/08/2019 14:47:44 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TEST',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [a]    Script Date: 13/08/2019 14:47:45 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'a',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'WAITFOR DELAY ''00:10:00''',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

And run it on the 2014 server, I can see using sp_whoisactive that the job is running as NT SERVICE\SQLSERVERAGENT

If I run it on the 2016 server, I can see using sp_whoisactive that the job is running as DOMAIN\SERVERNAME$(Which incidentally is not a login on the SQL Server)

Why is this behaviour different between the two servers? As the job is owned by sa, I would expect it to run as the service account user (which is what is happening on the 2014 server) but this is not happening on the 2016 server?

What is causing this?


texts:
I have a table with unique identifier sid, somecolumn and boolean changed that is needed by other program to detect change in row, I'm using insert into ... on conflict (sid) DO UPDATE ... to insert data that's not already in there, but I also want to update somecolumn and set changed to true, only if somecolumn doesn't match excluded one.

to make it bit clearer

if sid doesn't exist, insert data
if sid exists and somecolumn matches excluded `somecolumn, do nothing
if sid exists and somecolumn doesn't match, update somecolumn with new value
and set changed to true

is there clean way to do this? I'd prefer to do this without making multiple queries,  I'm inserting hundreds of values in each query.


texts: Which index types do not support online index rebuilds?

texts:
In SQL Server 2012 Enterprise Ed., which index types do not support online index rebuilds?
and why?
and which activity-operations can prevent online index rebuilds?


texts: Correct composite indexing indexing order

texts:
I have MySQL (MariaDB) database. I have a table 'sensors' ,which collects data from IoT devices.
Each device may have 4-6 parameters that it records, like temperature, humidity, air quality, etc. Each device sends a measurement once every minute.
There are 10-15 such devices. Each device has its deviceid.
Six columns:
`autoid`(INT,AUTOICREMENT)
`deviceid`(varchar)
`pname`(varchar)         /* name of parameter like temperature,humidty */
`pcode`(INT)             /* code for each parameter like for temperature its 11,humidty its 12 etc */
`datavalue`(double)      /* value of parameter */
`rectime`(INT)           /* UNIX timestamp */

Here is sample of table data:

autoid
deviceid
pname
pcode
datavalue
rectime

1
sdbjs4b
temp
11
30.54
1621702300

2
sdbjs4b
hum
12
104
1621702300

3
sdbjs4b
gas
13
768
1621702300

4
vsf5bjs
temp
11
31.45
1621702300

5
vsf5bjs
volt
15
5.10
1621702300

There are almost 4-5 million rows in the sensors table.
My query requirements: I have to get data for some arbitrary time values for each day for each device and parameter.
Here is query that is used:
SELECT * from sensors where deviceid =? AND  pcode = ? AND rectime =?

This is guaranteed to give me only one result. The problem is I need to run this query inside nested loops, worst case 500 times. Why I need looping?. I need to create a report between two dates for each device, parameter, and for a given set of time slots between two dates. I have to loop for values of time slots.
I have a composite index on (deviceid,rectime,pcode).
What is the difference if I change this index to (rectime,deviceid,pcode)?
In general, will column order matter in composite indexing if my query uses all indexed columns in the where clause?


texts:
I have a reservation table that has from_date, to_date, and parking_location_id. What is the best way to calculate location spots availability for each location and each hour?

I tried to write a query that calculates availability from reservation table but this query is very slow when the period is long even though the from_date and to_date columns are indexed.

Another approach to speed things up was to create a location availability table

The contains all locations and all dates and hours in a range of years and whenever a reservation is saved a trigger updates the reserved spots in this table. However, in this approach, the table rows size grows exponentially and we need to add a range of dates with location ids whenever a new location is added. Any ideas how can I improve this design or what alternatives do we have?

The slow query tries to calculate number of reserved spots per date and then we find the max number in that date range.

select  sum(r.number_of_slots) as reserved_spots
from location l
inner JOIN reservation  r on r.location_id=  l.id  , time_dimension t
where t.db_date between r.from_date and r.to_date and
 t.db_date < r.to_Date and t.db_date > r.from_date  t.id=type_id and group by db_date;

Time dimension table contains a list of all dates in 10 years.


texts: SQL Server and SSPI handshake failed error

texts:
Recently I faced an issue where users from one domain were unable to login to SQL Server and getting this error:

  Error: 17806, Severity: 20, State: 2.
  SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: xxxxxxx]

  Error: 18452, Severity: 14, State: 1.
  Login failed for user ”. The user is not associated with a trusted SQL Server connection. [CLIENT: xxxxxx]

We have two domains let's say A and B. My SQL Server is running in domain B. In a normal scenario, both domain users are able to connect. But few days ago, all the users from domain A were unable to connect to SQL Server running in domain B. However domain B users were able to connect it successfully. Users approached SQL Server DBA team to look into the issue.

When I checked, even I was unable to connect to SQL Server remotely using SSMS and using credentials of domain A. However when I tried to RDP the server using domain A's credential it was working fine and there after remote access to SQL server also started working fine. So it looked to me like when I RDPed the server something got refreshed and my login remotely started working fine. I was sure that the actual issue was something related to AD and kerberos authentication but unable to prove so.

I researched this issue on web and found two solutions to fix it. One was to fix malfunctioning DC and second was to restart the server where SQL Server was running. I went the 2nd way as windows admins were not accepting that it is an DC issue. I checked everything from SQL end nothing got changed recently. Also found that this issue started happening after below event was logged on server where SQL Server was running:

  Log Name: System
  Source: NETLOGON
  Date: xxxxxxxx
  Event ID: 5719 Task
  Category: None
  Level: Error
  Keywords: Classic
  User: N/A
  Computer: xxxxxxxxxx
  Description: This computer was not able to set up a secure
  session with a domain controller in domain xxxxxx due to the
  following: There are currently no logon servers available to service
  the logon request. This may lead to authentication problems. Make sure
  that this computer is connected to the network. If the problem
  persists, please contact your domain administrator.

  ADDITIONAL INFO If this computer is a domain controller for the
  specified domain, it sets up the secure session to the primary domain
  controller emulator in the specified domain. Otherwise, this computer
  sets up the secure session to any domain controller in the specified
  domain.

My questions are:

How to prove that this is a domain controller issue?
How reboot of server fixes this issue?

Thanks for your help.


texts: How to Backup Remote Database to FTP server

texts:
I can connect to a remote SQL Server via Management Studio. But, when I create backups, they get created on the server's disk.

How can I transfer them to an FTP server?

I only have a login to the MS SQL Server & the FTP site where I need to save the backup file.


texts: Does postgres single-user mode use postgresql.conf settings?

texts:
Does running postgres in single-user mode (--single argument) cause postgres to start and use the settings present in the postgresql.conf file in the data directory defined by the -D option?

I am doing some maintenance with postgres in single user mode. I want to ensure that while running in single user mode that it has the correct resource allocation available. I have already set these resources in postgresql.conf and the work as expected when running in normal (not single-user) mode. Is the same config file used when running in single-user mode as long as its present in the defined data directory?

My specific goal is to run a VACUUM FREEZE on a 1.6TB table to cope with a multixact wraparound issue. My postgresql.conf is loaded up with higher than normal (for us) maintenance_work_mem settings. If these settings are not read when running in single-user mode I need to know so I can apply them manually using the -c flag I think. I am specifically seeking an answer as to if the config is loaded, Q&A about my FREEZE or specific tuning should happen elsewhere.

I think I could verify this with pg_settings however I am now 3 hours in to a long running maintenance operation. I would prefer not to stop and restart that maintenance if another user or a manpage will confirm that my postgresql.conf settings are applied.

I am running PG 9.4.5


texts: Proper usage of last_insert_id() in PHP procedure

texts:
I have a problem with performing sql query from php script. The query in sql looks like this:

INSERT INTO `table1` VALUES (NULL, 'Blah', 'blah', 'blah', 'blah');
SET @lastID = LAST_INSERT_ID();
INSERT INTO `table2` VALUES (NULL, @lastID, 'Other blah', 'and blah');

It works fine on phpmyadmin, but when I use it in php procedure as following:

function saveData() {
    $insert = "INSERT INTO `table1` VALUES (NULL, 'Blah', 'blah', 'blah', 'blah');
                SET @lastID = LAST_INSERT_ID();
                INSERT INTO `table2` VALUES (NULL, @lastID, 'Other blah', 'and blah')";
    mysqli_query($mysqli, $insert) or die ("<b>Insert to DB failed:</b> " . mysqli_error($mysqli));

    $mysqli->close();
    exit();
}

It prints the following error:

  Insert to DB failed: You have an error in your SQL syntax; check
  the manual that corresponds to your MySQL server version for the right
  syntax to use near 'SET @lastID = LAST_INSERT_ID(); INSERT INTO
  `table2` VALUES (NULL' at line 2

Any help would be appreciated.


texts: Writing transactions to .MYD file to take Backup

texts:
I'm trying to backup MySQL DB by archiving the data directory of required databases.

I'm doing FLUSH TABLES WITH READ LOCK before taking the backup, but even then some of the transactions were not written into .MYD files. cpio reports that there are few changes which got written recently to the files were not archived. Is there any command or options to make MySQL write all the transactions before lock to .MYD files


texts: Need help on best database design for quick retrieval of data

texts:
I'm in the process of designing one database that will replace lots of CSV files that I currently use for data storage, which are starting to get messy and inconsistent. I am using C# in Visual Studio. It is for Bricklink/Lego data, and I'll I'll just explain the section that I need help with below, simplifying the actual figures:

The part of the database I'll focus on has 3 tables: -Parts contains PartID and about 30 other fields (e.g. mass, averageSalePrice). There are 50,000 Parts. -Store contains StoreID and about 10 other fields. There are 1000 Stores. -StoreParts links the two in a many-many relationship. It contains PartID, StoreID, Date, Price and Notes.

Now here is my issue: Each store has 10,000 parts. So there would be about 10 million records in StoreParts (more if I record multiple dates). One query that I am likely to run would need to retrieve all of the parts for a given store and compare their Price to the averageSalePrice in Parts. I feel this may run very slow as it would be going through 10 million records of StoreParts to find the 10 thousand parts.

When I was using CSV files to store the data, I had one file for each store/date, so it only had to open that file with the 10,000 parts. I feel this would be more efficient than having to find one store's parts in the list of 10 million or more.

Is there a way I can set up my database so there is a separate table for each store? I feel that this would be more efficient to search, but from my experience does not fit with best practice for database design, as I would have 1000 store tables. If I consider recording store data on different dates (e.g. 1 store has the price of all its parts on 100 different dates), then things could get way too big and slow.

I would welcome any advice on this, as I would love to do this properly and not have to have CSV files sitting around all over the place as I currently have. Thank you.


texts: Do locks on readonly secondary databases propagate to the Read/Write Database

texts:
I'm thinking about using SQL Server Availability Groups in SQL Server 2012 to offload reporting on our main database to a second read only server that is kept in sync through the availability group.

My question is when I run big reports on the read-only server that would normally create lots of blocking if run on the read/write version do the locks propagate from the read only server back to the read/write server? Or do the locks stay only on the read-only server and just cause blocking on data waiting to be synced?

Trying to see if having a read-only server would resolve some issues I am having.


texts: Restoring .bak file SQL Server 2005 in SQL Server 2008

texts: I don't have enough knowledge in SQL Server back and restore. I just want to know is it possible to restore an SQL Server 2005 .bak file in SQL Server 2008? If yes, how can I do it?


texts: Restore database with Availability Groups

texts:
I have high availability enabled with two servers and when trying to restore a database I realised I had to un-synchronize the database and then remove it from the availability group.

I use Ola Hallagrens maintenance solution. Is there something I can amend with Ola's script to automatically remove DB from availability group when I run the restore job or is there a script already available, or simply not do-able?

Thanks


texts: filter table rows within range of related column

texts:
I have a postgres database with the following table:
    CREATE TABLE data (
      id integer PRIMARY KEY,
      group_id integer NOT NULL,
      value double
    );

the data contained in it is like the following:
| id  | group_id | value |
| --- | -------- | ----- |
| 1   | 1        | 0     |
| 3   | 1        | 0.6   |
| 4   | 1        | 1     |
| 2   | 1        | 2     |
| 33  | 1        | 5     |
| 7   | 2        | 0.0   |
| 11  | 2        | 0.1   |
| 6   | 2        | 0.33  |
| 8   | 2        | 1.5   |
| 20  | 2        | 2     |

I need to get all the rows in a group_id between two id's values.
For example for the group_id 2 and ids 11 and 8 I should get:
| 11  | 2        | 0.1   |
| 6   | 2        | 0.33  |
| 8   | 2        | 1.5   |

I was looking for a cleaner solution than:
SELECT
  *
FROM
  data
WHERE
  group_id = 2 AND
value BETWEEN (SELECT value FROM data  WHERE id = 11)
      AND (SELECT value FROM data  WHERE id = 8)

and that possibly worked if the ids are reversed, like for the group_id 2 and ids 8 and 11 returned:
| 8   | 2        | 1.5   |
| 6   | 2        | 0.33  |
| 11  | 2        | 0.1   |


texts: How to sync two or more collocation database server?

texts:
I can't tell if mysql clustering could be a solution(I am thinking about galera or xtradb clustering). But my biggest problem is about the unstable internet connection.

Actually it is planned as multi-tenant setup with a single online mysql server and the tenant is separated by schema, and a tenant can also have their own local database on their location.

But I want to solve this problem first.

For example I have a tenant, but this tenant has a multiple collocation, and want to sync the data on every location and also on the online server.

They can read/write on the first, second, etc collocation server using a desktop app(respective to their location) and they can also read/write on the online server using a mobile app.

Now my problem is how could I sync them, when internet is available?
So data written and updated on the first location will propagate to other server, on the online server on the second server and so on, and vice-versa.

P.S.
Data deletion will be soft-delete.

Thanks.


texts: Help me about syntax STUFF (SELECT... for XML PATH)

texts:
Here is script of 3 table:

create table products(
pid varchar(10),
pname varchar(20));

create table [Order Details](
oid varchar(10),
pid varchar(10),
quantity int);

create table orders (
oid varchar(10),
oname varchar(10));

insert into [Order Details]
select 'o1', 'p1', 10
union all
select 'o1', 'p2', 20
union all
select 'o1', 'p3', 20
union all
select 'o2', 'p1', 10
union all
select 'o2', 'p2', 10
union all
select 'o3', 'p1', 10

insert into products
select 'p1', 'Product A'
union all
select 'p2', 'Product B'
union all
select 'p3', 'Product C'

insert into orders
select 'o1', 'Order #1'
union all
select 'o2', 'Order #2'
union all
select 'o3', 'Order #3'

select * from products;
select * from orders;
select * from [Order Details];

And here is my query:

select oname, pname, quantity
from orders o1 inner join [Order Details] o2 on o1.oid=o2.oid
inner join products p on o2.pid=p.pid
group by oname, pname, quantity

The output is like:

Order Name  Product Name Quantity
Order #1    Product A    10
Order #1    Product B    20
Order #2    Product A    10

I need to use syntax stuff (select... for XML PATH) for reporting likes that:

Order Name  Product Name           Quantity
Order #1    Product A, Product B   30
Order #2    Product A              10

Because there are 3 tables (I have searched but the examples just have 2 table only). So I have tried many times but the result is always failed. I'm embarrassed with the rest table#3... Please help me, thanks so much!


texts:
I've configured my server (5.5.41) with:

character-set-server   = utf8
collation-server       = utf8_unicode_ci
skip-character-set-client-handshake

However

mysql> SELECT CHARSET(USER()), COLLATION(USER());
+-----------------+-------------------+
| CHARSET(USER()) | COLLATION(USER()) |
+-----------------+-------------------+
| utf8            | utf8_general_ci   |
+-----------------+-------------------+

Why is the client collation utf8_general_ci when it should be utf8_unicode_ci?

mysql> SHOW VARIABLES LIKE '%char%'; and SHOW VARIABLES LIKE '%collat%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> SHOW VARIABLES LIKE '%collat%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+


texts: Can Oracle be installed on Windows without being an administrator?

texts: Can the oracle software be installed on windows without having administrator privileges?


texts: MySQL - LOAD DATA INFILE - Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

texts:
I am selecting data from a database on server A, dumping the output into a file locally, and then doing a LOAD DATA INFILE on my local database.   The bash command looks something like this:

mysql -e "SELECT * FROM items WHERE created_at > '2011-05-01';" > items

I was told this was the correct way when moving data from an external server to a local database.  I think I would have normally chosen to use mysqldump, but I didn't want to fuss with curling the dumped file from the server to my local machine.

The data that is dumped to the items file with the command line code above contains a lot of NULLs.  Those NULLs, when read by the LOAD DATA INFILE command, run locally, ends up loading a bunch of strings of "NULL" into my table.  I want those to be properly translated into NULL without having to go into the items file and find/replacing "NULL" with "\N"

I need a way to do one of two things:

1) When selecting data from a mysql table, I would like the output to escape NULLS into the standard \N string that is read by LOAD DATA INFILE.  In the interest succinctness and ease of maintenance, I want to avoid the type of code below.

SELECT IFNULL(x, '\N' ) AS x, IFNULL(y, '\N') AS y ...

2) When running a LOAD DATA INFILE, I would like it to treat the string NULL as null.

(SELECT * FROM items WHERE created_at > '2011-01-01')

Any thoughts or alternate methods are greatly appreciated.

Many Thanks,
Chad


texts:
Basically we need to have a replication, a minimal setup.

Primary
- Slave 1
- Slave 2

Is it possible in the future we Shared it? What would be the setup?

Is it going to have shared on Slaves like this?

Primary
- Slave 1 (Shared A)
  - Primary
  - Secondary
- Slave 1 (Shared A)
  - Primary
  - Secondary


texts: Need to intentionally create blocking processes for testing

texts:
My team and I have an issue where we need to be able to identify processes that are blocking other processes and kill them. There are a ton of scripts that are freely available to perform these actions. We have tried various ones and scrutinized the code. (If you are someone that has posted one of these queries, thanks!)

Before I go further, let me tell you that this is against a vendor application that we cannot modify. The vendor is also not willing to spend the time to figure out why processes are getting blocked. The only choice we have at this point is to kill the long running processes (as suggested by the vendor support). Before killing these processes, we do review the query running, but 99.9% of the time, it shows up as FETCH API_CURSOR00000000000A7E1F, which tells us nothing.

This has been a manual process up to this point. Now, we want to automate the killing of these long running blocking processes instead of someone manually killing them.

We want to test this script before putting it into production. We would like some help creating a script that would intentionally create blocking processes. We have tried with the TEST environment with this application, but unfortunately, we have had no luck replicating blocking processes.

Thanks in advance for your assistance!


texts:
I have several queries like this (running on MySQL 5.5):

DESCRIBE `TABLE`;

And it randomly takes 5-10 seconds on my production servers. We have an ORM that caches this data once per day (per web server) and I'm wondering why I see these slow queries popping up from time to time. I'm thinking that I could just change the web servers to cache until their processes are restarted but it'd be nice to know if there's an easy fix or something I could look into that may be causing this.


texts: Mysql binary logs are not deleted after expiration

texts:
I have 3 Mysql 5.6 nodes in one galera cluster in the first DC and 3 MariaDB 10.3 nodes in another galera cluster in the DR site. One node on the 1st DC is the master for one slave node on the DR site. In all nodes binary logs are enabled to be ready anytime act as masters.
The problem is that despite the fact that expire_logs_days = 2 binary logs are not deleted in any node . I have tried to stop one node, delete everything under /var/lib/mysql/ and restart but nothing changed. I still delete the logs by hand using PURGE BINARY LOG commands. Any idea why this cloud be happening?

I have checked expire_logs_days with mysql command and I also have it in the my.cnf


texts: Considerations for performance comparison with a high fragmented heap?

texts:
I am planning to perform some performance comparisons for different structures of one and the same table. The table I want to investigate on is a heap having ~ 1Mio rows and 123 columns.

Setup:
I have copied the table from production to a test system. It is a heap table and I want to check options to improve it.
Then I created 3 fresh copies of this table's data.

Copy1:

Clustered index on Primary Key (uniqueidentifier)
Same Nonclustered indexes & foreign keys

Copy2:

Clustered index on an existing IDENTITY Column
Same Nonclustered indexes & foreign keys

Copy3:

Clustered index on another uniqueidentifier and int column that is frequently used to filter a list of the rows
Same Nonclustered indexes & foreign keys

I have collected several typical and frequently used sample INSERT, UPDATE and SELECT queries from production environment. My plan is to run those multiple times against all those four tables and compare the total IO Statistics and total cpu time statistics as well as the Execution plan details to hopefully come to a valuable result telling me which table performs best with the regular queries of the application.

Since the three table copies are brandnew, so there is no fragmentation in place.
For a fair comparison I also re-builded all indexes of the original table.

I am not sure about the fragmentation of the "heap-index" of this table, it is ~60%. I wonder if I have to take care of this first or just leave it as it is since it will be in place in production as well and even if I fixed it, it will however come back and maybe influence query performance anyway?

I would beside an answer to the question above also appreciate any helpful comments what to consider as well to have the best possible result of this work. Especially regarding the data cache. Not sure if I should always clear it in advance of the queries or better run the queries once to FILL it before taking the samples?


texts: Converting to MyISAM, `foreign_key_checks = 0` Not Working

texts:
At the end of a long series of compromises working in a system I didn't design, own, or maintain, I need to convert a number of MySQL InnoDB tables to MyISAM tables.  However, when I attempt to alter the table

mysql> ALTER TABLE catalog_category_entity ENGINE=MyISAM;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql>

MySQL complains.  This is expected.  However, if I disable the foreign_key_checks, I get the same results

mysql> SET foreign_key_checks = 0;
mysql> ALTER TABLE catalog_category_entity ENGINE=MyISAM;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

This happens regardless of my setting the key check at the global or session level.   I assume the problem here is the table in questions has other InnoDB table which reference it, and MySQL refuses to end up in a state where an InnoDB table references a table that's not InnoDB. (I may be incorrect on this, and I'm more than happy to be corrected)

Is there a quick way to handle this situation?  I basically want all tables in the database to be MyISAM, and tracing out all the relationships myself and/or manually removing the contraints seems very time consuming, and  the sort of thing that a computer would be better at.

If the answer is "suck it up and do the work" I'm happy to hear that from an expert — I just don't want to waste the time if I don't need to.


texts: How can I replace the cursor in the below query?

texts:
I have this below query which calls a stored procedure that takes only 1 item's attributes at a time. But because of performance problems we are required to remove the cursor. How can I replace the below cursor logic with set operations or CTE? Please advice.

DECLARE db_cursor_ava CURSOR
    FOR
        SELECT t.[agent-id],
               t.[start-date],
               t.[end-date],
               t.[monitor-days],
               t.[monitor-start],
               t.[monitor-end],
               t.[timezone-offset]
        FROM   @tmpAgentPeriodTimeRange t

    OPEN db_cursor_ava
    FETCH NEXT FROM db_cursor_ava INTO @agentID_ava,
    @stDateTime_ava,
    @endDateTime_ava,
    @monDays_ava,
    @monSt_ava,
    @monEnd_ava,
    @offset_ava

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DELETE
        FROM   @tmpMonitorPeriod

        DELETE
        FROM   @tmpFinalResult

        SET @runID = 1

        IF(@endDateTime_ava>DATEADD(MI,@offset_ava, GETUTCDATE()))
        BEGIN
            SET @endDateTime_ava=DATEADD(MI,@offset_ava, GETUTCDATE())
        END

        INSERT INTO @tmpMonitorPeriod
        EXEC core.usp_GetMonitoringPeriod
             @startDate = @stDateTime_ava,
             @endDate = @endDateTime_ava,
             @monitoringDays = @monDays_ava,
             @monitoringStart = @monSt_ava,
             @monitoringEnd = @monEnd_ava

        SELECT @maxID = MAX(tm.id)
        FROM   @tmpMonitorPeriod tm

        FETCH NEXT FROM db_cursor_ava INTO @agentID_ava,
        @stDateTime_ava,
        @endDateTime_ava,
        @monDays_ava,
        @monSt_ava,
        @monEnd_ava,
        @offset_ava
    END

    CLOSE db_cursor_ava
    DEALLOCATE db_cursor_ava


texts: Why do Indexed Views not permit non-unique clustered indexes?

texts:
I've been looking into using Indexed Views to increase performance on a few of our most commonly used views.

However Indexed Views do not support non-unique clustered indexes which goes a little against the precedence set by the rest of the database structure.

For example, here is a simplified version of a couple of our tables.

-Groups-
Group ID    GroupName

-Users-
UserKey    UserName    FullName     GroupID

The indexes are on Groups.GroupID (Non-clustered) and Users.GroupID (Clustered). The clustered key being on GroupID in the Users table as most commonly a range of users from a specific group would be retrieved.
Obviously you would have multiple users per group, so this clustered index is non-unique.

This leaves me a bit uncertain of how to follow this precedence when indexing my views such as this example, as I cannot have a non-unique clustered index.

ConsumableID    ConsumableVariantID AllowThresholdOverwrite FullPath    GroupID ManufacturerID  Type    ModelID
101              29                 1                       0.1.2.4.    4       3               3       2

In actuality the only value on this View which would always be unique is the ConsumableID column, so I am left with little choice as of where to place my index.

Why do Views not permit non-unique clustered indexes when regular tables do?


texts: Why is Clustered Index on Primary Key compulsory?

texts:
Why Primary Key constraint creates Clustered Index on the PK column by default?

Can we create a table which has a primary key, but NO clustered index. I need PK constraint to maintain the unique values + not null on the column, but i do not want a clustered index as
there are too inserts in this table.

I have read that having clustered index has performance impacts on inserts.


texts:
I have some values under "account" and I need to have the same values under "invoice", so, I'm trying to find a way to symlink my tables, so every time one gets updated, the another have the new value too. I'm developing under OpenERP, I can't trow that update directly on code, I need to write the rule using PgAdmin or something like.

I need to junction symlink 2 tables under Postgres.

ACCOUNT                INVOICE
|ID|payment_type|@@@@@@|ID|account_payment_type|
|#1|cc          |@@@@@@|#1|cc                  |
|#2|cash        |@@@@@@|#2|cash                |
|#3|cash        |@@@@@@|#3|cash                |


texts: Whether Cold backup method can be used to "migrate oracle DB"?

texts:
I have to migrate one database.

I have exact server replica. Same directory structure on target as well as source database.

Previously I have performed DB migration using export/import datapump as we don't have access to any tools.
I want to ask whether I can create replica using cold backup method?

DB Details:

Oracle 10.2.0.3 (On Both Source & target)
DB is currently running in no archive mode.

Steps I am going to follow:

create pfile from spfile
shutdown normal on source db
Copy pfile to target server $ORACLE_HOME/dbs
Copy all the dbf , ctl , rdo files to target server (Using binary mode FTP) (select name from v$datafile; select member from v$logfile; select name from v$controlfile)
startup pfile='<path>' on target server

Doubts:

Can cold backup can be used to create replica/ DB migration?
If I tried above process, shall i am able to start the db on target server without any errors?
As I don't have any archive logs, will that create any problem here?


texts: Assignment of a column with dynamic column name

texts:
I get the name of a column to set in a (BEFORE UPDATE) trigger, I want to set it to the OLD value and ignore anything coming in. I've tried the following:

CREATE OR REPLACE FUNCTION prevent_column_update() RETURNS TRIGGER AS $$
  DECLARE
    col TEXT := TG_ARGV[0];
  BEGIN
    EXECUTE format('SELECT ($1).%I INTO ($2).%I', col, col) USING OLD, NEW;
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

And to be used like:

CREATE TRIGGER request_protect_date_price_value
  BEFORE UPDATE OF date_price ON requests
  FOR EACH ROW EXECUTE PROCEDURE prevent_column_update('date_price');

But when updating it fails with:

ERROR:  syntax error at or near "("
LINE 1: SELECT ($1).date_price INTO ($2).date_price
                                    ^
QUERY:  SELECT ($1).date_price INTO ($2).date_price


texts: why do I have huge log files (SQLFT000060005.LOG.x, 3+Gb) with recent timestamps when I haven't used SSMS for 4 months?

texts:
...plus several ERRORLOG.x files ranging from 200MB to 2.1GB ...

I installed SQL Server 2017 in January for an assignment, but haven't used it since then.
Now the logs files are hogging 22.8GB of space, with recent datestamps - how is that possible?
What can I do to remove them - just delete them?


texts:
We have peaks where latency (as measured by [Physical Disk\Avg Disk Sec read] on the drive where our data file is) goes up to 0.2 sec.

I immediately started suspecting the SAN while the DBA is suspecting that this is related to the fact that we currently have a single MDF file that is 5 terabytes.

Does a very large MDF file negatively impact latency? If so what is the appropriate size?

I have not seen a lot of content on this subject.

Thanks!
-Xavier


texts: SQL Server: Create default stored procedure as and when a new database is created

texts:
I am using SQL Server 2008 R2. I need one simple stored procedure to be created by default, as and when a new database is created in SQL Server. How can I achieve this? Assume the stored procedure to be created is

CREATE PROCEDURE [dbo].[Default_SP]
AS
BEGIN
    SELECT GETDATE()
END

Triggers can be applied on the table level. But this is on database create event. I searched over the net and I could not find answers.


texts:
Hi all can you please assist me,

I would like to split my records into different year and month see below I want figure one to be split into different categories but this must all display on one page.

my current result set is like this:

Date Year   Date Month  Instructor  Num Of Members
2013            November    Jones       57
2013            November    Smith       40
2013            November    Moreleta    55
2013            November    James       65
2013            November    Soodie      65
2014            January     Jones       47
2014            January     Smith       55
2014            January     Moreleta    60
2014            January     James       73
2014            January     Soodie      68
2014            February    Jones       48
2014            February    Smith       60
2014            February    Moreleta    66
2014            February    James       75
2014            February    Soodie      68

I want them to be split like the one below. Can any of you assist me in doing this.

Date Year   Date Month  Instructor  Num Of Members
2013            November    Jones       57
2013            November    Smith       40
2013            November    Moreleta    55
2013            November    James       65
2013            November    Soodie      65

2014            January     Jones       47
2014            January     Smith       55
2014            January     Moreleta    60
2014            January     James       73
2014            January     Soodie      68

2014            February    Jones       48
2014            February    Smith       60
2014            February    Moreleta    66
2014            February    James       75
2014            February    Soodie      68


texts: What are the "GDR" vs "QFE" hotfixes for SQL Server 2008 R2 SP3?

texts:
I'd always assumed that higher patch numbers were necessarily later releases, but now I'm not so sure.

Looking at the SQL Server Build List for SQL Server 2008 R2 SP3, there are two possible ways to apply security update MS15-058, one labeled "GDR", and one labeled "QFE".

What's the difference between these?

According to this blog entry, the GDR (general distribution release) are the ones we should all be installing, and the QFEs are the "only if you have this specific issue" releases, or something like that?

If I don't need TLS1.2 support, should I apply the 10.50.6220 patch? Or the 10.50.6529 patch?


texts: How can I create a view that spans multiple databases?

texts:
I have three databases named exayear2011, exayear2012, and exayear2013. They all have the same schema, but the data is different for each year.

I want to create a view that gets data from table1 in all three databases with UNION ALL. How would I do this?


texts:
Folks, I am currently learning about distributed data systems via the book "Designing Data-Intensive Applications".
I think I have a pretty strong understanding about how version numbers in a single replica system allow the server to detect concurrent writes*. The author starts with this example because once you understand the single replica system, expanding that understanding to a multi-leader or leaderless replicated system is supposed to be obvious, but it is not obvious to me at all.
How do version number in a system where multiple replicas can handle write requests work? In other words, what are version vectors?
* In a single replica system, each write is accompanied by a version number. This version number is the version of the data that the write is based off of. If a write is based on Version 1 of the data for that key, and Version 2 already exists, we know that the incoming write is concurrent with Version 2. The incoming write can only overwrite data that was in Version 1, since it does not know about the data in Version 2. For example, Version 1 is [eggs], Version 2 is [eggs] and [milk]. The incoming write wants to update this key to [eggs, bacon]. Version 3 of this key will become [eggs, bacon] and [milk]. The incoming write cannot overwrite [milk] since it didn't even know that [milk] was a value in the key.


texts: Index rebuild, data compression generated Large TLog

texts:
We are following vendor recommendations for a index rebuild changing the fillfactor and enabling data compression, on the test server the results have us wondering what is going on that would cause the TLog to exceed 650 GB, (ran out of drive space).  Why is the tlog growing so much?

SQL Server Version   12.0.2456.0

Vendor code provided:

alter index all on dbo.ACC_LOG_DTL_IX rebuild with
( data_compression=PAGE, fillfactor=85, online=ON)

This is the script for the table and the one index.

CREATE TABLE [dbo].[ACC_LOG_DTL_IX](
    [ACCESS_INSTANT] [numeric](16, 6) NOT NULL,
    [PROCESS_ID] [varchar](254) NOT NULL,
    [DATA_MNEMONIC_ID] [varchar](15) NOT NULL,
    [STRING_VALUE] [varchar](2000) NULL,
    [INTEGER_VALUE] [numeric](18, 0) NULL,
 CONSTRAINT [PK_ACC_LOG_DTL_IX] PRIMARY KEY CLUSTERED
(
    [ACCESS_INSTANT] ASC,
    [PROCESS_ID] ASC,
    [DATA_MNEMONIC_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]