texts: what databases are already set up in the log-shipping, and which ones are not?

texts:
I am setting up log-shipping from server1 to server2.
there are many databases on server1 and I want to automate the process.
I have already done a few databases manually, while I am working on the automating process.
First question is:
How can I easily find out which databases are already set up in the log-shipping and which ones are not?
Currently to get a list of databases not yet on log-shipping I am doing the following:
    if object_id('tempdb.dbo.#log_shipping_monitor ') is not null
       drop table #log_shipping_monitor

    create table #log_shipping_monitor
    (
        status bit null
        ,is_primary bit not null default 0
        ,server sysname
        ,database_name sysname
        ,time_since_last_backup int null
        ,last_backup_file nvarchar(500) null
        ,backup_threshold int null
        ,is_backup_alert_enabled bit null
        ,time_since_last_copy int null
        ,last_copied_file nvarchar(500) null
        ,time_since_last_restore int null
        ,last_restored_file nvarchar(500) null
        ,last_restored_latency int null
        ,restore_threshold int null
        ,is_restore_alert_enabled bit null
        ,ts timestamp not null
        ,primary key (is_primary, server, database_name)
        ,unique (ts)
    )

    insert into #log_shipping_monitor
    ([status]
    ,[is_primary]
    ,[server]
    ,[database_name]
    ,[time_since_last_backup]
    ,[last_backup_file]
    ,[backup_threshold]
    ,[is_backup_alert_enabled]
    ,[time_since_last_copy]
    ,[last_copied_file]
    ,[time_since_last_restore]
    ,[last_restored_file]
    ,[last_restored_latency]
    ,[restore_threshold]
    ,[is_restore_alert_enabled])
exec master.dbo.sp_executesql    @stmt=N'exec sp_help_log_shipping_monitor'
                                ,@params=N'@verbose bit'
                                , @Verbose=0

SELECT d.NAME, d.recovery_model_desc
  FROM sys.databases d
where d.name not in
         (
            select l.database_name
            from #log_shipping_monitor l
            where l.[server] = @@SERVERNAME

          )
      and d.database_id > 5
      and d.state_desc = 'ONLINE'
order by d.name

this is working fine and gives me the following result:


texts:
Since I could not find it out here, do set statements happen in order scripted? Below is query I found. Would I get the same result every time it ran?

DECLARE @Num VARCHAR(10) = '10';
DECLARE @Cleaned VARCHAR(10);
SET @Cleaned = SUBSTRING(@Num, PATINDEX('%[^0]%', @Num+'.'), LEN(@Num));
SET @Cleaned = CASE
                   WHEN LEN(@Cleaned) > 3
                   THEN @Cleaned
                   ELSE RIGHT('00000'+@Cleaned, 3)
               END;
-- to be set up
SELECT @Num AS [before]
     , @Cleaned AS [After]


texts: SQL Server 2014 compression and maximum row size

texts:
I need to create wide denormalized table with lot of decimal(26,8) columns (fewer than 1024 column limit, most of the columns would be null or zero).
I know about 8060 bytes per row limitation, so i tried to create table with page compression.
Code below creates table, inserts one row and queries row size. Row size is far below the limit, but if i try to add one more decimal(26,8) columns to table, operation fails with error "Creating or altering table 't1' failed because the minimum row size would be 8074, including 1256 bytes of internal overhead.". Is there any way to create single table with that many columns?

drop table t1
GO
create table t1(c1 decimal(26, 8) null)
with (data_compression = page)
GO

declare @i int = 2;
declare @sql varchar(100);
while @i <= 486
begin
    set @sql = 'alter table t1 add c' + convert(varchar, @i) + ' decimal(26, 8) null';
    execute (@sql);
    set @i += 1;
end;
GO

insert into t1(c1) select 0
GO
declare @i int = 2;
declare @sql varchar(100);
while @i <= 486
begin
    set @sql = 'update t1 set c' + convert(varchar, @i) + ' = 0';
    execute (@sql);
    set @i += 1;
end;
GO

select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id(), object_id('t1'), NULL, NULL, 'DETAILED')
GO


texts: Automatically purging binlogs after successful replication

texts:
I have a dedicated server hosting a MySQL database, and want to use replication to have an always up-to-date backup somewhere.

I chose to use Amazon RDS as my replica, and I'm following this guide:

Replication with a MySQL Instance Running External to Amazon RDS

What I fail to understand however, is who/what is supposed to take care of purging binary logs. The only configuration I found is expire_logs_days, which the documentation describes as:

  You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days (...). If you are using replication, you should set the variable no lower than the maximum number of days your slaves might lag behind the master.

Is this the standard procedure DBAs use?

Isn't it possible to have RDS, which is the only replica, purge the logs on the master as soon as it's processed them?


texts: Lost over 400k rows after scheduled system restart in MariaDB DB

texts:
I have lost over 400k rows (not a lot considering I have over 3000 thousand rows per minute)
I have hypothesized that it is not being written to disk often enough.
Is there something that I can do to remedy this? Thanks.


texts:
I have a new production 3-node Galera (MariaDB 10.2) cluster, and am looking for advice on backups. My current plan is twofold:

Replicate one node to a non-clustered slave, and schedule mysqldumps on the slave.
Do full VM backups of one Galera node with a pre-freeze / post-thaw script which stops and then restarts mysqld.

I am particularly interested for feedback on the second point (VM backup). Is this an acceptable strategy?

Thanks,
Sera


texts:
I'm learning master-slave replication with MySQL and I could get it to work and also use the Percona backup tool to restore a slave. I learnt from this project https://github.com/vbabak/docker-mysql-master-slave

Now I wonder if the master-slave replication must be two separate mysql instances of if it is possible to configure master-slave replication between two databases only instead of needing two separate mysql instances. I think it's not possible.

The reason I want to know is that I want to automate failover and restore scenario and in my environment a new mysql instance always runs on the default port because of infrastructure automation, and therefore it is not possible today to start two mysql instances on the same host machine server, therefore it looks like I need to create two VMs with one master and one slave just to perform the test which is quite overkill for a test scenario and would be slow.


texts: Creating a read only replica of SQL Server database for reports

texts:
I've faced a problem when my .Net web application is running slow, even when I've optimized all queries. As it turned out another developer, who is making reports runs a bunch of non optimized queries all the time. So I've decided to create a separate server for him. Currently I created Jobs making backup, copying it to another server and restoring it. But it's temporary solution, so in order for second server have up to date data or at least with minimal latency I'm investigating an opportunity to create a read only replica.

As I'm not a DBA I've read a bunch or articles regarding SQL Server replication mechanism. The best option for me is when my PROD web application isn't affected by replication at all, I mean lots of lock on syncronizing tables. I don't need a real time sync as mirroring does it, also I don't need any king of cluster solution, just read only syncing copy of my PROD database. So I've choosed Transactional replication mechanism(not updatable) with asynchronous distributing policy(on schedule).

So I've some question:

Does Transactional replication suit my problem the most among SQL Server replication mechanisms?
If I have an opportunit to migrate from SQL Server 2008 to SQL Server 2012 would transactional replication have some breaking changes? I've read an article on technet that there might occur errors?
Isn't SQL Server 2012 Always On mechanism better to solve my problem(I'm considering this option as last one, as I'm still using 2008 R2, and yet migration to 2012 is planned by not soon)?


texts: Select post and concatenating all categories it belongs to

texts:
I have 3 DB tables

POSTS
+----------+----------+----------+
| id       |  title   |  content |
+----------+----------+----------+

CATEGORIES
+----------+----------+
| id       |  title   |
+----------+----------+

POSTS_CATEGORIES
+----------+----------+--------------+
| id       |  post_id |  category_id |
+----------+----------+--------------+

Now lets say I have the following posts:

id=1, title=birds and it belongs to category 1 and 4

id=2, title=apple and it does not belong to any categories

My Query:

SELECT p.id, p.title, p.content
GROUP_CONCAT(DISTINCT p_c.category_id ORDER BY p_c.category_id ASC SEPARATOR ',') AS categories
FROM posts_categories AS p_c, posts as p
WHERE p.id = 1 AND p.id = p_c.post_id
GROUP BY p_c.post_id

This query works for id=1 but when a post does not have any categories, like id=2 it won't find anything. I've tried to modify this a bit, but I always end up with no results or all the fields as null when I'm looking for id=2

I know I could get around this with 2 queries, but was trying to do it with one query. I'm using mysql 5.5.27


texts:
I want to sync data from one table to another(only from 1 column) to another table.

Table A (
   col1 varchar(50) DEFAULT NULL,
   date_in timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
)

Table B (
   col1 varchar(50) DEFAULT NULL,
)

My strategry is to sync data that changed in timeframes of 5 minutes .

I sync with accuracy of 1 sec. Now there can be a situation where I want to sync data since 2020-05-03 00:05:00 until now (2020-05-03 00:10:00) and I receive x rows, but there are a few rows which are inserted to the database with for example date 2020-05-03 00:09:59 but they are not committed at the time I execute a SELECT query, so I miss them.

Plan A

Add some grace time, for example 10 seconds to my SELECT query.

Plan B

Have another thread which runs once a day and sync the "missed rows".

Are this plans legit? it there a better way of acomplishing this?


texts: Import SQL dumps (tables) in folder from shell

texts:
Im trying to import SQL tables (actually this is files, one file for 1 table), and this is files are too big for importing in phpMyAdmin. It needs to be imported from shell. I know the command "source " to import whole DB. But I'm need to import all *.sql files in folder into one DB. Does mysql have query for this?

P.S. I can't "cat " this file because I'm getting troubles with encoding after this.


texts: Postgres 9.4.1 on Ubuntu 14.04.2 LTS fails to install properly

texts:
I'm trying to install PostgreSQL 9.4.1 on Ubuntu 14.04.2. I'm using the packages from apt.postgresql.org.

The package installs but it fails running pg_createcluster. If I run manually, I get:

Creating new cluster 9.4/main...
  config /etc/postgresql/9.4/main
  data /var/lib/postgresql/9.4/main
  locale en_US.UTF-8
Flags of /var/lib/postgresql/9.4/main set as --------------e-C
Error: initdb failed

I can't find any details as to why initdb fails.

If I manually run initdb as the postgres user, that works, but that does not create the config files.

I checked everything I could think of, including on-line and can't find a solution.


texts:
I tried using set time until.. and mis-typed the date.  Can anyone help me understand how to get my backups into a manageable state?

After the accidental recover, most of my backupset disappeared.

I recovered them and used 'catalog recovery area' .. and they're listed in 'list backupset'.  But something still isn't right.

When I do a PITR now, I get messages that my dbf files aren't available

and... the 'list backupset' seems to show backupsets.  But they are listed differently than the files which weren't included in the 'bad' recovery.

Gists with the error and the list of backupsets are here https://gist.github.com/akinsgre/5561254


texts: Move a MySQL 5.5 database to a server running MySQL 5.1

texts:
I have a backup of a MySQL database (using InnoDB) from a Windows machine that was running MySQL 5.5. The Windows machine is no longer available and now I need to import the backup into an Ubuntu box running MySQL 5.1.73. Is this possible? Any hints on how/where to copy the files?

Most of the info I find is about "upgrading" from 5.1 to 5.5 but in my case I need to go the opposite way.


texts: Replication of specific tables using Postgres 9.4.4 and Amazon Web Services

texts:
I have currently setup a Postgres 9.4.4 RDS instance on Amazon Web Services. On this instance I hold 20 tables. In addition I also have Postgres 9.4.4 installed on my local server (OS: 64-bit Linux) which holds about 30 tables under one database. Both the cloud instance and my local server instance have overlapping tables i.e. there are some tables on the cloud that I replicate to my local and vice versa. I am not well versed with Postgres Replication procedures as I have never needed them and at the moment have been doing the transfer rather inefficiently via python scripts to read/write/append tables between these two databases.

I would appreciate any guidance from the community on what is the best procedure. Ideally I would like a solution where some tables on the cloud can be MASTER and SLAVE locally as well as allow other other tables to be a MASTER locally and a SLAVE in the cloud. Is this possible?

Have heard that maybe Burcardo or Slony could do this? Any good documentation/sites on this area would also be helpful.


texts: Wrong result for FileTableRootPath Function of SQL Server

texts:
I just restored a database backup and now the result of the function FileTableRootPath() is incorrect.

Where is the place that I can change to change the result of this function?


texts: Best way to use insert statement on Oracle

texts:
I need to store addresses from customers and companies into different tables with the same columns. And I would like to know what is the best approach to use the same insert statement, but only by changing the name of the table.

Example:

CASE CPF
 WHEN NULL
  INSERT INTO COMPANIES_ADDRESS (ZIPCODE, STREET, NUMBER) VALUES (V_ZIPCODE, V_STREET, V_NUMBER)
 ELSE
  INSERT INTO CUSTOMERS_ADDRESS (ZIPCODE, STREET, NUMBER) VALUES (V_ZIPCODE, V_STREET, V_NUMBER)
END


texts: When modelling a database, does the ER diagram depend on the DBMS used to implement the said ER?


texts: PostgreSQL - pgAdmin 4 - COPY command not working with Schema

texts:
When trying to use the COPY command in pgAdmin 4 to copy a table from my database into a CSV file on disk
copy Administrative.REGIONS TO 'd:\temp\regions.csv' with csv header

I get the following error:
ERROR:  schema "administrative" does not exist
SQL state: 3F000

I'm using PostgreSQL 12.3 on a localhost (Win 10), and I have tried to use the same command on a public schema table and it worked fine. It didn't work on any table from any schema I tested apart from the public one.
The same command works fine with any table in the public schema.
The schema name and table name are copied and pasted from the properties
Any clues?
Thanks in advance, Ahmed


texts: Whether Logfile Shrinking is good or bad one for Performance in SQL?

texts:
I am using SQL Server 2008 R2 database and on our SQL Server instance we have more than 70 databases. In a few databases I can see that the transaction log file size is more than 10 GB. I am shrinking the Tlog file size on a daily basis using the following script:

USE ABC;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE ABC
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (ABC_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE ABC
SET RECOVERY FULL;
GO

Is there any recommended way to shrink the transaction log file size?


texts: ALTER TYPE Not Available So How To Alter a Type?

texts:
I know there are other questions out there regarding this issue, but this one is really simple so I'm not looking for a complicated answer!

So I have this script:

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IDListTableType' AND ss.name = N'dbo')
DROP TYPE [dbo].[IDListTableType]
GO

IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IDListTableType' AND ss.name = N'dbo')
CREATE TYPE [dbo].[IDListTableType] AS TABLE(
    [ID] [int] NOT NULL,
    UNIQUE NONCLUSTERED
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

I get an error when I run it because IDListTableType is referenced elsewhere in the database. So I need a way to either alter the table type in place, or to find and drop and recreate all other database objects that are referencing IDListTableType. Is there a simple way to do this?


texts: Need some clarification of another post's method for restoring database with frm and ibd files

texts:
I want to ask a question in this one post at Restoring MySQL Tables from .ibd, .frm and mysqllogbin files but unfortunately my reputation isn't at 50 yet, so the system won't let me ask in that post.

So I ask here:

She figured out how to get her database restored through some trial and error. Her step #2 said:

  Go into your command/terminal to open the MySQL utility, mysqlfrm, and
  use it to find the structure of your table that you need to restore.
  How I did that, was I cd'ed into the file location of mysqlfrm, then
  entered "mysqlfrm --server=user:pwd@localhost --port=3307
  "path_to_.frm_file" > table_name.txt". The .txt file should be saved
  in the same folder as where your utilities are saved if you didn't
  specify where you wanted it to go.

My question is this: I assume that I put the path to my frm file in that area she puts "path_to_.frm_file" so I'd do something like this: C:\MySQL_5.6.12\data\wordpress\mysqlfrm  but I'm not understanding the "table_name.txt" part. Will I need to put the name of my table in the "table_name" part and am I correct in understanding that this will create a .txt file with my information and it will be saved where I have mysqlfrm and the other MySQL Utilities?

Other question is: she attempted this using WampServer 2.4 and MySQL 5.6.12 on Windows 2003 Server. I'm doing this in WAMP on MySQL 5.6.12 on a laptop running Windows 8. Is her method okay to do on Windows 8 or am I not in the same ballpark. I'd like to know before I would try her method.


texts: Is an MDF "Last Modified Date" affected by transaction log backup?

texts:
This is possibly a dumb question, but it has been triggered by a conversation I'm having with a colleague in our Dev team.

My understanding is that the 'Last Modified Date' of the MDF file (in Windows) is only changed when the Database is Closed/Reopened or data is written to the MDF file causing it to grow (eg. by a Full Backup or Transaction Log Backup, assuming database recovery mode is full)

However I've just noticed our hourly log backups are not changing the date of my .mdf files - they are stuck at the date/time of the last full backup. Shouldn't they be changing hourly for active databases?

What if there was no write activity captured in the log during that hour? Would it not affect the .mdf modified date? And if this is the case, would the modified date for readonly databases (databases that are defunct for write purposes but still used for read purposes) ever change?


texts: DR option for SharePoint databases? ideas and experiences

texts:
First let me clarify that I am not looking for thoughts in terms what is the best or the bad options for a DR for SQL Server. I am just looking for for inputs from experienced people.

We are running Virtual Hyper-V and SQL Sever 2012 with FCI. I have three options here.

AlwaysOn (DR).
It seems easy and awesome but it is a new topic for us. I cannot find too much information about it , other challenge is we have a limited support so i'm not sure to go with it unless feel comfortable. we implemented on test environment, but I believe production is a different story.
Database Mirroring (DR)
May be the easiest option to go with. and yes I know it a deprecated feature. but  still supported in SQl Server 2012, so still we have enough time.

I like mirroring because it is recommended from Microsoft before Always-on, and secondly it is good in terms of transactions as commit in primary then directory shipped to mirrored database in DR. easy to monitor and stable.
Log shipping(DR)
The more robust solution as DR. Microsoft use it in office 365, and very good option in case of slow network. It does need some work to configure and a lot of hands-on to prepare. but it is old and reliable. May be is good for non-critical databases where can I put interval like 24 hours. One cool thing is read only databases where I can switch application in read only mode till primary get fixed unlike mirroring.( Yes Always-On does that).

Again it is for DR only and I would like to hear from you. Consider this for SharePoint databases which means only content and other few databases will be replicated as Microsoft support them only.


texts: I get the error "You are attempting to execute a query in which the selected expression is no part of a statistical function." in MS Access 2010?

texts:
Please note: my copy of Access is in Dutch, so any English parts are translated from Dutch.

I'm currently working on a database in Access using SQL, with the exercise being:

Create a query that will list all students who have more than €25 of penalty.

I created this query:

SELECT L.LLNR, U.LLNR, L.VOORNAAM, L.TUSSENVOEGSEL, L.ACHTERNAAM, SUM(U.BOETE)

FROM
    LEERLINGEN L
    INNER JOIN UITLENINGEN U ON L.LLNR = U.LLNR

WHERE (((U.BOETE)>25));

When I click Execute, I get the following error (translated from Dutch)

  You are attempting to execute a query in which the selected expression LLNR is no part of a statistical function.

What should I do?

Translations: |
Leerlingen = students |
Boete = penalty |


texts:
I have two MySQL 5.7 databases ([db1] 5.7.27 and [db2] 5.7.29)
(n.b. db1 is Linux, db2 is Windows)
I have created a basic user on db1:
CREATE USER 'ssltest'@'%' IDENTIFIED BY 'ssltest';
GRANT ALL ON *.* TO 'ssltest'@'%' REQUIRE SSL;

I now connect from db2 to db1:
mysql -h1.2.3.4 -P3306 -ussltest -pssltest

And check the connection:
mysql> show session status like 'ssl_version';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.1 |
+---------------+---------+

mysql> show session status like 'ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+

So, this suggests that my connection is indeed using SSL.
However, if I log onto db1 and run:
SELECT
  sbt.variable_value AS tls_version,
  t2.variable_value AS cipher,
  processlist_user AS user,
  processlist_host AS host
FROM performance_schema.status_by_thread  AS sbt
JOIN performance_schema.threads AS t ON t.thread_id = sbt.thread_id
JOIN performance_schema.status_by_thread AS t2 ON t2.thread_id = t.thread_id
WHERE sbt.variable_name = 'Ssl_version'
and t2.variable_name = 'Ssl_cipher'
ORDER BY tls_version;

I get no results, suggesting that it is not using SSL.
(n.b. If I look in the processlist I can see my connection, so it is definitely connected, and the performance-schema is enabled)
So which one is telling the truth. db1 which is receiving the connection or db2 which is initiating the connection?


texts:
I have this mysql ddl
Create Temporary Table #empData
(
    workDate varchar(100)
  ,numOfCustomers int
  ,numOfSales int
  ,avgTimeToGreetCustomer varchar(100)
);

Insert Into #empData Values
('07/01/20', 16, 16, '00:12')
,('07/02/20', 6, 6, '00:07')
,('07/03/20', 9, 4, '00:18')

Select * from #empData

DROP TABLE #empData

Now what that DDL I need to create a query that will display the data like this
                 07/01/20 --- 07/02/20 --- 07/03/20

numCustomers     16                  6                     9

numSales             16                  6                     4

AvgGreet               00:12             00:07              00:18


texts: Work around average behaviour with nulls

texts:
I have two tables:

Product (PK product_id, brand, model)
Order (PK invoice_id, customer_name, customer_email, FK product_id)

I am trying to find the product(s) whose number of times that product is sold is closest to the average number of times a product is sold.

The problem is as far as I know the AVG function ignores NULL values when it calculates the average. So if a product has never been sold before, it won't be included in the average, but it should be included for my calculation to be correct. A null should be counted as zero to lower the average.

How can I work around this?


texts:
I have defined a column in my MySQL database as :

ALTER TABLE `my_table` CHANGE `desc` `desc` VARCHAR( 255 )
    CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

However, when I enter text that is longer than 233 characters from my front end the data is truncated.

Changing the size of the column to varchar(511) has made no difference.

I counted the number of characters using PHP's strlen() function and it revealed 233 characters.

Why is MySQL doing this, and how can I save the full string?


texts: Can SQL Server Transactional Replication do this?

texts:
We have a production server and a reporting server, with transactional replication in place to move all inserts/updates/deletes from production to reporting. We're using SQL Server 2008 R2.

We have one table with ~2.5B rows in it, and we need to add a new column to the table, and then put a calculated value in the column for each of the 2.5B rows. We'll create a T-SQL script that populates the new column with values. We want the new column and values replicated to reporting.

What we initially considered doing is to create a SQL job that executed every 30 seconds, and the job would have an embedded T-SQL statement that updated a batch of 25,000 rows for each iteration. At this rate, we determined we wouldn't overwhelm replication, even though it would take a long time to backfill all the rows. But this isn't a problem.

We just had one of our DBAs tell us that if we put the T-SQL that updated the 25,000 rows each time it's run, into a stored proc, and put the stored proc on both the production server and reporting server, then replication could be set up so that when the stored proc is run on the production server, the 25K rows that are updated will not replicate to reporting, but the stored proc on the reporting server will get called and update the same batch of rows on the reporting server, thus, essentially, eliminating any chance replication will get backed up on this table because, essentially, nothing is getting replicated.

I'd never heard of this being possible before. Can this be done? The DBA that is recommending this works remotely (out of the country) and I'm not able to talk to him about this at the moment (he's off for a while). But he is an experienced DBA. If replication cannot do this, what might he have in mind?


texts: How to search for a name in two columns in MySQL?

texts:
I want to search a name 'Hamed Haddadi'. 'Hamed' is first name and 'Haddadi' is the last name of the user. When I search individually in the first name and last name columns it shows a record.

My query is:

SELECT *
FROM nbaplayermaster
WHERE firstname LIKE "%Hamed%" OR
      lastname LIKE "%Haddadi%";

But whenever I search his name in two columns, it shows an empty result set in MySQL.

My query is:

SELECT *
FROM nbaplayermaster
WHERE firstname LIKE "%Hamed Haddadi%" OR
      lastname LIKE "%Hamed Haddadi%";

I want to search his full name in two columns.


texts: Mongodb high lock percentage / slow queries

texts:
I'm working with an application using MongoDB that is getting a fairly large number of incoming requests and I've noticed that the responses are getting slower as the number of requests get higher.

I'm suspecting that there is some inefficiency with the database. I ran mongostat during one of our higher load periods and the result seems to indicate high lock percentage:

insert  query update delete getmore command flushes mapped  vsize    res faults           locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn set repl       time
*0   1120    479     *0     179   472|0       0  72.1g   146g  1.31g      0 server-prod:15.7%          0       0|0     1|0     1m     4m   298 rs0  PRI   15:37:28
*0   1192    509     *0     178   428|0       0  72.1g   146g   1.3g      0 server-prod:18.9%          0       0|0     0|0     1m     5m   298 rs0  PRI   15:37:29
*0   1107    456     *0     174   362|0       0  72.1g   146g   1.3g      0 server-prod:20.4%          0       0|0     2|1     1m     5m   298 rs0  PRI   15:37:30
*0   1320    556     *0     211   532|0       0  72.1g   146g   1.3g      0 server-prod:19.5%          0       0|0     0|1     1m     6m   298 rs0  PRI   15:37:31
*0   1094    474     *0     179   449|0       0  72.1g   146g   1.3g      0 server-prod:15.7%          0       0|0     0|0     1m     5m   298 rs0  PRI   15:37:32
*0   1120    487     *0     184   458|0       0  72.1g   146g   1.3g      0 server-prod:20.8%          0       0|0     1|1     1m     5m   298 rs0  PRI   15:37:33
*0    807    299     *0     108   270|0       0  72.1g   146g   1.3g      0 server-prod:15.3%          0     103|1     0|3     1m     3m   298 rs0  PRI   15:37:34
*0   1613    709     *0     161   146|0       0  72.1g   146g   1.3g      0 server-prod:63.5%          0       0|0     1|0     2m     7m   298 rs0  PRI   15:37:35
*0   1133    472     *0     167   341|0       0  72.1g   146g  1.31g      0 server-prod:21.2%          0       1|0     0|1     1m     6m   298 rs0  PRI   15:37:36
*0   1292    555     *0     189   440|0       0  72.1g   146g  1.31g      0 server-prod:23.3%          0       5|0     1|4     2m     6m   298 rs0  PRI   15:37:37

The 3rd line from the bottom has a lock percentage of 60% which is pretty crazy considering the average lock percentage is around ~20%. On higher loads the average goes up to 35-40%.

I also ran show log in my production mongo shell and have not seen a single query that ran below 100ms. Some of the queries have an execution time of ~700ms to 1s.

The machine the database is running on might also be the problem (an n1-standard-1 (1 vCPU, 3.8 GB memory) google compute engine VM instance).
The database is also structured as a three-member replica set (one primary and two secondaries with no arbiter).

What are some ways I can troubleshoot and increase my query speed?

Thanks!

EDIT: Forgot to mention that I'm using Mongo v2.6

EDIT: Here is the output of db.stats()

{
"db" : "server-prod",
"collections" : 20,
"objects" : 36560,
"avgObjSize" : 359.20568927789935,
"dataSize" : 13132560,
"storageSize" : 43479040,
"numExtents" : 62,
"indexes" : 24,
"indexSize" : 6189232,
"fileSize" : 67108864,
"nsSizeMB" : 16,
"dataFileVersion" : {
    "major" : 4,
    "minor" : 5
},
"extentFreeList" : {
    "num" : 0,
    "totalSize" : 0
},
"ok" : 1

}

EDIT: Last night I saw my CPU go up to 128% during peak load hours, how is this possible? (This is a single core machine)

EDIT: I created indexes on some of the most queried fields and noticed an increase in performance, however, the update queries are still taking way too long (~500-800ms). What are some optimisations I can do to increase update/insert performance?


texts: How to get more details about root cause of failed Database Mail SSL connection

texts:
I'm trying to send database mail over SSL/TLS-encrypted SMTP. It works when sending plain text email, but fails when I enable SSL/TLS.

The database mail log shows the following error message:

Date        6/02/2019 11:07:09 AM
Log     Database Mail (Database Mail Log)

Log ID      11
Process ID      9476
Mail Item ID    6
Last Modified   6/02/2019 11:07:09 AM
Last Modified By    sa

Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2019-02-06T11:07:09). Exception Message: Cannot send mails to mail server. (The remote certificate is invalid according to the validation procedure.).
)

How can I find out more information about why SQL Server doesn't like the certificate?


texts: Concatenate all values of the same XML element using XPath/XQuery

texts:
I have an XML value like this:

<R>
  A
  B
  C
  ...
</R>

I want to concatenate all I values and return them as a single string: ABC....

Now I know that I can shred the XML, aggregate the results back as a nodeless XML, and apply .values('text()[1]', ...) to the result:

SELECT
  (
    SELECT
      n.n.value('text()[1]', 'varchar(50)') AS [text()]
    FROM
      @MyXml.nodes('/R/I') AS n (n)
    FOR XML
      PATH (''),
      TYPE
  ).value('text()[1]', 'varchar(50)')
;

However, I would like to do all that using XPath/XQuery methods only, something like this:

SELECT @MyXml. ? ( ? );

Is there such a way?

The reason I am looking for a solution in this direction is because my actual XML contains other elements too, for instance:

<R>
  A
  B
  C
  ...
  <J>X</J>
  <J>Y</J>
  <J>Z</J>
  ...
</R>

And I would like to be able to extract both the I values as a single string and the J values as a single string without having to use an unwieldy script for each.


texts: Postgres10 picks very slow query plan, when faster plans seem available

texts:
we are using Postgres 10.

We are seeing a situation where, for the same query, with one value it picks a super ineficient query plan, but with another value it picks a good query plan.

SELECT
   "sfe_procedure"."*"
FROM "sfe_procedure" INNER JOIN "sfe_claim"
   ON ("sfe_procedure"."claim_id" = "sfe_claim"."id")
WHERE (
   "sfe_procedure"."patient_id" = 100
   AND "sfe_claim"."from_date" >= '2011-01-01'::date
   AND "sfe_claim"."from_date" <= '2020-02-13'::date
   AND "sfe_procedure"."code" IN (
       SELECT U0."code"
       FROM "medical_codes_hediscode" U0
       WHERE U0."value_set_name" = 'Colonoscopy'
   )
)
ORDER BY "sfe_claim"."from_date" DESC LIMIT 1;

It picks this query plan:

 Limit  (cost=1001.30..5784.38 rows=1 width=46)
   ->  Nested Loop Semi Join  (cost=1001.30..2459500.48 rows=514 width=46)
         Join Filter: ((sfe_procedure.code)::text = (u0.code)::text)
         ->  Gather Merge  (cost=1000.89..2455885.11 rows=4369 width=46)
               Workers Planned: 2
               ->  Nested Loop  (cost=0.86..2454380.80 rows=1820 width=46)
                     ->  Parallel Index Scan Backward using sfe_claim_from_da_3d3cc6_idx on sfe_claim  (cost=0.43..771392.17 rows=1427744 width=8)
                           Index Cond: ((from_date >= '2011-01-01'::date) AND (from_date <= '2020-02-13'::date))
                     ->  Index Scan using sfe_procedure_claim_id_f0f8a72b on sfe_procedure  (cost=0.43..1.17 rows=1 width=42)
                           Index Cond: (claim_id = sfe_claim.id)
                           Filter: (patient_id = 100)
         ->  Materialize  (cost=0.42..11.08 rows=55 width=7)
               ->  Index Scan using medical_cod_value_s_e3a75d_idx on medical_codes_hediscode u0  (cost=0.42..10.81 rows=55 width=7)
                     Index Cond: ((value_set_name)::text = 'Colonoscopy'::text)
(14 rows)

Notice especially this row:

Parallel Index Scan Backward using sfe_claim_from_da_3d3cc6_idx
  on sfe_claim  (cost=0.43..771392.17 rows=1427744 width=8)

Which seems to suggest that its best bet is to load 1.4 mil rows, and take 771 seconds? Am I reading this wrong?

On a different value for patient_id, the query plan is sane and the query is blazing fast:

Limit  (cost=4173.67..4173.67 rows=1 width=48)
  ->  Sort  (cost=4173.67..4174.12 rows=180 width=48)
        Sort Key: sfe_claim.from_date DESC
        ->  Nested Loop  (cost=18.73..4172.77 rows=180 width=48)
              ->  Hash Semi Join  (cost=18.29..2722.51 rows=180 width=44)
                    Hash Cond: ((sfe_procedure.code)::text = (u0.code)::text)
                    ->  Index Scan using sfe_procedure_patient_id_4bcdc9ad on sfe_procedure  (cost=0.43..2677.30 rows=3829 width=44)
                          Index Cond: (patient_id = 200)
                    ->  Hash  (cost=16.82..16.82 rows=83 width=7)
                          ->  Index Scan using medical_cod_value_s_e3a75d_idx on medical_codes_hediscode u0  (cost=0.42..16.82 rows=83 width=7)
                                Index Cond: ((value_set_name)::text = 'Colonoscopy'::text)"
              ->  Index Scan using sfe_claim_pkey on sfe_claim  (cost=0.43..8.06 rows=1 width=8)
                    Index Cond: (id = sfe_procedure.claim_id)
                    Filter: ((from_date >= '2011-01-01'::date) AND (from_date <= '2020-02-13'::date))"

The DB has had ANALYZE run on it recently, so it doesn't seem like the DB is confused about the amount of work that it's signing up for... but it still seems to pick that.

Questions

Is there a way to understand why other query plans were not picked?
Am I missing something obvious - a multiple-index doesn't seem to work - that could help Postgres see a better plan faster?
Do I need to rewrite the query?

Thank you for any help or pointers!


texts: How to Migrate Oracle 11g R2 (Windows) database to Oracle 12c R2 (Linux) db

texts:
I have a requirement to migrate Oracle 11g R2 database (Windows platform) to Oracle 12c R2 database (Red Hat Linux platform).

Is there any methods using RMAN to do it?

Any help will be appreciated.


texts: How to pivot a column showing its content as new columns

texts:
I'm working with MySQL 5.6 and I'm having headaches with this issue. I've been looking in several groups the result, but I think this isn't very common.

I have a table like the following one:

direc       pro_id  program                                       project
----------  ------  ---------------------------------------       -------
31111-0201  191     Empresa Exportadora                           K0002
31111-0201  192     Apertura de Empresas                          K0002
31111-0201  223     Promoción y Atracción de Inversiones...       K0002
31111-0201  191     Empresa Exportadora                           K0003
31111-0201  192     Apertura de Empresas                          K0003
31111-0201  223     Promoción y Atracción de Inversiones...       K0003

31111-0501  190     Ingreso de detenidos en centro de detención   E0045
31111-0501  190     Ingreso de detenidos en centro de detención   E0159
31111-0501  190     Ingreso de detenidos en centro de detención   O0059
31111-0501  190     Ingreso de detenidos en centro de detención   P0042
31111-0501  190     Ingreso de detenidos en centro de detención   R0043

31111-0601  224     Administración de servicios básicos           O0060
31111-0601  228     Análisis Jurídico en las liquidaciones        O0060
31111-0601  232     Administración de seguros por siniestralidad  O0060
31111-0601  224     Administración de servicios básicos           U0135
31111-0601  228     Análisis Jurídico en las liquidaciones        U0135
31111-0601  232     Administración de seguros por siniestralidad  U0135

I need a kind of pivot, but every project would be a new column and the result could be 1 or n columns (projects), the content of those columns would be a zero (that will be captured with percentages).

I've separated with a line the data cause the result I'm looking for is by direc, in this case I need to show as columns the projects K0002 and K0003 for the 31111-0201 direc, the 31111-0501 direc have the result of five and that would be columns and so on.

Following this sample the result I need is like this (I did a dynamic table in Excel for the sample):

Thanks in advance for your help.


texts: Manual SCSU 'compression' in SQL Server 2017 for NVARCHAR(MAX)

texts:
SQL Server has the wonderful SCSU "compression" gem that works on NVARCHAR(1-4000) types. The usage of SCSU effectively means that the physical storage space used is approximately that of VARCHAR(1-4000) (at least for Latin-ish langauges) while still supporting Unicode.
https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/unicode-compression-implementation
However, SCSU does not apply to NVARCHAR(MAX) columns, regardless of if they are in-row or not.
The initial issue is avoided by splitting the column in two: NVARCHAR(3900) and NVARCHAR(MAX), and using COALESCE during reading. This is OKAY.
However, the problem is: as soon as the values are over 3900 characters the storage requirement "doubles" (ignoring any overheads associated with LOB) as SCSU does not apply. Even applying COMPRESS is not very helpful as it is already working from a "double size" deficit (it would still be ideal to use!).
Is there a way in SQL Server 2017 to achieve something similar to the following with the goal of minimizing the physical storage requirements?
 COMPRESS(
    SCSU(
       N'data')
    ) -- results in VARBINARY(MAX) after SCSU
 ) -- result in VARBINARY(MAX) after compression of SCSU binary

This data has a very low read/usage rate per volume, and even meager space savings per row are preferable. (Yes, it's a business 'need'.)
The best "alternative" I can come up with is to attempt to convert to VARCHAR(MAX) before compression when only ASCII is used. While this covers 99% of the current data, it won't benefit when any Unicode values are used and is a bit more of a kludge to implement due to needing additional state. It also requires an additional implicit conversion from VARCHAR to NVARCHAR in usages.
It is not possible to use UTF-8 collation (since 2019+) or to use CLR modules.

The extra cost can be seen here. A better Unicode encoding before compression could result in a potential 'significant' amount of physical space savings.
The usage of SCSU-before-COMPRESS would also greatly help with compression on smaller values of only a few hundred characters. Here the arbitrary value chosen is 3900, as that's roughly the maximum length SQL Server can encode using SCSU as it writes to the table.
-- 3864 random hex characters.
-- While compression is relatively good, it's even better when
-- starting with a single-byte encoding (or SCSU as it applies).
nvar_bytes  var_bytes   compress_nvar   compress_var
7728        3864        2841            2222

-- 3860 random codepoint'ish 0..255, expected not COMPRESS'ible.
nvar_bytes  var_bytes   compress_nvar   compress_var
7720        3860        5264            3883


texts: Restoring database between different version of SQL Server (SoftwareVersionBuild)

texts:
i have a question about restoring database from newer version of SQL Server to older, when version differs only in SoftwareVersionBuild.
I know that's not possible to restore database from SQL Server 2012 to 2008, or from 2008 R2 to 2008 etc. but how about situation when i have to restore DB from SQL Server 2008 R2 SP2 (10.50.4033) to version 10.50.2500? (2008R2 SP1).
Well, I know that you'll ask why i just won't try to do it, but unfortunately i don't have access to server with 2008R2 SP1 version, and i'll have to deploy my DB on it soon.

I installed SQL Server 2008 R2 on freshly craeted virtual machine, but exact version of freshly installed SQL Server is 10.50.4000. I managed to successfully restore my test DB from version 10.50.4033 to 10.50.4000, so I guess that it'll work to restore it on version 10.50.2500, too, but I would like to just be sure.
I tried to find information about restoring SQL databases when SQL Server version differs only in SoftwareVersionBuild, but i couldn't find answer.


texts:
I'm trying to execute the following statement in MySQL:

  INSERT IGNORE INTO shop_item_details (shop_id,product_id,product_name,Product_model,Product_category,Product_details,price)
VALUES (1, NULL, 'camera', 'sony','', 'hd', '5000')
ON DUPLICATE KEY
UPDATE (shop_id='1',product_id='',product_name='camera', Product_model='sony',Product_category='', Product_details='hd',price='5000')

But it produces an error:

  #1064 - 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 '(shop_id='1',product_id='',product_name='camera', Product_model='sony',Product_c' at line 1

What is wrong with my query?


texts: Postgresql BDR Update Update conflict detection

texts:
Apparently only the new values of rows are sent between nodes on update.  So how can there ever be detectable conflict?   Node A receives an update from Node B, and a different update from node C.  How can A know that there was a conflict?  That the B and C updates were not conducted in some well defined order?

And what does "last-update-wins" mean?  Are the updates timestamped?  In which case does it rely on synchronized clocks?  Or does it just mean whichever update happens to get to Node A last wins?

SUMMARY

Last Update Wins means based on commit time (requiring fairly synchronized clocks), and not the time a node sees an update.  So should be globally consitant.
There aren't any Update/Update conflicts that are detectable (except constraint violations).  And they would be difficult to resolve if they were.
Conflict resolution is row by row, not transaction by transaction.  Also see http://sdf.org/~riley/blog/2016/01/04/is-bi-directional-replication-bdr-in-postgres-transactional/


texts:
I have a sqlite3 database with many tables which have same columns. It's a dictionary database, and each table name is starting word letter.

I would like to list single column from all tables in database, but couldn't find a way to do so.

From sqlite3 CLI client, something like:

sqlite3 -line my_db.db 'select my_column from *'

Of course the above does not work.


texts: Issue with Identifiers in Postgres greater than Oracle's limit while connecting over ODBC (DBLINK)

texts:
We have a database link from Oracle 12.1 to Postgres via psqlODBC Driver. There is a problem while running few queries/statements when the Postgres identifiers, like the length of a column name exceeds 30 characters, which is the maximum limit of column names until Oracle 12.1.

  ORA-04052: error occurred when looking up remote object
  myschema.mytesttable@ORA_TO_PGLINK ORA-01948: identifier's name
  length (32) exceeds maximum (30) ORA-06512: at line 13
  04052. 00000 -  "error occurred when looking up remote object %s%s%s%s%s"
  *Cause:    An error occurred when trying to look up a remote object.
  *Action:   Fix the error.  Make sure the remote database system has run
             CATRPC.SQL to create necessary views used for querying or looking up
             objects stored in the database.

This error occurs even if  the statement does not refer the column explicitly. The statement we use basically updates another column which has a normal length(<= 30 characters) through an update statement over PL/SQL.

BEGIN
  UPDATE "mytesttable"@ORA_TO_PGLINK
     SET "col" = 10
   WHERE "col" = 1;
END;

Here the col is not the one whose length is greater than 30 characters in Postgres.
Also, the statement works fine while running as a normal SQL statement, ie. without an anonymous block or inside a procedure.

Now, of course we can fix this issue by renaming the column name in Postgres to have a length <= 30 characters. But, we do not want to do that because it has several other dependancies. As far as Oracle db is concerned, the update is part of a PL/SQL procedure  and can't be run as a single statement directly. What is the solution for this? Is there a workaround or some settings of the driver  be modified?


texts: Approximate MySQL Query Time For A Where Clause On A 10 Million Records Table

texts:
I'm looking forward to designing a database (in MySQL) but before I start I need to have some calculations to make sure that my application will remain efficient even if there are 10 Million+ rows (either in a single table or combined with other tables).

I'm a person with a very little knowledge of database administration and query construction. I searched on Google and read few questions at Stackoverflow about 'creating a new table for each user or using one table with a user_id column to separate each user's data'.

It seems that the question I'm going to ask is more like 'Need directions' then 'approximation of MySQL query time'.

If I go with only one database-table design then the table will have structure like this:-

+----------------------+---------+-------+-------+-------+-------+
| col_with_primary_key | user_id | col_1 | col_2 | col_3 | col_4 |
+----------------------+---------+-------+-------+-------+-------+
|                    1 | A       | aa    | bb    | cc    | ll    |
|                    2 | A       | dd    | cc    | ff    | dd    |
|                    3 | B       | zz    | dd    | yy    | xx    |
|                    4 | C       | ii    | yy    | kk    | tt    |
+----------------------+---------+-------+-------+-------+-------+

The user_id is differentiating which row is of which user.

Approximately, how much time will it take for MySQL to run the following query, given 10 Million records in the table.

SELECT *
FROM one_table
WHERE user_id = 'A'


texts:
I want to create a cross tab query for my table. When I use CONCAT I am unable to get output successfully. Can anyone help me with my query?

    SET @sql = NULL;
    SELECT GROUP_CONCAT(DISTINCT CONCAT('CASE WHEN TX_STOP_NAME=''', TX_STOP_NAME, '''  THEN
    concat('TIME:-',s.TS_ACTUAL_ARIVAL,' HALT FOR:- ',s.NO_HALT_DUR,' STOP NAME:-',s.TX_STOP_NAME) HALT
    else null  END AS `', concat('TIME:-',s.TS_ACTUAL_ARIVAL,' HALT FOR:- ',s.NO_HALT_DUR,' STOP NAME:-',s.TX_STOP_NAME) HALT , '`'))
    INTO @sql
    FROM cmn_trip_statistics;
    SET @sql = CONCAT(' select *from(SELECT s.FKYS_TRIP_ID,s.FKYS_REPORT_TYPE,sum(s.NO_HALT_DUR) as totalhalt, ', @sql, '
    FROM cmn_trip_statistics s
    group by s.FKYS_TRIP_ID,S.TX_STOP_NAME)T where FKYS_REPORT_TYPE=''HALT''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;

DEALLOCATE PREPARE stmt;


texts: Is Azure Managed Disks enough to ensure high-durability for a database?

texts:
I want to set up a database in a high durability set-up on Azure. I've previously relied on DB-as-a-service offerings, but can't do that in this case, so I'd like your feedback on the plan below. Is this enough to ensure reliable storage of data?

An Azure Web App takes in metric data from the web, does some minor processing and sampling, and sends the data in batches to VM2.
VM2 runs the Clickhouse database, and stores data on an Azure Managed Disk
Some periodical job takes snapshots of the disk using Clickhouse built-in backup functionality and stores them to cold storage

The periodical backup is meant to mitigate human error, i.e. accidentally running "DROP TABLE xx" on the wrong data.
The big question is if managed disks are an acceptable substitute for database replication, to ensure data durability. Azure Managed Disks are advertised as being very durable forms of storage, with built in triple-redundant replication. They are advertised as good for database use. It seems that this should be enough to take away any concerns of data loss due to hardware failure. Is this correct? Do you see any potential problems with this?
The recovery plan is that if VM2 fails, some monitoring process catches this and spins up a new VM2 instance attached to the same managed disk. The Web App similarly restarts if it fails.
I understand that this setup isn't high-availability, if a VM fails there will be some window of time before it is able to store new data. This is acceptable to me. But I want to ensure that data that gets stored will not be lost, i.e. is durably stored with very high probability. Is this enough to ensure that? Do you see any problems?


texts: microsoft webmatrix deleted my wamp databases?

texts:
I've been using WAMP server for a while and I have several databases on the mysql server.

today I installed microsoft webmatrix and when I opened one of my sites on wamp I got a mysql error saying the connection has failed, and when I try to open the phpmyadmin it says access denied.

#1045 - Access denied for user 'root'@'localhost' (using password: NO)

I guess it somehow changed the password for my user, so how can I reconnect to wamp's phpmyadmin and restore my old databases?

Currently I can connect to the server with the password ' ' (space) but my databases are gone.

EDIT: ok I changed the password back to '' (null) using an sql command

SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

is it possible to restore the old dbs?


texts: Check for duplicates in column and determine which row to keep

texts:
I've found myself running in to this on multiple occasions now where I got a query and would need too further refine it. Where one column might contain duplicates and I need to evaluate which row to keep and which rows needs to be discarded.

So far I've had trouble to apply what I've found on my queries as most examples contains selections from one or two tables where my queries are three or more.

I've tried using group by and having count as these examples show but I just
get a lot of aggregate errors.

I think what I'd like to do would be to use the table created from my query and then query that one if it's possible and basically cull rows which contains duplicates in one of the columns depending on some criteria. For example if one row contains a datetime remove all except the newest.

SELECT
    stamps.stampsnr AS 'stampsnr' ,
    stamps.time AS 'time' ,
    stamps.amount AS 'amount' ,
    products.productnamn AS 'productnamn' ,
    products.Artikelnummer AS 'Artikelnummer' ,
    FContainer.id AS 'FlaggId' ,
    FContainer.FlaggId AS 'Flagga' ,
    Tempo.tempo AS 'nuTempoNr' ,
    Tempo.amounttempo AS 'amountTempon'
FROM
    stamps
INNER JOIN Tempo
ON  stamps.temponr = Tempo.temponr
INNER JOIN products
ON  Tempo.productnr = products.productnr
INNER JOIN FContainer
ON  stamps.ID = FContainer.id
WHERE
    ( stamps.time > '" & dtmYesterday & "' + ' 06:00:00' )
    AND ( stamps.time < '" & dtmNow & "' + ' 06:00:00' )
    AND FContainer.flaggid = 5
ORDER BY
    time;

Here is an example query. Now on the table created I'd like to check the column renamed to flaggID for duplicates if any found remove every row except the newest and the time is stored in stamps.time.


texts: Why is there such a huge performance difference in these two join statements?

texts:
I have a query which i first wrote as (query has been simplified a bit) :

select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c
inner join product p on p.indID = c.id or p.catid = c.id and p.deleted = 1 and p.statusid = 1
group by c.name

This took 8 seconds.

If i changed it to the following it runs in under 1 second:

   select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c
   inner join product p on p.indID = c.id or p.catid = c.id
   where p.deleted = 0 and p.statusid =1
group by c.name

I'd like to understand why there's such a huge difference in performance. Not only is the performance worse but it seems to be ignoring my conditional statements on the first sql query it's counting records regardless of what p.deleted or p.statusid is

What I am not understanding about how joins work? This is being run on mssql 2012


texts: New database not using the model configuration

texts:
On a SQL Server 2008 R2 Express instance, I have the "auto Close" option set to "False" on the [model] database.

However, When I execute CREATE DATABASE [test];, SQL Server creates the database with the auto close option set to true.

I checked sys.configurations just to make sure there was nothing pending (not the case).

I also tried to change the config on [model] to true, save, then change it back to false but I still have the same behavior.

If I create the database using the SSMS GUI (right click -> new database), it creates the database with the auto close option set to false

How can I make CREATE DATABASE create a database with auto close set to false?


texts: Move SQL Server XML column to a new table - slow performance

texts:
I have a table with a XML Column, each XML document size is 15kb and I have like 2 million rows.

I need to move this XML column into a new table. I tried:

insert into newtable
    select xmlcolumn
    from originaltable

but is taking too long.

Any other way to do it? What's the best way to achieve this?


texts: Multiple service_name and hosts with same alias

texts:
I am currently working on a duplicated infrastructure like this :

We have two data server, with two database on each.

Server 1

Oracle instance 1
Oracle instance mirror 1

Server 2

Oracle instance 2
Oracle instance mirror 2

I cannot change the instance name on the database.

DWH_connection =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Server1)(PORT = 1521)
      (ADDRESS = (PROTOCOL = TCP)(HOST = Server2)(PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Oracleinstance1)
      (SERVER = DEDICATED)
    )
  )

Here is what I know we can do, but I am stuck and can't find any docs. Maybe I am bad at searching, but can't find a way.

I would like to get the Server 1 - Oracle instance 1 with the same alias as Server 2 - Oracle instance 2. So in case Server 1 is not up, our application will go the Server 2. So this is what I would like to have:

DWH_connection =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Server1)(PORT = 1521)
      (ADDRESS = (PROTOCOL = TCP)(HOST = Server2)(PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Oracleinstance1)
      (SERVICE_NAME = Oracleinstance2)
      (SERVER = DEDICATED)
    )
  )


texts: How does max_allowed_packet affect the backup and restore of a database?

texts:
I have a database based on mysql 5.0.75-0Ubuntu10.2, running under Ubuntu 9.04. I have a table in that database with a medium blob column. I have been using automysqlbackup to do nightly backups/dumps of the database, then restoring them periodically to either a staging server we have or to my MacBookPro.

We recently had some trouble restoring with errors related to the packet size. Late last week, I changed the max_packet_size setting within automysqlbackup to 1MB (it was unspecified). After that change, the database backups stop at the table with the blob column. Actually, it stopped with the first record in that table. Do I have to set the max_packet_size setting to be at LEAST as large as the largest blob stored in that column?


texts: What should I set max_connections to for MySQL?

texts:
I have MySQL 5.1.35 on Linux Centos.  The Linux server has 2GB RAM with 14GB of disk space.

I have created some web services using the Restlet framework in Java that has 1,000+ users.

What should I set max_connections to for maximum concurrent connections?


texts:
I wrote a simple procedure to modify the whole column to a set of meaningless values. However, when I tried

call NewProc('tableName','colName')

it returned

  1064 - 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 'NULL' at line 1". The error may occur in the "set @sql0=CONCAT('select count(*) into ',@i,' from ',tab1);

but I have no clue why it's the problem.

CREATE DEFINER = root@% PROCEDURE NewProc (IN tn VARCHAR(20), IN mc VARCHAR(20))
BEGIN
  DECLARE j INT;
  DECLARE tab1 VARCHAR(20);
  DECLARE col1 VARCHAR(20);
  DECLARE stmt VARCHAR(1000);
  SET tab1:=tn;
  SET col1:=mc;
    set @sql0=CONCAT('select count(*) into ',@i,' from ',tab1);
    PREPARE stmt FROM @sql0;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET j=1;
WHILE j<@i DO
    SET @sql1=CONCAT("update ",tab1," set ",col1,"='565656' where id=",j);
    PREPARE stmt FROM @sql1;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET j=j+1;
END WHILE;
END;


texts: Speeding up a simple select query on Mariadb

texts:
I have a 100k rows table with the following structure:

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(11)          | NO   | PRI | NULL    | auto_increment |
| user_id  | int(11)          | NO   | UNI | NULL    |                |
| success  | int(10) unsigned | NO   |     | NULL    |                |
| fail     | int(10) unsigned | NO   |     | NULL    |                |
| points   | int(11)          | NO   | MUL | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

This table holds users' scores in a game. All fields' values other than id's and user_id's are constantly changing on every user event.

A simple select used for displaying the top scoring users on this table runs in 0.3s on production.

MariaDB [db]> select user_id, points from state where points > 100000 ;
...
119 rows in set (0.31 sec)
MariaDB [db]>show profile for query 3;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000090 |
| checking permissions | 0.000018 |
| Opening tables       | 0.000030 |
| After opening tables | 0.000014 |
| System lock          | 0.000015 |
| Table lock           | 0.000013 |
| After table lock     | 0.000016 |
| init                 | 0.000027 |
| optimizing           | 0.000021 |
| statistics           | 0.000029 |
| preparing            | 0.000034 |
| executing            | 0.000012 |
| Sending data         | 0.307494 |
| end                  | 0.000041 |
| query end            | 0.000018 |
| closing tables       | 0.000036 |
| freeing items        | 0.000015 |
| updating status      | 0.000038 |
| logging slow query   | 0.000068 |
| cleaning up          | 0.000034 |
+----------------------+----------+

For the points column is ever changing, I did not want to create an index for it. And this behaviour is expected. But a full select, without a where condition runs for 0.4s either.

MariaDB [db]> select user_id, points from state;
...
126851 rows in set (0.40 sec)

If I create an index for the points column, it will most probably result in great amount of IO operations on the server, but It will speed up the top scores list. If I filter and sort the list in server side code, I can speed up the list, but this time mariadb does not provide the result in a shorter time.

How can I speed up this simple select query?


texts: PostgreSQL - PUBLICATION tries to copy the data which already exists in another database?

texts:
I have database and table collection_city on one server. It has 21 rows.
I have database and table collection_city on second server. It also has 21 rows.

They both have this row:

tinker=# table collection_city;
 id |     name      |    alias     | postal_code | region_id
----+---------------+--------------+-------------+-----------
  2 | Obrenovac     | obrenovac    |             |         1

id column is the primary key.

I created Publication on first server:

CREATE PUBLICATION tinkerpub FOR ALL TABLES;

tinker=# \dRp[+]
                      Publication tinkerpub
  Owner   | All tables | Inserts | Updates | Deletes | Truncates
----------+------------+---------+---------+---------+-----------
 postgres | t          | t       | t       | t       | t
(1 row)

I created Subscription on second server:

CREATE SUBSCRIPTION tinkersub CONNECTION 'dbname=tinker host=192.168.150.5 user=postgres password=test port=5432' PUBLICATION tinkerpub;

But I see errors like this (it is trying to copy the row with id 2 AGAIN???):

2019-06-25 15:51:37.178 CEST [2270] ERROR:  duplicate key value violates unique constraint "collection_city_pkey"
2019-06-25 15:51:37.178 CEST [2270] DETAIL:  Key (id)=(2) already exists.
2019-06-25 15:51:37.178 CEST [2270] CONTEXT:  COPY collection_city, line 1
2019-06-25 15:51:37.181 CEST [21905] LOG:  background worker "logical replication worker" (PID 2270) exited with exit code 1

Why system is trying to copy the data which already exists with exactly the same data?
I see errors for all tables.
How we can resolve this? Does the second table should be totally empty before the subscription process? But it does not have any sense...why I should not have both tables populated and then to start the process of replication at one moment?


texts: Designing an extensible authentication system

texts:
I need to design an authentication system, where different type of authentication schemes may exist. At the end, I should assign a passphrase (access token) to the user.

I have several authentication schemes right now, and design should be extensible with new authentication schemes. What I've currently is:

Insecure (direct)
One time code over SMS
Time-based code via external authenticators (i.e. Google Authenticator)

To clarify how does those schemes work, I will give some examples:

Insecure authentication is performed by only user credentials. Those credentials are username and password. All users have those two credentials.

One time code over SMS is another authentication scheme, having taken users credentials, a one time code is generated by the server, and sent to the user's phone via SMS. Afterwards, user supplies that one time code to the server in order to complete the authentication. In practice, time-based code is no different than this authentication scheme.

Here are the DDTs of my current design (please note that they're canonical):

CREATE TABLE users(
    id serial PRIMARY KEY, -- surrogate key
    username character varying (25) NOT NULL,
    inserted_at timestamp without time zone DEFAULT now() NOT NULL
);

-- Those tables are middleware artifacts
CREATE TABLE one_time_codes(
    id serial PRIMARY KEY, -- surrogate key
    code integer NOT NULL,
    inserted_at timestamp without time zone DEFAULT now() NOT NULL,
    user_id integer REFERENCES users(id) NOT NULL
);

CREATE TABLE insecure_authentication_handles(
    id serial PRIMARY KEY, -- surrogate key
    passkey character varying (255) NOT NULL,
    inserted_at timestamp without time zone DEFAULT now() NOT NULL,
    user_id integer REFERENCES users(id) NOT NULL
);

-- Final product of successful authentication
CREATE TABLE passphrases(
    id serial PRIMARY KEY, -- surrogate key
    passkey character (5) UNIQUE NOT NULL,
    inserted_at timestamp without time zone DEFAULT now() NOT NULL,
    user_id integer REFERENCES users(id) NOT NULL
);

As you may understand, I used Martin Fowler's "Concrete Table Inheritance" in order to represent the concrete OneTimeCode and InsecureAuthenticationHandle subclasses of AuthenticationScheme abstract class in the database. Here, each passphrase could be matched with either one_time_code or insecure_authentication_handle depending on users preference of authentication. one_time_codes and insecure_authentication_handles are the middleware artifacts of the authentication, hence I created a view to list all of those artifacts.

CREATE VIEW concrete_authentication_middleware_artifacts AS
    SELECT user_id, NULL insecure_authentication_handle_passkey, code one_time_code, inserted_at
    FROM one_time_codes

    UNION

    SELECT user_id, passkey insecure_authentication_handle_passkey, NULL one_time_code, inserted_at
    FROM insecure_authentication_handles;

Hence, if I want to pair each middleware artifact with successful authentication artifact (passphrase), I would execute the query (assume all artifacts have a valid duration of 3 minutes):

SELECT cama.*, p.passkey passphrase
FROM concrete_authentication_middleware_artifacts cama
INNER JOIN passphrases p ON cama.user_id = p.user_id AND (GREATEST(p.inserted_at, cama.inserted_at) - LEAST(p.inserted_at, cama.inserted_at) < '3 minutes'::interval);

Everything works great, however:

I could use foreign keys instead of comparing timestamps in order to join passphrases table and the view, however that would lead to creating n foreign keys in passphrases table, whereas only one may have non-NULL value. I'm also unaware of creating external predicates in order to ensure database integrity on such conditions.
The design does not favor the latter query with huge amount of data. This may not be a problem since it's executed rarely.

What kind of suggestions do you have? I'm not sure I reached to the most elegant design.


texts:
Most of the forum and example online always suggest to have both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT set to ON whenever someone is asking snapshot, row versioning or similar question.

I guess the word SNAPSHOT in both setting get a little confusing. I thought that, in order for database engine to use row versioning instead of locks for READ_COMMITTED default behavior, the database READ_COMMITTED_SNAPSHOT is set to ON regardless of what ALLOW_SNAPSHOT_ISOLATION setting.

The ALLOW_SNAPSHOT_ISOLATION setting is set to ON only to allow snapshot isolation when starting a transaction (e.g. SET TRANSACTION ISOLATION LEVEL SNAPSHOT) regardless of READ_COMMITTED_SNAPSHOT setting.

The only reason to have these two settings set to ON is when it needs to have READ COMMITTED row versioning AND  snapshot isolation.

My question is, is my understanding incorrect in some way? And that these two setting have to be always set to ON together (especially for READ COMMITTED row versioning)?


texts: Master-slave + master-master replication at the same time

texts:
Can a MySQL database which is doing master-slave replication to a number of slaves be configured to do master-master replication to a new server at the same time?

I have an application that has a main database (master1) and a number of front-end servers, each with a local slave database (slave1...n). These are doing filtered replication, filtering on slave with replicate-do-db and replicate-wild-ignore-table.

I'm trying to find a way to upgrade the master database without downtime. I need you to review and possibly confirm that the following would work.

Configure new server (master2) and import data from master1.
Turn on master-master replication so master2 replicates everything from master1
On each front-end server, change master to master2
Switch DNS record for main database from master1 to master2
Take down master1

Is this possible? Is there anything I should be paying attention to?


texts:
How can I confirm that JDBC is running and properly configured/registered in the system? I followed the installation instructions but I believe there has to be more to it than what the instructions show. It just says to extract sqljdbc__enu.exe (it's an executable zip file) into "C:\Program Files\Microsoft JDBC Driver 4.0". Ok? Is that all? I'm trying to setup OEM to work with SQL Server and it requires JDBC I'm unable to connect any databases for monitoring so I believe the issue is with JDBC. Any help is appreciated.

JDBC Install Instructions for Windows:

  Read the license.txt file. Downloading and using the Microsoft JDBC Driver 4.0 for SQL Server signifies acceptance of this license.

  Download the sqljdbc__enu.exe to a temporary directory.

  Run sqljdbc__enu.exe

  Enter an install directory when prompted; it is recommended that you unpack this zip file in %ProgramFiles% with the default directory:

  Microsoft JDBC DRIVER 4.0 for SQL Server

  After the package unpacks, open the JDBC Help System by opening %InstallationDirectory%\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_\ help\default.htm. This will display the help system in your Web browser.


texts:
Using Microsoft SQL Server Management Studio 2012

I need some help in improving my db structure.

Currently I have a table that looks kinda like this:

Id       Table_Name         Table_Id        Next_Id
1        Login              1               2
2        Logout             10              3
3        Close              2               4
4        Open               3               null
5        Login              4               null
6        Login              3               7
7        Logout             4               8
8        Close              9               null
9        Login              2               10
10       Logout             3               null

Each row has a unique Id, a not null Table_Name, not null Table_Id and a Next_Id.

The idea behind this table is, that the user can add a "sequence" for my desktopapplication. If we look at Id 1 for example, we will see the Table_Name Login which contains the value Login and the Table_Id 1. My programm will now read the row in the Table Login that contains the Id 1
(Login will contain logininformation and some other stuff) and will afterwards look into the colum Next_Id to check if this is the end of the sequence. Since Next_Id contains the number 2 my programm will now read the row 2 of this table and will continue to do so until it hits the Next_Id value null

Is there any way I can improve my solution attempt in any way? If there are still questions regarding what I tried to do with this table then go ahead and ask :)


texts: How to installation spark standalone mode in ubuntu

texts:
I am trying to install spark standalone but show error.
How i can solve this problem.

Java version :- 1.8.0_131
Spark:- 2.2.0
Hadoop: 2.7.4
bashrc file setting
Hadoop file location in local System: /usr/lib/hadoop/hadoop-2.7.4
Spark file location: /opt/spark/spark

File:

-----------------------------
#JAVA HOME directory setup
export JAVA_HOME=/usr/lib/jvm/java-8-openjdk-amd64
export PATH=$PATH:$JAVA_HOME/bin

#HBASE HOME setup
export HBASE_HOME=/usr/lib/hbase/hbase-1.3.1
export PATH=$PATH:$HBASE_HOME/bin

#HADOOP Setup
export HADOOP_HOME=/usr/lib/hadoop/hadoop-2.7.4
export HADOOP_CONF_DIR=$HADOOP_HOME/etc/hadoop
export HADOOP_INSTALL=$HADOOP_HOME
export HADOOP_MAPRED_HOME=$HADOOP_HOME
export HADOOP_HDFS_HOME=$HADOOP_HOME
export YARN_HOME=$HADOOP_HOME
export HADOOP_COMMON_LIB_NATIVE_DIR=$HADOOP_HOME/lib/native
export PATH=$PATH:$HADOOP_HOME/sbin:$HADOOP_HOME/bin
export HADOOP_PID_DIR=$HADOOP_HOME/hadoop2_data/hdfs/pid

#spark setup
export SPARK_HOME=/opt/spark/spark
export PATH=$SPARK_HOME/bin:$PATH
#scala
export SCALA_HOME=/usr/local/src/scala/scala-2.11.11
export PATH=$SCALA_HOME/bin:$PATH
------------------------------------------------------------


texts: Simple Unpivot - erroneous Column Conflict

texts:
Did I stumble across a reserved word here or something?

For, the following statement:

SELECT [Type]
FROM (SELECT 'Receive' AS [Rec], 'Transfer' AS [Trx]) [data]
UNPIVOT
(
    [Orders] FOR [Type] IN ([Rec], [Trx])
) AS [unpvt];

produces this exception/error: The type of column "Trx" conflicts with the type of other columns specified in the UNPIVOT list.

Working Sample

SELECT [Type]
FROM
--(SELECT 'Receive' AS [Rec], 'Transfer' AS [Trx]) [data]
(SELECT 'Rec_eive' AS [Rec], 'Transfer' AS [Trx]) [data]
UNPIVOT
(
    [Orders] FOR [Type] IN ([Rec], [Trx])
) AS [unpvt];

Database Explorer: data.stackexchange.com | Simple Unpivot - Column Conflict


texts: select query on Temp table is not returning data in correct order even after specifying index hint

texts:
In my code, I am creating a temp table using select * into #tempTable.
Temp table contains the huge number of records and I need to retrieve data from it many times in my code so I created a clustered index on it.
But somehow I am not getting data in correct order as per created index.

I know I can do it simply by putting the order by clause, but my question is why I am not getting data in the correct order even if I am forcing the optimizer to use the particular index?

Below is my sample code:

----------- Sample code ----------------------------

;with CTE1
as (
    select col1
        ,col2
        ,-- - -- - - from table1

    union

    select col1
        ,col2
        ,-- - -- - - from table2
        -- few more tables in union --
    )
    , CTE2
as (
    select *
        ,RowNumber = RowNumber()
            over (order by case
                    when @sortOrder = 0
                    then case
                            when @sortColumn = 'Date'
                            then [tdate]......some more dynamic order by conditions..........
                         end
                    end asc
            )
    from CTE1
    inner join
    from TABLE3 on -- some conditions
    )
select *
from CTE2
into #TempTable;

Then add the clustered index:

create clustered index IX_TempTable_RowNumber on #TempTable (RowNumber asc);

select *
from #TempTable with (index (IX_TempTable_RowNumber));

----------------- End of sample code ---------------------

Even I checked the table scheme and index details using

exec tempdb.dbo.sp_help N'#TempTable';

select *
from tempdb.sys.indexes
where name = N'IX_TempTable_RowNumber'

And it seems correct to me. What am I missing here?


texts: Query slower after upgrade from sql server 2008R2 to 2014sp2

texts:
I recently upgraded from SQL Server 2008R2 to 2014SP2 and upgraded the databases to the compatiblity level 120.
This query generates a better execution plan with the old cardinality estimator (traceflag 9481 enabled): https://www.brentozar.com/pastetheplan/?id=Hy9skV1Ox
It only uses 269 logical reads on object2 and 3 on object3.
With the new cardinality estimator i get this execution plan: https://www.brentozar.com/pastetheplan/?id=BJAnpXk_e
Unfortunately this results in 269 logical reads on object2 and 68688 on object3.

The server is configured for adhoc workload.
Even enabling the traceflag 4199 doesn't improve the estimates/execution plan
Why does the new cardinality estimator generate such a worse plan?
Why are the estimates so far off?
What could i do to improve it besides disabling the new estimator or using query hints?


texts: Database design for large amount of sensors at very fast intervals

texts:
I've been tasked with designing a backup solution to store sensor data from about 1500 sensors at 10 second intervals. In the event that the primary transmission method of the sensors goes down, my solution should not have a delay of more than 10 seconds since the actual update from the sensor. Thus making my solution the next source of data for them to consume.

The data that is generated is also stored in a file and I am supposed to read them as reference, rather than taking it directly from the sensors themselves.

I've been playing around with the numbers and I can't seem to reach that 10 second threshold. Considering that each file amounts to about 10 MB at the end of each month, I've got close 15 GB of data to process and import.

Currently I have been testing with 3 tables of 500 sensors each to store about 1 month worth of data and I keep hitting the 2 hours mark for processing using MySQL LOAD DATA INFILE method with temporary tables.

From here I can only assume that my methods and approach to this is wrong and was wondering if anyone could provide any kind of advice? Is it even possible to approach that kind of timing in the first place?

I have read the following questions for reference to get a better idea on how to approach this.
Database redesign opportunity: What table design to use for this sensor data collection?

Edit 1

Currently using MySQL 5.7.28 for testing. No plans to move towards 8.0.
Current structure is as follows:

CREATE TABLE `points0500` (
  `DateTime` datetime NOT NULL,
  `Point0000` double DEFAULT NULL,
  `Point0001` double DEFAULT NULL,
  `Point0002` double DEFAULT NULL,
...
  `Point0497` double DEFAULT NULL,
  `Point0498` double DEFAULT NULL,
  `Point0499` double DEFAULT NULL,
  PRIMARY KEY (`DateTime`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Database Configuration: my.ini

innodb_log_buffer_size=256M
innodb_buffer_pool_size=2G
innodb_log_file_size=256M

Sorry about the lack of details, not sure what else is important.

Edit 2

Current Test Server is running on a Xeon E3-1220, with 16 GB of RAM and two 1 TB HDD on RAID 1.

If the data was used for consolidated report, I am okay with the time taken.
The issue with the timing is that they are using the data for something time-sensitive which currently I am not privy to. To the best of my knowledge, they will query the database for the latest readings every 10 seconds to get the sensor readings.


texts: Share the database between vmware guest and host

texts:
I wanna Access the MySQL Database founded in Windows 10 from VMware Workstation MAC OS X guest and give the guest ( MAC in VMware ) only permissions to just one database from the host (Windows 10),

I mean I want the Mac guest can access to one database from the host (NOT For ALL Databases).

I am connected in the guest by bridge network adapter.
I know I must do a change in /Applications/XAMPP/xamppfiles/etc/my.cnf but don't know what. ( I am using XAMPP 7.1.6, it's doesn't have bind_address ).

I want the database always up in my machine ( Windows 10 ) and when I run the VMware mac guest ( MAC ) so the guest can access to this database.

( I mean I want the database to be accessed in the host ( although when VMware is off ) .

I have already made a database in PhpMyAdmin in the host ( Windows 10 ), How can I access to just this database from the MAC guest in the VMware.

I wanna mention I don't want to give permissions to this database to another computers in my Network, or another computers that have my IP Address.

I appreciate any help. Thanks.


texts: I have a need to have a database table that has a column that is either null, or unique values. I know I can control that in code, but is there a way to do that with an index as a backup? Or is that just really bad DB design? :) For example, I don't want to require an email address, but if they do enter one, I don't want to allow duplicates. Easy enough to check with my code, but I would like to configure Mysql to enforce it. Maybe a trigger returning false if the rules are violated?


texts: mysqldump slow on Windows unless piped to file

texts:
Running mysqldump without piping to file such as:

mysqldump -u root -pPASS db

is really slow on windows. If I do

mysqldump -u root -pPASS db > db.sql

it runs fast.

This might not seem like a big deal as I can always pipe to a file. BUT I am using mysqldump from a script and I just want the raw output so I don't have to load the file in memory or save a temp file.

Linux and Mac there is NOT a difference if I pipe it to a file or just let it output.

Is there a way to fix this? This is for mysql 5.6.x

The full command I run is:

mysqldump --user=$db_username --password=$db_password --tz-utc=false --single-transaction --routines --triggers db

but I have tried a lot of combos and nothing makes it fast.


texts: How can I transpose multiple unrelated columns into rows in SQL Server?

texts:
I have a table that looks like the following:
PersonId   EyeColor    HairColor    FavoriteTVShow      FavoriteMovie    FavoriteFood    BirthInfo
---------- ----------- ------------ ------------------- ---------------- --------------- ------------------
A123       Brown       Black        The Price Is Right  NULL             Pizza           New York City
A444       NULL        Brown        Friends             Elf              Steak           Seattle

My desired result set is this:
PersonId ColorInfo FavoriteInfo       BirthInfo
-------- --------- ------------------ -------------
A123     Brown     The Price Is Right New York City
A123     Black     Pizza              NULL
A444     Brown     Friends            Seattle
A444     NULL      Elf                NULL
A444     NULL      Steak              NULL

So for each PersonId, each group of columns (2 ColorInfo cols, 3 FavoriteInfo cols, and 1 BirthInfo col) is transposed into rows. I've tried variations of CROSS APPLY and UNPIVOT, but I can't seem to get it just right. I know it's a weird request, but can anyone think of a good way to do this?
Thank you in advance!
Here's the T-SQL:
--Create the table
CREATE TABLE #test (
PersonId VARCHAR(10),
EyeColor VARCHAR(20),
HairColor VARCHAR(20),
FavoriteTVShow VARCHAR(30),
FavoriteMovie VARCHAR(30),
FavoriteFood VARCHAR(30),
BirthInfo VARCHAR(30)
)

--Populate the table
INSERT INTO #test
VALUES
('A123', 'Brown', 'Black', 'The Price Is Right', NULL, 'Pizza', 'New York City'),
('A444', NULL, 'Brown', 'Friends', 'Elf', 'Steak', 'Seattle')

--See the original result set.
SELECT *
FROM #test

--My desired result set
SELECT *
FROM ( VALUES ('A123', 'Brown', 'The Price Is Right', 'New York City'),
              ('A123', 'Black', 'Pizza', NULL),
              ('A444', 'Brown', 'Friends', 'Seattle'),
              ('A444', NULL, 'Elf', NULL),
              ('A444', NULL, 'Steak', NULL) ) t (PersonId, ColorInfo, FavoriteInfo, BirthInfo)


texts: Cardinality Estimate for LIKE operator (Local Variables)

texts:
I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and the query optimiser doesn't have to resort to selectivity guesses).

When executing the below query against the credit database I get different estimates under the different CEs.  Under the new CE I receive an estimate of 900 rows which I was expecting, under the legacy CE I receive an estimate of 241.416 and I can't figure out how this estimate is derived.  Is anyone able to shed any light?

-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;

-- Forcing Legacy CE (Estimate = 241.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);

In my scenario, I already have the credit database set to compatibility level 120, hence why in the second query I'm using trace flags to force the legacy CE and to also provide information on what statistics are used/considered by the query optimiser.  I can see the column statistics on 'lastname' are being used but I still can't work out how the estimate of 241.416 is derived.

I couldn't find anything online other than this Itzik Ben-Gan article, which states "When using the LIKE predicate in all optimize for unknown scenarios both the legacy and new CEs use a 9 percent estimate.". The information in that post would appear to be incorrect.


texts: Sql Server Management Studio can't remember passwords

texts:
When I connect to a database using ssms, I also have a checkbox labelled "Remember password". However, ssms will not remember the password. The next time I connect to the same server, I have to write it again.

What is the actual functionality of the "remember password" checkbox?


texts: No missing index requests on SQL Server 2012

texts:
I am trying to optimize the database by creating missing indexes, but the dm_db_missing_index_details table does not have any data.

I have the same database on different server and there are many records in that table. So it looks there may be a configuration problem causing the missing index requests to not be saved.

Why are there missing index requests on one server, but there aren't any on the other server (for the same database)?

We have not restarted the database server for the last four days. I am sure there should be index issues, because the same db is running at a different location and that one has many missing index warnings (and we fixed those).


texts:
We recently migrated from LogShipping standby/read-only setup to Multi Subnet AG setup with readable secondaries.
Generally on old setup we have select queries running for longer duration as the database in question is over 20 TB and has mix of read write workload on primary.
After moving to new setup of AG we have started seeing blocking which i am not able to understand. Why select queries on secondary are blocking other select queries in my readable secondary replica instance, even when the database being queried has RCSI enabled?
Below is what i have captured

Lead blocker is some long running SELECT query does not show any
specific waittype as particular, lets say SPID 129

SPID 129 blocks a session ID 45 ( i am sure this is not a user
id) for almost 6 hours which is dependent on spid129 and wait type is
LCK_M_SCH_M

Here comes the problem when this SPID 45 just blocks all other select
queries now in that 6 hour duration.

I am not able to understand what is happening. Can someone help me troubleshoot or look in correct direction?


texts: Data inherently ordered as if it were a clustered index

texts:
I have the following table with 7.5 million records:

CREATE TABLE [dbo].[TestTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TestCol] [nvarchar](50) NOT NULL,
    [TestCol2] [nvarchar](50) NOT NULL,
    [TestCol3] [nvarchar](50) NOT NULL,
    [Anonymised] [tinyint] NOT NULL,
    [Date] [datetime] NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I notice that when there is a non-clustered index on the date field:

CREATE NONCLUSTERED INDEX IX_TestTable_Date ON [dbo].[TestTable] ([Date])

-and I run the following query:

UPDATE TestTable
SET TestCol='*GDPR*', TestCol2='*GDPR*', TestCol3='*GDPR*', Anonymised=1
WHERE [Date] <= '25 August 2016'

-the data coming returned by the index access operation is sorted to match the key order of the PK/CX, reducing performance.

Query plan

I was surprised to find that removing the index from the date field actually improves the performance of the query by about 30% because it no longer performs the sort:

Query plan

My theory, and this may be obvious to the more experienced among you, is that it has figured out that the date column is implicitly ordered exactly the same as the primary key / clustered index.

So my question is: Is it possible to take advantage of this fact in order to improve the performance of my query?


texts: Performance: storing list of thousands of items in postgresql's JSON field vs having separate table for items?

texts:
Let's say I have some table A which have json-field items:

A:
 foo: 'foo'
 items: [
  {a: 1, b:2},
  {a: 3, b:4},
  ...
 ]

There could be couple of thousands of items in one A object.

Is there any sense to introduce another table Item instead of having json field?

Item:
 a: 1
 b: 2

Both methods allow me to query stuff that I need, but it's much easier to work with separate table and it also provide out of the box validation for data.
My only concern is that it might be much slower than dealing with all items that already stored in one place.

Should I worry about performance?


texts:
I have a table deflator that is defined as:

               Table "deflator"
    Column   |       Type        | Modifiers
-------------+-------------------+-----------
country_code | smallint          | not null
country_name | character varying | not null
year         | smallint          | not null
deflator     | numeric           |
source       | character varying |

Sample output from this table looks like:

country_code | country_name  | year | deflator | source
-------------+---------------+------+----------+----------
           1 | country_1     | 2016 |       12 | source_1
           1 | country_1     | 2015 |       11 | source_2
           1 | country_1     | 2014 |       10 | source_2
           2 | country_2     | 2016 |       15 | source_1
           2 | country_2     | 2015 |       14 | source_1
           2 | country_2     | 2014 |       13 | source_2
           3 | country_3     | 2016 |       18 | source_1
           3 | country_3     | 2015 |       17 | source_2
           3 | country_3     | 2014 |       16 | source_3
(9 rows)

I use the following query to pivot the table if I exclude the column source:

SELECT
    *
FROM CROSSTAB (
    'SELECT
        country_code
        , country_name
        , year
        , deflator
     FROM dimension.master_oecd_deflator
     ORDER BY 1;'
     , $$ VALUES ('2014'::TEXT), ('2015'::TEXT), ('2016'::TEXT) $$
) AS "ct" (
    "country_code" SMALLINT
    , "country_name" TEXT
    , "2014" NUMERIC
    , "2015" NUMERIC
    , "2016" NUMERIC
);

The above query gives me:

country_code |   country_name    | 2016 | 2015 | 2014 |
-------------+-------------------+------+--- --+------+
           1 | country_1         | 12   | 11   | 10   |
           2 | country_2         | 15   | 14   | 13   |
           3 | country_3         | 18   | 17   | 16   |

But because the source of the deflator varies from year to year for each country I want to include the source column in the pivot for my desired output to look like:

country_code |   country_name    | 2016 | 2016_source | 2015 | 2015_source | 2014 | 2014_source
-------------+-------------------+------+-------------+------+-------------+------+------------
           1 | country_1         | 12   | source_1    | 11   | source_2    | 10   | source_2
           2 | country_2         | 15   | source_1    | 14   | source_1    | 13   | source_2
           3 | country_3         | 18   | source_1    | 17   | source_2    | 16   | source_3

How do I modify this query to give me the desired output? (With the source for each year listed next to the year). Is this even possible?


texts:
I have setup barman that allows me to backup my database.
I have several backup, and I would like to recover an old backup. because I need to check the state of the database at a given time, two month ago.
Here are my backups:
$ barman list-backup test-server
test-server 20210108T050002 - Fri Jan  8 04:00:11 2021 - Size: 500.9 MiB - WAL Size: 14.2 MiB
test-server 20210107T050002 - Thu Jan  7 04:00:06 2021 - Size: 499.0 MiB - WAL Size: 17.1 MiB
test-server 20210106T050001 - Wed Jan  6 04:00:09 2021 - Size: 497.1 MiB - WAL Size: 10.0 MiB
test-server 20210105T050001 - Tue Jan  5 04:00:10 2021 - Size: 493.7 MiB - WAL Size: 18.0 MiB
test-server 20210104T050001 - Mon Jan  4 04:00:08 2021 - Size: 490.6 MiB - WAL Size: 19.0 MiB
test-server 20210103T050002 - Sun Jan  3 04:00:07 2021 - Size: 490.5 MiB - WAL Size: 274.1 KiB
test-server 20201227T050002 - Sun Dec 27 04:00:11 2020 - Size: 468.0 MiB - WAL Size: 139.1 MiB
test-server 20201129T050002 - Sun Nov 29 04:00:07 2020 - Size: 299.0 MiB - WAL Size: 757.8 MiB
test-server 20201025T050002 - Sun Oct 25 04:00:04 2020 - Size: 84.0 MiB - WAL Size: 850.8 MiB

I would like to restore the backup from Nov 29.
I ran barman recover test-server 20201129T050002 test-server-restore.
The probem is that the backup I get contains values inserted today. How is it possible ? It's like if I have the backup from Nov 29 +  the all the WAL streaming between Nov 29 and today.
How can I recover a backup exactly from Nov 29 ?
Thanks


texts: I am wondering if there is a SQL Server "version" of the MySQL max_packet_size parameter. Is there a way to configure this setting in SQL Server?


texts:
On Wikipedia, it says:

The decomposition is a lossless-join decomposition of R if at least one of the following functional dependencies are in
F+ (where
F+ stands for the closure for every attribute or attribute sets in
F):

R1 ∩ R2 → R1 or R1 ∩ R2 → R2

Unfortunately, I do not understand this criteria. It is known that the decomposition is lossless if the join of R1 and R2 is R, but how is this derivable from the criteria above?


texts: ROR application with Mysql 5.6 - transaction-isolation with Master -Slave

texts:
We are running a ROR application with Mysql 5.6 and master/slave replication. We are facing deadlock issues.

We've successfully tested changing settings from REPEATABLE-READ to READ-COMMITTED on our test servers (which are standalone; not master/slave) which has eliminated the deadlock problem:

transaction-isolation = READ-COMMITTED

When we added the same in the my.cnf file on both master and slave, not only does the replication fail but the application also fails to load.

What would be the recommended setting to accommodate this change. Is there any alternative ?


texts: is it problematic to cluster across two datacenters w/aag?

texts:
In general is it problematic to cluster across two datacenters that are up to 15-miles apart?

For this one particular project the approach is the following?

2-Node Failover Cluster across two datacenters, one node per datacenter
local SAN disks, no shared disks
'Node and FileShare Majority' at third location on-campus
employ AAG, failover will be manual; asynchronous mode

Is anyone implementing something like this with success??!?

Thanks so much for the help!!


texts:
I have a users table. I want to keep track of all the changes I have done, name, phone etc. I've thought to create an extra table changes that will store the id of the previous and the next version of the user. I keep all the users record. Is this a good approach?

Update

I've also thought to use a separate table for the history see below

Or, to use a single table and track its version from the timestamp or the id (since it's increment), but I consider that as a poor solution.


texts: Best practices for providing PHI/PII data to users in organization

texts:
I am looking for best practices for how to share data from a SQL Server database that contains PHI/PII to individuals that cannot view PHI/PII. In short, we maintain a SQL server database that contains 30+ columns of PHI/PII. We need to provide datasets for certain individuals that cannot access the PHI/PII columns, but can access the other fields to conduct different types of analyses.
Current structure: The database is 100GBs and is updated 4 times per day. All data resides on Azure SQL Server. The tables should maintain metadata so blob storage is not ideal (unless someone has an idea to maintain metadata in blob storage). The data will be accessed through PowerBI or Azure Databricks.
Several options come to mind:

Create a DB Role and deny access to PHI/PII columns
Create a new SQL server database and an ETL that copies non-PHI/non-PII data from one database to the new database
Create a new schema and then create views of the tables which do not contain PII/PHI columns and then restrict access to users for this schema only
Build an script/ leverage Azure Data Factory to copy data from the database to Azure table.

Security is number one priority. Any advice is much appreciated.


texts: I've been looking at the wikipedia page for NoSQL and it lists several variations on the Key/Value store database, but I can't find any details on what it means by Key/Value store in this context.  Could someone explain or link an explanation to me?  Also, when would I use such a database?