texts: Group and count array elements using intarray

texts:
I am working on a Postgres 9.4 project with the intarray extension enabled. We have a table that looks like this:

items
-------------------------------------
id    name                  tag_ids
--------------------------------------
1     a car                 {1,4}
2     a room to rent        {1}
3     a boat                {1,2,4,11}
4     a wine                {2}
5     emily                 {3}

I'd like to group the tag ids if possible. Like get a count of all the elements that have a tag_id of '{1,2,4,11}'

tag_id  count
1       3
2       2
4       2
11      1

Is this possible? I would think an intersection like this:

select * from items where tag_ids && '{1,2,4,11}'

But I need to group by the array elements inside the intersection result. If I group by tag_ids, it is just the unique value.

How would I do it?


texts: Where should I put my system databases in WindowsCore container

texts:
We're looking into migrating our SQL Server environment to use containers.

I've successfully built a WindowsCore image with SQL Server installed. The instance is accessible with the data files using volumes so that they persist.

My question is where should I put my system databases? Im thinking externally so that things like logins etc persist, and Ill just have to put a startup procedure to if serverproperty('Machinename') <> @@servername usp_dotheservernamething;, but is that wise?

If I put it in the image, I still would need to rename the server, but then I suppose if I start a new container in lets say a dev environment, I wouldn't have to worry about a drive.

Thinking out loud, I could possibly add onto the startup procedure to have to change the system database mappings if the servername is a production db name? That way if we spin up a dev/qa/local container it will have system databases, but if its production it will go to the external volume for its databases...


texts: Deciding the best way to partition (Postgresql)

texts:
I have been trying to research this for quite a while now and I am having a hard time trying to figure it out. I am out of ideas other then setting up an extra test database and running through all of the possibilities to see how they perform. Hopefully someone can pass on a bit of knowledge to prevent having to do that.

I have a database (Postgresql 9.2 with PostGIS) with a large amount of spatial data in it. Typically I look at it by location and date, however I do have to be able to search across all of the fields pretty regularly. The amount of data we receive in a day varies and continues to grow over time, so we cant guarantee the size of a partition on disc or the number of rows in one if we partition by date. I have read that setting a limit on the number of rows in a partition helps, because you can basically make a maximum size for each partition and allow for faster reads, but I do not have a sequential id field to partition off of either.

I have found functions that will allow me to partition by date, or possibly partition off of a calculated serial field (not sure on this one yet). My main question for now is: how should I decide which is the best way to do it? Is there an inherent benefit to one way or the other? Is there even a way to tell which will be better without implementing both and testing them?


texts: not a single-group group function when trying to add two sum() function

texts:
I have two tables:

create table a
(
  index_ numeric(1),
  value_ numeric(2)
);
create table b
(
  index_ numeric(1),
  value_ numeric(2)
);

Now I want to get the number of these two tables sum,I tried :

select sum(value_) - (select sum(value_) from b) from a ;

it is not working without group by, how can i do this without group by?


texts: Groups and Members database: Which of these two options is a good design?

texts:
Each group can have either 1 or 2 members, no more, no less. Every select of group data will need to retrieve the member data. The eventual state of each group will tend towards 2 members (full group).

Group
group_id | ...

Member
group_id | user_id

vs

Group
group_id | user_id_1 | user_id_2 | ...


texts:
I have a log shipped database, and I want to create a new NDF file in another disk for moving historic tables to that NDF file. My goal is to free some space in the MDF file because the disk that the MDF resides is getting full.

I have a doubt and is: if I create the NDF file in a disk in the primary server that the secondary server does not have: does the log shipping process will fail?

If yes, how can I avoid this?


texts: What data type should I use: ENUM, TINYINT, or CHAR?

texts:
I'm creating a new table and I would like to do it right. The table will be a list of members with their Professional Type and Decile associated.

The Professional Type is a 3-character string. There are only 8 allowable strings for this field. Should I use ENUM or CHAR(3) or something better?

The Decile can be any number between 1-10, but only these numbers. Should I use ENUM or unsigned TINYINT or something better?

Which are the best options for efficiency?


texts: mongodb shard chunk migration 500GB takes 13 days - Is this slow or normal?

texts:
I have mongodb shard cluster, shard key is hashed. It has 2 shard replica sets. Each replica set has 2 machines.

I did an experiment by adding another 2 shard replica sets, and it starts to rebalance.

However, after a while I find out that chunk migration is rather to be slow. It takes 1 hour to move 1.4GB of data.

It makes me worry, it means I have to wait for 13 days to complete 500GB of chunk migration!

I am new on this stuff and I don't have a god feel whether it is slow, fast, or normal. But still, those number does not convince me.

Additional notes on experiment:
- using m3 medium aws machines
- no other process running, only chunk migration
- default mongodb sharding installation with no further configuration
- shardkey is using hashed at object id (_id)
- max chunk size 64MB


texts: Create a view that dynamically UNIONs new tables

texts:
I have a database named DDOS_WAF_REPORT that has multiple tables with the name format dbo.DDOS_WAF_ACCOUNT_###### (a 5 or 6 digit ID number).

All the tables have the exact same structure (both column names and types). I want to create a view to UNION ALL them together. In addition there will be new tables of the same type that will be added later into the database, so I need the view to be able to 'run through' all existing tables in the database and add them (either adding or creating the view from scratch).

I've created a simple view with the tables name's hard coded but I'm having difficulty how to approach the issue of having the view created dynamically.
I'm using SQL Server 2012.

The tables being added are the result of an outside script running against an API per account so I can't avoid having multiple tables in the database. I assumed I'll probably have to write some script code that would actually run and check for new table names but I wanted to see some example to figure out how to do that.


texts: Looking for a DR-like solution to enable full data warehouse re-load while having secondary copy live

texts:
I am looking for suggestions/ideas to the following problem.  We have a number of data warehouses for each of our clients (on SQL Server 2014).  These data warehouses are maintained fairly fresh with continuous data loads every few minutes.  When DW needs to change, this will, more often than not, require a complete data re-load.  The challenge is that we can't afford to take DW offline for extended period of time and, consequently, make any applications, which use this data unavailable.  It is acceptable if the data is stale, but it has to be available.

Therefore, we are looking for a solution, which will enable us to maintain a secondary copy of the data warehouse.  Let's call these copies A and B.  The secondary copy (B) needs to be kept in-synch with the primary copy (A).  When the time comes to make significant DW changes, we would like for the following to happen:
1) stop synchronization between A and B
2) point our applications to the secondary copy (B)
3) perform our upgrade/maintenance/re-load of the primary copy (A)
4) point our applications back to the primary copy (A)
5) re-establish synchronization between A and B (we understand it will take some time)

So basically B acts as a temporary "fill-in" while A is being upgraded and reloaded.

The solution has to be automatable, meaning, we need to be able to do everything from a command-line/powershell.

We looked at some of the existing HA/DR technologies, for example SQL Server Always On, but it seems that breaking and re-establishing synchronization (as described above) may not be a good fit for it.  It seems that what we need is a 3rd party software, possibly capturing disk activity at the system level.

I appreciate any ideas, recommendations or experiences.

Thank you!


texts: A bit confused on how mongo and mongod load configuration files

texts:
I'm new to MongoDB and was working on getting my first replica set up and running using the MongoDB Cloud Manager.  However, the server I deployed to already had mongodb installed.

I currently have everything seemingly working, including SSL and authentication, but I'm wondering how mongodb decides what config files to use and which mongod to run.

When I used the deploy option in Cloud Manager to my remote server, it had me set up an automation agent, which included its own mongo and mongod executables.  I would have thought I needed to use these to connect to my database, but regardless of what mongo or mongods I'm running (I've tried all 4 combos of the automation agent's and the preinstalled ones in /usr/bin), as long as I type:

mongo --host localhost:port#

and specify the port number for the primary in my replica set, I connect with the proper configuration settings.  I've noticed the replica set has an automation_agent.conf file on the primary, but how does merely specifying the port # tell mongo to use that configuration file?

On a somewhat related note (I was trying some command line diagnostics in the mongo shell to figure out the configuration behavior), is the normal process to log in once you've set up authentication to use the admin database, and then authenticate in the mongo shell? Am I overcomplicating things?

Sorry if these questions seem a bit scattered, I'm just unsure I'm not shooting myself in the foot later by not uninstalling the old mongo and mongod executables.


texts: Script to Output Tables to CSV Showing Heading Only Once

texts:
I am trying to write a script that will output a table to a CSV.  The tricky part has been to get the output to only show the header once.  The script below does not show the header at all.  The other option we have used is is setting the pagesize extremely high.  That has also been very problematic.

set colsep |
set pagesize 0
set trimspool on
set linesize 10000

spool table1.csv
SELECT host_name from v$instance;
select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "CURRENT TIME" from dual;
prompt ================================================
prompt    TABLE 1
prompt    SELECT * FROM tbl1;
prompt ================================================
SELECT * FROM tbl1;
spool off

For clarity:
I do not have direct access to the database.  I will be passing the script to a DBA to run against the database, so PL/SQL or anything else more advanced than a very basic script is probably not an option.  Also because of this, I want the column headers to show ONCE and only once whether there are 10 or 10 million records in the table.  This will give me reasonable descriptions of data that I need and minimize the cleanup before analyzing the data.


texts:
I made a master(A)-slave(B)-slave(C) mode MySQL instances for backup. But today when I see whether the mode works fine, I find A and B are OK. That is B can get binlog from A, but B does not write updates to its binlog. So C cannot sync with B.

After some investigation, I see that B's new binlog file is 120 bytes and there are not any errors in MySQL's log. The sql_log_bin is ON. So I don't know what can be the reason that causes the problem. What I can supply as an addition is that the new binlog's create time is when I made a mysqldump on B. So I guess there may be some relationship between the mysqldump event and the binlog write event? Hopefully someone can help me out, thanks a lot.

B's master status:

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysqlbin.000016 |      120 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+

B's binlog file size status:

ll /data/mysql/
-rw-rw----. 1 mysql mysql        166 7月   7 02:00 mysqlbin.000015
-rw-rw----. 1 mysql mysql        120 7月   7 02:00 mysqlbin.000016
-rw-rw----. 1 mysql mysql        288 7月   7 02:00 mysqlbin.index

B's mysqldump SQL:

mysqldump --master-data=2 --all-databases --flush-logs --user=xxx  --socket=/tmp/mysqld.sock -A > /tmp/backup.sql


texts: Update by iterating table in batches faster than whole table in PostgreSQL

texts:
I have a table with approximately one million rows.

It is being used in production and I ran an UPDATE which covers ~95% of those rows.

After five hours I cancelled the request because it was taking so long.

The table has an auto-incrementing ID column, so I tried extending the WHERE condition of my query to include id BETWEEN 1 AND 500.

This update completed in approximately two seconds. I then manually iterated up through the id in batches of 500, e.g. BETWEEN 500 AND 1000, then BETWEEN 1000 AND 1500.

At this rate it will require 2000 batches of 500 to update the whole table.

Updating 2000 batches at 2 seconds each is just over an hour.

My questions are:

What reasons are there for this difference?
I don't care about transaction isolation, so is there a way to simulate this 'update in batches', without having to script the 2000 updates to run separately.


texts: Fetching wrong results on comparing a variable with 0 in unix

texts:
I have the following code in unix

 #!/bin/bash
 user=$1
 username=$user'%'
 DB_name=$2
 userpwd=$4
 export PGPASSWORD=$3

 psql -h localhost -U postgres -d $DB_name -At -c "SELECT COUNT(*) AS USER_COUNT FROM pg_roles WHERE rolname = '${user}' " \
| while read -a Record ;
do
USER_COUNT=${Record[0]}
echo 'user USER_COUNT ' $USER_COUNT
done

echo 'count' $count
if   [[ $(($USER_COUNT)) -eq 0 ]]
then
echo 'New User ' $user
else
echo 'User Exists ' $user

fi

It basically accepts a given username and checks if it exists in Postgresql.
After we pass the arguments, it checks its count, which will be 1, if the username exists, otherwise 0, and stores it in the variable USER_COUNT.
It then compares it with 0.
But the comparison is fetching me wrong results.
Regardless of whether a username exists or not, it only runs the 'True' part of if.

Edit-1 The reason I used if [[ $(($USER_COUNT)) -eq 0 ]] instead of
if [$USER_COUNT -eq 0 ], as suggested by Phill, is because I was getting an error message otherwise, saying unary operator expected. I read somewhere that it is because $USER_COUNT is an unset variable.

Edit-2 Thanks for your suggestion Lennart. As suggested by him, I added -x to my first line and found that even though the value of $USER_COUNT is 1 when the user exists, and 0 otherwise, but while comparison, i.e in the step
if [[ $(($USER_COUNT)) -eq 0 ]], $USER_COUNT takes the value 0, even if earlier it was 1. Hence, it always evaluates to true.
But I am not sure what to do to resolve the above situation.

Edit-3 Thanks a lot Lennart. Your code solved my problem.


texts: Old archivelog files present on disk, but not in RMAN

texts:
I recently spotted 3 very old archivelog directories on disk that do not appear within RMAN.

[oracle@ORACLE01 archivelog]$ du -sh *
122M    2017_02_25
4.9G    2017_02_26
2.5G    2017_02_27
1.6G    2017_06_28
474M    2017_06_29

Within RMAN, archivelogs appear from the 28th June (as I would expect)

RMAN> list archivelog all;  (list truncated for brevity)
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name R2CI
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
30329   1    81000   A 28/06/2017 10:45:26
        Name: /u01/.../archivelog/2017_06_28/o1_mf_1_81000_do72c0ng_.arc

30330   1    81001   A 28/06/2017 10:48:00
        Name: /u01/.../archivelog/2017_06_28/o1_mf_1_81001_do73f110_.arc

If I run a crosscheck archivelog all, this picks up only the recent archivelog files that I expect to be there:

RMAN> crosscheck archivelog all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
validation succeeded for archived log
archived log file name=/u01/.../archivelog/2017_06_28/o1_mf_1_81000_do72c0ng_.arc RECID=30329 STAMP=947846881
validation succeeded for archived log
archived log file name=/u01/.../archivelog/2017_06_28/o1_mf_1_81001_do73f110_.arc RECID=30330 STAMP=947847970
validation succeeded for archived log
archived log file name=/u01/.../archivelog/2017_06_28/o1_mf_1_81002_do74pzcw_.arc RECID=30331 STAMP=947849313
validation succeeded for archived log
(list truncated for brevity)

A "list archivelog all" after this point does not show these old files.

Questions:

Where are these files likely to have have come/why weren't they
eventually expired and deleted like the rest of the archivelogs?
Why does crosscheck archivelog all not detect them and add them
back? is it because there is a 4 month gap between them and the next
most recent files?
Is it safe to just delete these files off disk?

Thanks


texts: Ensuring a GROUP has specific values, no more no less with an aggregate

texts:
I have a table (a :: text, b :: uuid).

I also have a set (not a list, i.e. order doesn’t matter) of UUIDs, bs = {b1, b2, b3}.

How can I get such an a that has exactly those three bs in the table, no more and no less?

Something like:

SELECT tt.a
  FROM (SELECT a, sort(array_agg(b)) AS bs FROM t GROUP BY a) AS tt
  WHERE tt.bs = sort('{b1, b2, b3}')

But that seems like quite a bad idea w.r.t. efficiency…


texts: mongodb :: failures in bring up mongo server

texts:
I am unable to add a replica to the cluster.

Steps tried:

done hot backup for existing member.
Copied it to new replica.
Tried to start the server, it's failing with these errors:

2019-12-31T13:03:38.277+0000 E STORAGE  [initandlisten] WiredTiger error (0) [1577797418:277479][8130:0x7ff6a95f9b80], connection: __wt_cache_destroy, 358: cache server: exiting with 169 bytes in memory Raw: [1577797418:277479][8130:0x7ff6a95f9b80], connection: __wt_cache_destroy, 358: cache server: exiting with 169 bytes in memory
2019-12-31T13:03:38.279+0000 W STORAGE  [initandlisten] Failed to start up WiredTiger under any compatibility version.
2019-12-31T13:03:38.279+0000 F -        [initandlisten] Fatal Assertion 28559 at src/mongo/db/storage/wiredtiger/wiredtiger_util.cpp 65
2019-12-31T13:03:38.279+0000 F -        [initandlisten] aborting after fassert() failure

We are using percona mongodb - 4.0.13-7

What could be the reason?


texts:
I am trying to create a database in MySQL Server 5.5. The database is created, I know this as when I do

SHOW DATABASES;

It shows me my database in the list of all databases from the commandline.

But when i go inside

  C:\Program Files\MySQL\MySQL Server 5.5\data\

my database is not visible there. In fact with

SHOW DATABASES;

it shows me 6 databases in the list but inside

  C:\Program Files\MySQL\MySQL Server 5.5\data\

shows only two databases.

Its really strange error I m experiencing, the database is created but not actually created
feels like the commandline is cheating with me!

thanks for the help in advance!


texts: Postgres replication not cleaning up WAL files

texts:
I'm not that great with postgres but i was able to setup replication following this tutorial:
https://www.howtoforge.com/tutorial/how-to-set-up-master-slave-replication-for-postgresql-96-on-ubuntu-1604/
It all works great but the amount of WAL files is getting ridiculous.
I assume the slave should tell them master to remove old files, but I'm not sure how to do that.
I have the same settings as in the tutorial. I've already tried to "manually" clean them, which lead to a ton of problems. But right now the i'm using 30GB of space for the files and i'm afraid to do any more massive imports into my db because i will run out of space


texts: Retrieving data of vehicle drivers that have not been assigned to a job

texts:
Struggling with something for the past 24 hours. Needed some genius to shine some light please. I am trying to display a weekly job rota/log for drivers, which will display:

Jobs that have not been assigned to drivers
Jobs that have been assigned to drivers
If a job has been assigned to a driver then display drivers that are available for the day
If there is no jobs for the day then display a list of all drivers available

The last 2 points are what I am really struggling with.

I have the following tables:

drivers ('driver_id', 'driver_name')

vehicles ('vehicle_id', 'vehicle_make', 'vehicle_model')

jobs ('job_id', 'collection_address', 'collection_datetime', 'delivery_address', 'deliver_datetime', 'driver_id', 'vehicle_id')

I have attached the output screen below:

For example if you look at 23/09/2016 the driver Keith is on a job but Nick is available. However, if I do a sub-query it displays both drivers, which is not the correct result. My SQL statement is below and if anyone can help with this, I would really appreciate it.

    SELECT listofdays.job_date, j.job_id, IF( j.driver_id > 0 AND j.job_id > 0, (SELECT driver_name FROM t_drivers WHERE driver_id = j.driver_id LIMIT 1), '') as job_driver, IF( j.vehicle_id > 0, (SELECT vehicle_reg FROM t_vehicles WHERE vehicle_id = j.vehicle_id LIMIT 1), 'no') as job_vehicle, j.collection_town, j.collection_postcode, j.delivery_town, j.delivery_postcode, j.job_status
 FROM (
  SELECT '2016-09-19' + INTERVAL seq.seq DAY AS job_date
  FROM seq_0_to_999999 AS seq
  WHERE seq.seq <= TIMESTAMPDIFF(DAY , '2016-09-19', '2016-09-25' )
 ) AS listofdays
LEFT JOIN t_jobs j ON listofdays.job_date = DATE(j.collection_datetime)
ORDER BY DATE(listofdays.job_date), j.job_order ASC

I am also happy to take onboard any improvements to the above query in terms of speed and performance. It is going to be used for an internal system.

REVISED QUERY BELOW:

    SELECT
  sub.job_date,
  j.job_id,
  IFNULL(d.driver_name, '') as job_driver,
  IFNULL(v.vehicle_reg, 'no') as job_vehicle,
  j.collection_town,
  j.collection_postcode,
  j.delivery_town,
  j.delivery_postcode,
  j.job_status,
  IF(j.job_id IS NULL, sub.available_drivers, 'no availability') AS available_drivers
FROM
  (
    SELECT
      listofdays.job_date,
      GROUP_CONCAT(d.driver_name) AS available_drivers
    FROM
      (
        SELECT
          '2016-09-19' + INTERVAL seq.seq DAY AS job_date
        FROM
          seq_0_to_999999 AS seq
        WHERE
          seq.seq <= TIMESTAMPDIFF(DAY, '2016-09-19', '2016-09-25')
      ) AS listofdays
      CROSS JOIN t_drivers AS d
      LEFT JOIN t_jobs AS j ON listofdays.job_date = DATE(j.collection_datetime)
      AND d.driver_id = j.driver_id
GROUP BY listofdays.job_date
  ) AS sub
  LEFT JOIN t_jobs AS j ON sub.job_date = DATE(j.collection_datetime)
  LEFT JOIN t_drivers AS d ON j.driver_id = d.driver_id
  LEFT JOIN t_vehicles AS v ON j.vehicle_id = v.vehicle_id
ORDER BY
  job_date ASC,
  job_order ASC

The output is the following:

If you look at the above picture on the 23rd there is 2 open jobs, which should have a list of the available drivers but it is saying no availability. In our case Nick is not working on that day but it says no availability.


texts: Deriving a computed column based on several nullable columns

texts:
I'm a bit of an SQL novice.

I've been trying to create an auto generated column called TAG from 5 other columns, some of which can retain NULL marks.

The syntax used to create the auto generated column is:

[Tag]  AS ([Tag Type]+[Parent Tag Type]+[Tag Area No]+[Tag seq No]+[Tag Suffix])

This autogenerates OK if none of the five columns retains a NULL, but if I try to create a row with any one of the columns containing a NULL, the auto generate does not work, and leaves the TAG column blank, even though it creates the row OK with the all the individual column data.

Is there a way I can get the auto generate to work with NULLs?


texts:
Say in Mongo we have one primary and two replica node(simple replica no sharding). Read and write request will be handled only primary node
and replica will just backup the data in case of failover.

My understanding/question about replication(not sharding) in Cassandra

Can we have only one node(say Node_1) serving read and write requests while other two simply working as replica nodes(these nodes just takes the replica request from Node_1 , no real time request from user ) like in Mongo considering replication factor as 3 ?

Per my understanding this should not be possible in cassandra, as in cassandra , replica nodes are nodes are equal peers , so it is mandatory that any of three nodes has to serve write and read request.

Is my understanding correct ?


texts: Linked servers: Transaction context in use by another session

texts:
I'm using linked servers to perform some queries in a distributed transaction. It doesn't work in a distributed transaction. I get the following error:

  Transaction context in use by another session.

Not even this simplified example will work:

IF EXISTS(SELECT server_id from sys.servers WHERE name = 'SomeServer')
BEGIN
       EXEC sp_dropserver  @server = N'SomeServer';
END

EXEC sp_addlinkedserver
   @server = N'SomeServer',
   @srvproduct = N'',
   @provider = N'SQLNCLI',
   @datasrc = N'localhost';

EXEC sp_serveroption
       @server = N'SomeServer',
       @optname = 'rpc out',
       @optvalue = 'on';
GO

BEGIN DISTRIBUTED TRAN;

EXEC('SELECT TOP 100 * FROM [SomeServer].[SomeDatabase].dbo.tblFoo')

ROLLBACK;

EXEC sp_dropserver  @server = N'SomeServer';

Removing the work distributed, or removing the begin/rollback tran entirely also works. Running sp_whoisactive returns no rows. I have had to restart the server (on my laptop) several times because there were distributed transactions I was unable to kill even if I killed all the spids returned by sp_whoisactive.

In those possible related instances, killing thos spids more then once gives me:

  SPID X: transaction rollback in progress. Estimated rollback completion: 0%.


texts: How to write the `apply` function in postgresql

texts:
I'm trying to figure out how to use arrays as inputs to variadic functions and to have a function resembling apply in other languages.
So for instance, what's the best way of writing apply in the following statement?
DROP FUNCTION IF EXISTS test_greatest;

CREATE FUNCTION test_greatest(arr INT[]) RETURNS INT AS $$
BEGIN
    RETURN apply(greatest, arr)
END;
$$ LANGUAGE plpgsql;

SELECT test_greatest(ARRAY[1,6,3])
-- OUTPUTS 6

I found that unnest can works by converting an array into a relation:
DROP FUNCTION IF EXISTS test_max;

CREATE FUNCTION test_max(arr INT[]) RETURNS INT AS $$
BEGIN
    RETURN max(z) FROM unnest(arr) as z;
END;
$$ LANGUAGE plpgsql;

SELECT test_max(ARRAY[1,6,3])
-- OUTPUTS 6

but I'd really like to be able to write sql functions with apply.


texts: SSIS Script Task appears to run, but actually doesn't

texts:
I have a script task  that appears as running fine, but no matter what I put inside nothing ever get executed.  I tried all sort of stuff: logging via the built in SSIS Logging mechanism, logging through additional variables and SQL Command task and I can never figure what's wrong.  I tried to put breakpoint and it won't break during debugging.

This is VS 2015 with SQL 2016.  This issue happens only when deployed on one of the SQL Server.  I previously has no issue at all at that server.  I deployed the same package on a different SQL box (same configuration) and it ran as expected.

EDIT: To better clarify the problem I started a new SSIS Package, it only 3 script tasks in it:

Test Task:

    public void Main()
    {
        // TODO: Add your code here

        Dts.TaskResult = (int)ScriptResults.Failure;
    }

Once the Test Task is completed, it is linked to a "Success Task" if the test is successful, a "Fail Task" if it fails.

On VS, the test task fail as expected and goes to the fail task.  However once deployed it stop working.


texts:
I'm working on a project dealing with genomic data in a Postgres database. There is one table in particular which is causing trouble. It is slowly approaching a billion rows and has three columns: snp_name varchar(32), genotype_id int, local_genotype char(2)

The first two are foreign keys and together form the primary key of the table.

Querying this data is still OK, response-time-wise. Inserting data takes ages, though. We are now at a point where we get new data faster than we can insert it, and we already COPY into a temporary table first and insert from there. Due to the server still running on spinning disks, inserting a genotype with a million rows takes about 5 hours.

Partitioning by genotype_id would be great, since this would result in ~ a thousand tables with a million rows each and a genotype is basically the package we get the data in. Then, querying by snp_name probably won't perform that well anymore, though. Whether we can live without it, has not yet been decided.

Do you have any suggestions for other options we could try? Are there any database systems, that allow indexing across partitions? Any NoSQL solutions that could fit?

EDIT: The table definition:

# \d user_snps
              Table "public.user_snps"
     Column     |         Type          | Modifiers
----------------+-----------------------+-----------
 snp_name       | character varying(32) |
 genotype_id    | integer               |
 local_genotype | character(2)          |
Indexes:
    "user_snps_new_genotype_id" btree (genotype_id)
    "user_snps_new_snp_name" btree (snp_name)

There is only one distinct value of genotype_id per insert, and all values of snp_name are unique per insert.


texts: How to get data from three different tables

texts:
This question is continuing of this issue: How to get data from two different tables?

Now I need to connect the table Sessions with 2 tables:

Input
Downloads

When I use this query with real data:

SELECT sessions.ip
    , COUNT(sessions.id)
    , COUNT(input.input) as TotalInputs
    , COUNT(DISTINCT input.input) as UniqInputs
    , COUNT(downloads.shasum) as files
    , COUNT(DISTINCT download.shasum) as Uniqfiles
FROM sessions, input, downloads
WHERE sessions.id = input.session
    AND sessions.session = downloads.session
    AND date_format(sessions.starttime, '%Y-%m-%d') > "2015-01-01"
GROUP BY sessions.ip
ORDER BY COUNT(sessions.id) DESC LIMIT 5;

I get this output:

ip  | COUNT(sessions.id)    | TotalInputs   | UniqInputs    | files | Uniqfiles
IP1 |              11145    |       11145   | 15            | 11145 |         8
IP2 |               9125    |        9125   | 71            |     0 |         0
IP3 |               7882    |        7882   | 56            |  7882 |        19

But numbers for count(sessions.id), TotalInputs and Files are not accurate. For example if I use this query:

SELECT downloads.shasum
FROM sessions, downloads
WHERE sessions.id = downloads.session
    AND date_format(sessions.starttime, '%Y-%m-%d') > "2015-01-01"
    AND sessions.ip = "IP3";

I find that Files count for IP3 have proper value of 752 (not 7882). Real value for TotalInputs is less than COUNT(sessions.id).

How can I fix my query?

Sample data is available on this SQL Fiddle.

Using the above query and below sample data, I get this output:

ip  | COUNT(sessions.id)    | TotalInputs   | UniqInputs    | files | uniq_files
IP2 | 3                     | 3             | 2             | 3     | 1
IP3 | 8                     | 8             | 4             | 8     | 2

I need this output:

ip  | COUNT(sessions.id)    | TotalInputs   | UniqInputs    | files | uniq_files
IP2 | 1                     | 3             | 2             | 1     | 1
IP3 | 3                     | 5             | 4             | 3     | 2

How can I update my query ?

Sample Sessions data:

id      | starttime             | endtime               | sensor    | ip    | termsize  | client
id1     | 2015-05-07 11:01:20   | 2015-05-07 18:01:32   | 10        | IP3   | 80x50     | 3
id2     | 2015-05-07 18:03:20   | 2015-03-07 18:11:32   | 2         | IP2   | 80x50     | 1
id3     | 2015-05-07 23:05:20   | 2015-06-07 18:10:32   | 10        | IP3   | 80x70     | 3
id4     | 2015-05-07 13:05:20   | 2015-05-09 20:05:32   | 7         | IP3   | 60x30     | 5

Sample Input data:

id  | session   | timestamp             | realm | success   | input
1   | id1       | 2015-07-13 10:29:18   | NULL  | 1         | date
2   | id3       | 2015-08-13 10:11:18   | NULL  | 0         | aaa
3   | id1       | 2015-03-13 10:11:18   | NULL  | 0         | aaa
4   | id1       | 2015-07-14 10:33:15   | NULL  | 1         | uname
5   | id3       | 2015-05-19 20:33:11   | NULL  | 1         | netstat
6   | id2       | 2015-09-22 10:53:21   | NULL  | 1         | pwd
7   | id2       | 2015-09-22 10:58:11   | NULL  | 1         | pwd
8   | id2       | 2015-11-03 09:53:07   | NULL  | 0         | bbb

Sample Downloads data:

id  | session   | timestamp             | url           | outfile   | shasum
1   | id1       | 2014-07-13 12:15:47   | http://xxx    | xxx       | SHA1
2   | id2       | 2014-09-13 12:18:50   | http://xxx2   | xxx2      | SHA2
3   | id1       | 2015-09-11 13:20:50   | http://xxx3   | xxx3      | SHA1
4   | id3       | 2016-01-19 18:21:30   | http://xxx4   | xxx4      | SHA3


texts:
Sometimes, when developing in a hurry, devs make poor choices on column names.

Changing them can be a challenge, having to stage code, and time migration perfectly.

I'm told that Oracle has a way to give a secondary name to a column. Does SQL Server have anything like this? The best I can think of is to add a calculated column to duplicate the column, but this will lead to follow on indexing and performance challenges.

Any good patterns or a magic bullet for SQL Server 2008 or 2012?

(Someone mentioned a view, which would definitely work, but wondering if there's a built-in option.)

I guess I'm looking for a Synonym for Columns.
http://msdn.microsoft.com/en-us/library/ms177544.aspx


texts:
I am trying to implement a booking system for charging stations that can either extend or refund a booking. I basically have the following tables:

booking
booking_logs
payment
refunds

With how the 3rd party payment system we are using is set up, the refunds are attached to payments. Meaning a payment can have multiple refunds, but refunds only belong to one payment.

Now I want to log those actions as I want to be able to display the history of what went on in a single booking. Because a user can extend as many times as they want and we can also refund them if for some reason the charging didn't go well.

Here is my ERD so far.

I just want to know if what I am doing with the booking_logs is okay. I have read about Polymorphic Associations but I don't think it would apply to me as payment and refund have a 1:M relationship and even if they didn't, it seems like it is not always advised to use Polymorphic Associations.

Please correct me if Im wrong.


texts:
I am new to SQL. I have a join which uses three lookup tables. It is a bit complicated so I will publish the code here.

Create View TEST As select
        BDP_INSTITUTION_NAME,
        BIC,
        BDP_COUNTRY_NAME,
        BDP_ISO_COUNTRY_CODE,
        BDP_CITY,
        BDP_NETWORK_CONNECTIVITY,
        BDP_SERVICE_CODES,
        BDP_ISTARGET,
        BCDB_NAME,
        BCDB_LAENDERKENNZEICHEN,
        BCDB_AKTIVMERKMALBANK,
        BCDB_AKTIVMERKMALLAND,
        BCDB_AKTIVMERKMALBANKLAND,
        BCDB_SWIFTKENNZEICHEN,
        COUNTRYCODE,
        ISBDP,
        ISBCDB,
        BCDB_ORT,
        RMA.CRSPDT AS RMA_CRSPDT,
        RMA.ISSR AS RMA_ISSR,
        RMA.TP AS RMA_TP,
        RMA.SVCNM AS RMA_SVCNM,
        RMA.RMASTS AS RMA_RMASTS
       from
(SELECT
        bdp.bic,
        bdp.institution_name AS bdp_institution_name,
        bdp.country_name AS bdp_country_name,
        bdp.iso_country_code AS bdp_iso_country_code,
        bdp.city AS bdp_city,
        bdp.network_connectivity AS bdp_network_connectivity,
        bdp.service_codes as bdp_service_codes,
        bdp.isTarget AS bdp_isTarget,
        bcdb.name as bcdb_name,
        bcdb.laenderKennzeichen as bcdb_laenderKennzeichen,
        bcdb.aktivMerkmalBank AS bcdb_aktivMerkmalBank,
        bcdb.aktivMerkmalLand AS bcdb_aktivMerkmalLand,
        bcdb.aktivMerkmalBankLand AS bcdb_aktivMerkmalBankLand,
        bcdb.swiftKennzeichen AS bcdb_swiftKennzeichen,
        CASE
            WHEN bcdb.laenderKennzeichen IS NOT NULL THEN bcdb.laenderKennzeichen
            ELSE bdp.iso_country_code
        END AS countryCode,
        CASE
            WHEN bdp.bic IS NOT NULL THEN 1
            ELSE 0
        END AS isbdp,
        CASE
            WHEN bcdb.bic IS NOT NULL THEN 1
            ELSE 0
        END AS isbcdb,
        bcdb.ort AS bcdb_ort
        FROM BDP bdp LEFT JOIN BCDB bcdb ON bdp.bic = bcdb.bic WHERE bdp.bic IS NOT NULL
    UNION ALL SELECT
        bcdb.bic,
        bdp.institution_name AS bdp_institution_name,
        bdp.country_name AS bdp_country_name,
        bdp.iso_country_code AS bdp_iso_country_code,
        bdp.city AS bdp_city,
        bdp.network_connectivity AS bdp_network_connectivity,
        bdp.service_codes as bdp_service_codes,
        bdp.isTarget AS bdp_isTarget,
        bcdb.name as bcdb_name,
        bcdb.laenderKennzeichen as bcdb_laenderKennzeichen,
        bcdb.aktivMerkmalBank AS bcdb_aktivMerkmalBank,
        bcdb.aktivMerkmalLand AS bcdb_aktivMerkmalLand,
        bcdb.aktivMerkmalBankLand AS bcdb_aktivMerkmalBankLand,
        bcdb.swiftKennzeichen AS bcdb_swiftKennzeichen,
        CASE
            WHEN bcdb.laenderKennzeichen IS NOT NULL THEN bcdb.laenderKennzeichen
            ELSE bdp.iso_country_code
        END AS countryCode,
        CASE
            WHEN bdp.bic IS NOT NULL THEN 1
            ELSE 0
        END AS isbdp,
        CASE
            WHEN bcdb.bic IS NOT NULL THEN 1
            ELSE 0
        END AS isbcdb,
        bcdb.ort AS bcdb_ort
    FROM BDP bdp RIGHT JOIN BCDB bcdb ON bdp.bic = bcdb.bic WHERE bdp.bic IS NULL)

  t1 left join RMA on (RMA.ISSR = substr(t1.BIC, 1,8) or RMA.CRSPDT = substr(t1.BIC, 1,8));

What I want is to have a unique BIC for each row in the 'TEST' view. The rest of the information is not important i.e. once I get a hit on a BIC it should not be repeated again, all the rest is not important what is important is if I get a BIC hit or not.

Through internet search I found out I cannot use DISTINCT in my case it does not help. What else can I do?


texts:
I have a web app using a database per client (around 120 and growing, 20k users in total). Each database has its own user table with user_id, username, password, etc. Meaning: if a user is member of multiple sites, the user has multiple accounts, passwords, etc.
I want to "centralise" the user tables, so users can log in once for all sites they are member of.
The username is the email address; which is used on a lot of places in the app.
Ideally I'd use a few "centralised" tables (user with columns username, password, etc; login_has_site linking user with site), while leaving the "decentralised" user tables untouched (containing username but also site specific preferences).
But, how do you maintain integrity between "central" and "decentral" tables when e.g. email address changes? Looking for strategy/ how to approach. Any thoughts?
Additional information
Basic schema
CREATE TABLE `login` (
  `login_id` int(11) NOT NULL,
  `username` varchar(255) NOT NULL,
  `password` text NOT NULL,
  `salt` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `login_has_site` (
  `login_id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL,
  `date_start` date NOT NULL,
  `date_end` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- In each client database:
CREATE TABLE `user` (
  `login_id` int(11) NOT NULL,
  `user_id` varchar(255) NOT NULL,
  -- other fields
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

What would be the advantage of recording the user_id in the "client" database in the "central" database vs storing login_id in the "client" database?
Also: the username column is referenced in a lot of places in the codebase. Would it be wise to make it a key in the central database and use e.g. on update cascade to maintain integrity, while not having to rewrite all code?


texts: Inner Join or direct data save in SQL Server

texts:
I am developing a social network site and expecting very good response from people so expecting huge users (maybe more than million in years). My website is holding user information table and all its related information like

Location

Country
State
City
Location

Education

School
College
University
etc

So finally user table will join with these and will fetch information through views. I am maintaining index properly. However thinking for future and want to make best solution for Select query.

My question is what is best process for faster select query, shall I do inner join among thees tables or should I  store direct location type information in user table as these changes very rare. All location fields will be nvarchar type and length around 100-250 char.


texts:
I am an administrator for a bunch of SSAS Instances.

Sometimes I need to install or migrate SSAS instances to different machines or even different AD domains (we have a couple of different AD domains in a trusted relationship).

Same as with sql server, I need to be able to export and import logins and permissions between instances.

Also, as part of maintenance plan I like to save logins and permissions as well as backup ssas databases.

I would to script the administration logins via script either T-SQL or Powershell so that I can automate it.

at the moment, because I was in a rush I did it in this way:

Connect to the original server as administrator and script all the permissions into a mdx file.
Copy this script and run it on the target server.

How can I script administrator permissions on a SSAS server?


texts:
I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data.
I've created a query that shows me the results and I'm wondering if I can rewrite this query in a single select without the subqueries.

My actual query looks like

    SELECT
         t1.CHECKID
        ,t1.CHECKNAME
        ,t2.Startdate
        ,t2.Enddate
        ,t2.StatusID
        ,t2.Month
        ,t2.Year
    FROM
    (SELECT CHECKID, CHECKNAME FROM CHECKS ) AS t1
    LEFT JOIN
    (SELECT
            STATUSCHANGES.CHECKID
            , STATUSCHANGES.DATETIME AS Startdate
            , MIN(STATUSCHANGES_1.DATETIME) AS Enddate
            , STATUSCHANGES.STATID AS StatusID
            , CHECKS.CHECKNAME
            , STATUS.STATUSNAME
            , DATEPART(MONTH,STATUSCHANGES.DATETIME) as Month
            , DATEPART(YEAR,STATUSCHANGES.DATETIME) as Year
        FROM
            STATUSCHANGES
            INNER JOIN STATUSCHANGES AS STATUSCHANGES_1
                ON STATUSCHANGES.CHECKID = STATUSCHANGES_1.CHECKID
                AND STATUSCHANGES.DATETIME < STATUSCHANGES_1.DATETIME
            INNER JOIN CHECKS ON STATUSCHANGES_1.CHECKID = CHECKS.CHECKID
            INNER JOIN STATUS ON STATUSCHANGES.STATID = STATUS.STATUSID
        GROUP BY
            STATUSCHANGES.CHECKID,
            STATUSCHANGES.DATETIME,
            STATUSCHANGES.STATID,
            CHECKS.CHECKNAME,
            STATUS.STATUSNAME
        HAVING
            (STATUSCHANGES.STATID IN (3))
            AND DATEPART(Q,STATUSCHANGES.DATETIME) = 2
        ) AS t2
        ON t1.CHECKID = t2.CHECKID
        ORDER BY t2.Startdate

Is it possible to integrate the first subquery t1 in the from clause of subquery t2?


texts: Except duplicates from duplicates based on columns

texts:
Here is what I am trying to do,

Get duplicates based on 2 columns (let say returns 500 rows)
Get duplicates based on above 2 columns + another column (let say returns 100 rows)

Now I want to get remaining 400 rows. In simple words, I want all duplicates where there are not duplicates because of columnC...

-- get duplicates based on ColumnA, ColumnB
SELECT '-'
    ,ColumnA
    ,ColumnB
    ,ColumnC
    ,COUNT(*)
FROM MainTable
     ...SOME joins(INNER AND left)
WHERE ColumnA IS NOT NULL
GROUP BY ColumnA
    ,ColumnB
    ,ColumnC
HAVING COUNT(*) > 1

EXCEPT

-- get duplicates based on ColumnA, ColumnB, ColumnC
SELECT '-'
    ,ColumnA
    ,ColumnB
    ,ColumnC
    ,COUNT(*)
FROM MainTable
     ...SOME joins(INNER AND left)
WHERE ColumnA IS NOT NULL
GROUP BY ColumnA
    ,ColumnB
    ,ColumnC
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

I am just not able to complete this query :(


texts: Postgresql function for detecting alerts

texts:
need help with PGSQL function for detecting temperature alerts.
Let's imagine we have tables

**measurment_tab**
id:int
room_id:int
measurment_date:timestamp
value:long

**alert_tab**
id:int
room_id:int
alert_value:long
date_add:timestamp
last_check_date:timestamp
alert_type:varchar [two possible values: below, over]

Last measurement value is:

10|2|2020-01-02 15:33:00|11.3

and alert value:

33|2|12.7|2020-01-02 16:00:00|null|over

In this case function should detect alert when:

measurement value is over 12.7
each check I believe should updated last_check_date field to current value (now()) to limit the collection of values to check

I think this scenario is quite simple. Next one is more complicated.

Last measurement value is:

10|2|2020-01-02 15:33:00|13.3

and alert value:

33|2|12.7|2020-01-02 16:00:00|null|over

As You can see current measurement value is over alert value. In this case alert shouldn't be triggered. Alert should be triggered when:

measurement value goes below alert value
after that measurement goes again over alert value

Thanks for advice.


texts: SQL Mirroring FailingOver from Principal times out

texts:
I have the following code:

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            while (true)
            {
                string Command = string.Format("SELECT COUNT(*) FROM [AdventureWorks2014].[Person].[Address]");
                using (SqlConnection mConnection = new SqlConnection("Data Source=SQL1.mike.local;Failover Partner=SQL2.mike.local;Initial Catalog=AdventureWorks2014;User Id=sa;Password=***************; "))
                {
                    mConnection.Open();
                    using (SqlCommand myCmd = new SqlCommand(Command, mConnection))
                    using (SqlDataReader reader = myCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(reader.GetInt32(0));
                        }
                    }
                    mConnection.Close();
                }
                Thread.Sleep(500);
            }

        }
    }
}

I run the console app, and manually failover the database within sql server management studio.

The app which is running just stops writing lines to the console, and after about 30 seconds, it fails with this exception:

  Unhandled Exception: System.Data.SqlClient.SqlException: A
  network-related or instance-specific error occurred while establishing
  a connection to SQL Server. The server was not found or was not
  accessible. Verify that the instance name is correct and that SQL
  Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)
  ---> System.ComponentModel.Win32Exception: The network path was not found

I do exactly the same thing, but failing back over from the failover partner, back to the original principal, and it works as expected. No error messages or exceptions, it just gracefully fails over and continues printing lines to the console.

What could be the problem? Why doesn't it fail over gracefully from the principal?


texts:
I have the following procedure:

drop procedure  if exists deletebatches;

create procedure deletebatches()

BEGIN
declare acount int default 0;
declare done INT default 0;
declare abatchno varchar(10);
declare abatchpart varchar(20);
declare cursor1 cursor   for select batchpart, batchno from batchestodelete;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;
SET DONE = 0;
open cursor1;
readloop: loop
fetch cursor1 into abatchpart, abatchno;
if done = 1 then leave readloop;
end if;

delete from stockbatches where batch_batch = abatchno
and batch_part = abatchpart;

select curtime();
end loop readloop;

close cursor1;

end;

call deletebatches()

This query runs for so long that I have not had the patience to let it finish.  I cancel it after an hour or so.

I also created a query to delete from stockbatches where batch_batch + batch_part in (select batch_batch + batch_part from batchestodelete) and it took 1,278 seconds (over 21 minutes!).

There are only 776 records in the batchestodelete table and about 1 million in the stockbatches table which has indexes on batch_part, batch_batch and both fields combined.

Can anybody offer an explanation as to why this is taking so long for what is a relatively simple query?

Here are some pieces of information various people have asked from me.

Show full process list shows:

2614 root localhost:4785 Sleep 4786
2621 root localhost:4792 new_web_files Sleep 4789
2967 root localhost:1283 new_web_files Query 3 updating
     delete from stockbatches where batch_batch = abatchno
     and batch_part = abatchpart 3014 root localhost:1336 new_web_files
     Query 0 show full processlist

Show create table stockbatches shows:

STOCKBATCHES CREATE TABLE stockbatches (
    batch_part varchar(20) collate latin1_general_ci NOT NULL,
    batch_batch varchar(10) collate latin1_general_ci NOT NULL,
    batch_qty decimal(18,2) default NULL,
    batch_cond varchar(2) collate latin1_general_ci default NULL,
    batch_release varchar(3) collate latin1_general_ci default NULL,
    batch_curedate varchar(10) collate latin1_general_ci default NULL,
    batch_lastmod datetime default NULL,
    batch_lastaction varchar(1) collate latin1_general_ci default NULL,
    PRIMARY KEY (batch_part,batch_batch),
    KEY partno (batch_part),
    KEY batchno (batch_batch),
    KEY batchandpart (batch_part,batch_batch)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Show create table batchestodelete shows:

batchestodelete CREATE TABLE batchestodelete (
    batchpart varchar(20) NOT NULL,
    batchno varchar(10) character set latin1 collate latin1_general_ci default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8


texts: Automatically set timestamps for "create" and "modified"?

texts:
I have currently following SQL table:

CREATE TABLE IF NOT EXISTS `xyz` (
  `id` bigint(20) NOT NULL,
  `guid` char(36) NOT NULL,
  `ts_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ts_modified` timestamp NULL DEFAULT NULL,
  ................
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT AUTO_INCREMENT=1 ;

ts_created is the timestamp when the entry was created. The default value is CURRENT_TIMESTAMP.

ts_modified is the timestamp when the entry was updated.

In phpMyAdmin, I could choose ON UPDATE CURRENT_TIMESTAMP for ts_modified. This would be exactly what I want.

But the error message is following:

  #1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

The error message is clear to me, but it is not clear to me

1) why this constraint is existing, since the "default" value (on create) and the "on update" value are two different things.

2) Is there anything I could do, or do I have to add ts_modified = NOW() at every UPDATE statement?


texts: Why do I get ERROR 1824 (HY000) while importing MySQL dump from 5.7 to 8.0.x?

texts:
I have a MySQL 5.7.x dump with about 12GB that I try to import into a newly installed MySQL 8.0.x local instance.
mysql -u username -p test < DB-210508.sql

The import starts but fails with the following message:
ERROR 1824 (HY000) at line 518: Failed to open the referenced table 'table-name'

I am wondering why there is a problem with foreign keys and if it is not more due to the fact that I am importing into 8.0.x
The MySQL 8.0.x instance is configured like this:
[mysqld]
default_authentication_plugin= mysql_native_password
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 32
innodb_buffer_pool_chunk_size = 134217728
collation_server        = utf8_unicode_ci
character_set_server    = utf8

What could be the reason for the failed import?


texts: Looping over column names in SQLite and running a command for each column name

texts:
I'm trying to run the following command

UPDATE attachments SET colname=TRIM(colname);

on every column name in a SQLite table.

I found the following approach to getting a list of column names in
an answer to "How to get a list of column names on sqlite3 / iPhone?", namely

SELECT name FROM PRAGMA_TABLE_INFO('your_table');

which seems like a nice, clean, simple way to do it. But this method is specific to SQLite, per PRAGMA Statements.

In any case, I'd like a solution that runs entirely in SQLite. I'm actually calling SQLite from Lua, using LuaSQL. So I could run the loop in Lua if necessary. But doing it inside SQLite just seems cleaner.

It would be even better if it's a general method that works for any RDBMS. But that may not be possible, and if it is, it would probably be more complicated


texts: DB2 - How to get value for: Transation Per Minute parameter for DB?

texts:
Hi is there some easy way to find out what is transaction per minute value for the database?
Should I use db2 get snapshot?
We are using DB2 9.7 ESE on Linux

Thank you in advance


texts: MariaDB/MySQL: Import from dump (.sql) file remotely via network

texts:
After I set my.ini file following the answer by Rolando here : https://dba.stackexchange.com/a/83385/191579 , today I managed to import about 40GB+ sqldump file in around 2-3 hours only. While on a different server; where I have set similar setting yesterday and importing around 20+GB data is still running today and I check it only managed to import less than half of the data inside (70+ million from a 200+ million rows).
Specs for both server are following:
Server 1 - import 40+GB data in 2-3 hours:
Intel Xeon E3 3.00 Ghz
16GB RAMs
MariaDB 10.3
Server 2 - import 20+GB data only managed to import less than half in 24hr:
Intel Xeon E3 3.40 Ghz
16GB RAMs
MariaDB 10.3
Clearly both server have almost similar specs but I don't understand why the other take so long to dump. Both of these database are not production/live db.
I usually opt to do import/dump locally for big files because I'm on the impression that it's much faster that way (obviously) but this case made me think of the otherwise. I'm currently taking the dump file into my PC to split it in smaller chunks and while waiting, I'm just wondering whether if I do import from a different pc via network (LAN etc.), will the process depending on the remote server pc specs or the local pc specs?
Edit:
I was looking around and found this : https://stackoverflow.com/a/12439368/10910692

Use:
mysqldump --extended-insert=FALSE
Be aware that multiple inserts will be slower than one big insert.

The 20+GB I dumped with this setting. Could it be the cause?
my.ini setting on Server 1 & Server 2:
[mysqld]

innodb_buffer_pool_size=1024M
innodb_file_per_table=1
skip-host-cache
skip-name-resolve

innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite=0


texts:
I have a merge subscription with the ExchangeType parameter set to 1 (upload only).
Rows are transferred to the publisher as expected.

If I delete a row at the subscriber, the corresponding row at the publisher doesn't get deleted. Accidentally, this is the exact behaviour I desire, but I can't find any documentation stating it's by design.

Before going live, I would like to have something documenting the thing.

Can anyone confirm this is the expected behaviour?


texts: Database design for tracked inventory that could get “transformed”

texts:
I need to track some serialized items in the inventory that can be either dismantled into multiple items or combined together into a single item. I have thought of a few options:

Flow tracking - Use two tables for inv_group_out -> inv_group_in with a joining table. Entry into either table can be a single item or multiple items each referencing the original inventory_id. New inventory item(s) get created for the re-entry of the inventory.
Movement tracking - Mark all changes in a single modifications table. Can't really think about how to reliably carry this out though.
Flow tracking alternative - Use the same table for original purchase/sales using a separate column to indicate that these are just inventory movements

Would love to hear some opinions on which would be preferred.


texts: How to log newly instantiated member status changes in a replica set

texts:
I'm trying to benchmark when a new mongo replica member

first communicates to my replica set.
change state from STARTUP2 -> RECOVERY
change state from RECOVERING -> SECONDARY

I'm specifically looking for exact duration on how long it took for the replica to run it's initial sync or the timestamps to parse such actions by a new member.

Is there a log that will give me this information?

I'm currently using the following script in mongo shell.

MAXSCRIPT_RUN_ITERATIONS = 1800 // approximately 30 minutes

for (var i = 0; i < MAXSCRIPT_RUN_ITERATIONS; i++) {
  sleep(1000);
  var currentdate = new Date();
  var datetime = new Date().toLocaleString();
  var rsStatus = rs.status()
  var members = rsStatus.members
  // Change this index depending on the # of members in your set
  var resyncmember = members[0]
  var resyncMemberState = resyncmember.stateStr
  print("--------------")
  print("Member Count: " + members.length + " " + datetime)
  for (var n = 0; n < members.length; n++){
    var member = members[n]
    print("HOST: " + member.name  + " State: " + member.stateStr)
  }
  if (resyncMemberState == "SECONDARY"){
    print("########################")
    var currentdate = new Date();
    print("resyncMember finished: " + datetime)
    print("########################")
    break
  }
}


texts: Importing CSV's into MySQL(5.6) tables leaves date fields as 0000-00-00

texts:
During the import from a CSV file created from an MS Access table into a MySQL table it converts all date fields in the csv into  0000-00-00. I use the following Load Data command for the import.

LOAD DATA LOCAL INFILE 'C:\\..\\tableName.txt'
INTO TABLE tableName
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Here's an example line from the CSV file im trying to import.

1,1,5/30/1934 0:00:00,"H","Vancouver, B.C."....

I tried the solution from "Dates changed to 0000-00-00" but it didn't work.


texts: Associate SQL Server Queries with Databases

texts:
Is there a way via T-SQL and DMVs to show which database a query was run against?

The goal is to look at CPU and I/O by query by database so I can redistribute the databases as I reconfigure the environment.

I do not want to use trace, profiler or extended events if I can help it. I also do not want to shred XML if I can avoid it. I am looking for history not just active.


texts: Hostgator php code error while creating a new database table

texts:
Error is
Error creating database: Access denied for user ''@'localhost' to database 'my_db'

Code is

{

<?php

$link=mysql_connect ("localhost", "cpuser_dbuser", "password") or die
 ('I cannot connect to the database because: ' . mysql_error());
 mysql_select_db ("cpuser_db");

$sql = 'CREATE DATABASE my_db'; if (mysql_query($sql, $link)) {
    echo "Database my_db created successfully\n"; } else {
    echo 'Error creating database: ' . mysql_error() . "\n"; } ?> }

I have replaced all the credentials here.

Please help me solving this error.


texts:
I am using MongoDb/PHP on a Linux server.
The problem is that, if a query takes too long to execute, the mongo server stop (apache keeps running).
So is there any way ,so that I can limit the time for a query to execute, and kill the process automatically like in php-  max execution time  ?

Till now I have found:

socketTimeoutMS, but I found some where , it will not kill the process.
cursor.maxTimeMS() , but I have to define it for each query.

So can anyone please suggest which approach I should follow so that

the query is automatically killed, if it takes more time,
I don't need to change every query.


texts: Postgres columns of latitude and longitude from varchar to numeric

texts:
I have a table with latitude and longitude columns and they are in varchar. I have to convert them to numeric because that is how the fields are in the master table.

Is there a way to change all the values in the columns at one time from varchar to numeric in PostgreSQL?

I am struggling because since they are lat/long values they vary in length, have decimals and the longitude is negative.

Table Name

Latitude      | Longitude
+-----------------------------+

35.0528620000 | -119.375136000
+-------------|---------------+

and they go on like this. I have tried ALTER TABLE, but that just gives me an an error, CAN NOT BE CAST
and I tried http://www.postgresql.org/docs/9.3/static/functions-formatting.html
to_char

EDIT: I was able to get the fields almost to the same length. Latitude is not 13 char and Longitude is 14 including the negative sign.

EDIT: The alter table error in Navicat:

[Err] ERROR:  syntax error at or near "ALTER"
LINE 1: EXPLAIN ALTER TABLE "allwells_CA_copy"

In Postgres:

ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71

I've been switching between PG Admin and Navicat in an attempt to make the change.


texts: Best approach to get last value inserted

texts:
I have several devices that report via Edge network their position (and more data) to a UDP server which directly inserts this data in a SQL 2008 r2. I want to know which is the best approach to retrieve the last position from the db, concerning performance upon insert and lookup of data.

I thought of these ways:

1) Master table & Last_position table, I insert the data in the master which holds all the data in the package, and a trigger in this table that updates the row in last_position table of the corresponding ID of device.

2) same as 1 but no trigger, and I manually make an insert and an update from the UDP server.

3) no last_position table, only master table and an index that allows me to retrieve last inserted row with something like this:

select top 1 pos_x, pos_y from master where device_id = @id  order by date_inserted desc

Currently we have implemented the solution number 1, but I'm not a big fan of triggers, specially in tables with very high insert/seconds rate. This table holds approximately 50 million rows, which migrate periodically to a master_historic table in order to hold only a week old data (in the historic we hold 2 months).

Any other solution is welcome.

Thanks!


texts: How to check when statistics was last executed?

texts:
We've been having a number of issues with our indexes lately which our DBA team has attributed to statistics not having been run recently. This has made me wonder - how can I check if statistics have been recently updated via SQL Management Studio?

I apologize if this question isn't explaining this very well - I've only been introduced to statistics until now and prior to this would look to indexes whenever I've had performance related issues.

Edit:

I'm using the following but receiving a syntax error:

use *databasename*
exec sp_autostats *schema.tablename*

The error I'm receiving is:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.

Why is this?


texts: Repeat row in the result based on the value of a column

texts:
FIRST_COL VAL_COL
A          2
B          3
C         4

I need output  as below.

FIRST_COL VAL_COL
A          2
A          2
B          3
B          3
B          3
C          4
C          4
C          4
C          4

example: my original table contains a=2 but I need in output as a=2 two times same for of b=3 three times c=4 four times


texts: Date of birth saving to MYSQL DB with timestamp or date column type

texts:
i am using MYSQL database and would like to know which of this is a good practice.

way 1 - use column type in DB as timestamp. i.e will contain values like 2012-11-13 00:00:00 way 2 - use column as Date. i.e will contain values like 2012-11-13 in the database.

The question here is, to Store DOB. do we really need timestamp as the column type ? also the time section (00:00:00) is not required for a DOB in my opinion.

Please let me know the correct way to store DOB in database.


texts: Find all values not present for other values of other column

texts:
Let's suppose I have a table with integer columns a and b (each pair (a,b) is unique) and following data:

 a | b
-------
 1 | 2
 1 | 3
 1 | 4
 1 | 5
 2 | 1
 2 | 3
 2 | 4
 3 | 1
 3 | 3

Now I need to get all the rows whose b value is not contained in the set with some other a value (i.e. they appear just once in the union of rows with the 'a' values in question). From these I should get (1,2), (1,5) and (2,1).

I want to include only two sets (groups with the same a value; I want to use this as part of a function, where both a values are given as parameters) at a time, so I don't want to get (3,1) from the new data.

I have some (working) solution; I post it as an answer. However, there should be something more elegant - the query should be one and exploit the symmetricity, not two unioned queries. I thought of various solutions listed in this answer, but after some hour of playing with code I didn't find how to make anything better and working.


texts:
I have an issue that I want to do conditional replication to one of my enterprise level client databases. This database is very large: approximately 300 GB MDF file and a 160 GB LDF file because of archiving and accountability.

My primary DB has lots of accountability data and I want to transfer them to the secondary replicated DB for reference purposes. Once the accountability data has been deleted by a job on the primary server, when the replication job runs again
the secondary DB should retain all the accountability data without overwriting and/or deleting anything.

Does anybody have a suggestion on the best way to do this?


texts:
in one of my databases I have the following table:

CREATE TABLE [app].[applicantSkill](
    [ApplicantSkillID] [int] IDENTITY(1,1) NOT NULL,
    [applicantID] [int] NOT NULL,
    [skillID] [tinyint] NOT NULL,
    [skillDetails] [varchar](500) NULL,
    [skillLevelID] [tinyint] SPARSE  NULL,
    [dateAdded] [datetime2](7) NOT NULL,
    [lastModified] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_tbl_applicant_skill] PRIMARY KEY CLUSTERED
(
    [ApplicantSkillID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [UserFG],
 CONSTRAINT [uc_appSkillID] UNIQUE NONCLUSTERED
(
    [applicantID] ASC,
    [skillID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [UserFG]
) ON [UserFG]
GO

which has 1 sparse column - skillLevelID

when I have to re-create my index:

CREATE NONCLUSTERED INDEX I_applicantID
ON [app].[applicantSkill] (  [applicantID] ASC  , [dateAdded] ASC  )
INCLUDE ( [ApplicantSkillID] , [skillDetails] , [skillID] , [skillLevelID])  WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, DROP_EXISTING = OFF, DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [NONCLUSTERED_INDEXES]

I get the following error message:

Msg 10622, Level 16, State 1, Line 18
The index 'I_applicantID' could not be created or rebuilt. A compressed index is not supported on table that contains sparse columns or a column set column.

How can I find out which one the sparse column or the data_compression should I keep?


texts: How to do 10 G Database recovery without archive logs but available redo logs, ctl files and .dbf files

texts:
My client's Oracle 10g database crashed abruptly on Win 2003 NT. The oracle was installed on C drive and redo logs, ctl files and .dbf files were in D:
It was unable to backup C: but successfully backup D:
Later the system was rebuild with updated RAM and high disk size. Oracle is re_install in C: as it was and latest .dbf, CTL and redo logs in D:

Not sure if the below steps would solve the purpose. Please suggest ASAP.

SQL> startup nomount;
SQL> alter system set control_files
Then you mount the database so control file can be read and written
SQL> alter database mount;
SQL> alter database rename file for .dbf files
SQL> alter database rename file for redo logs

If it would be using PFILE then will use Init.ora to update the location.

SQL>startup mount
SQL> ALTER DATABASE OPEN;


texts: Does READ UNCOMMITTED have the same effect as AUTOCOMMIT=1?

texts:
AFAIK running transactions as READ UNCOMMITTED means that there is no lock, so every change caused by a transaction is immediately visible to other transactions.

Is it correct to assume that this is the same behaviour we get with any transaction if we enable AUTOCOMMIT? AFAIK in this case, as well, the effects of any query are immediately visible to others.

Of course, the two concepts are somewhat different, semantically, but is there any difference in what's actually happening?

I'm interested in a general answer, though I do tend to care more about MySQL and Microsoft SQL Server.


texts: Resolving port 5500 not free issue during Oracle 12c Release 2 Database Configuration Assistant installation on CentOS

texts:
Background

I am attempting to install Oracle 12c on CentOS 7 (deployed as a Hyper-V virtual machine) as outlined by this installation guide.

Problem

Everything with my installation goes well except for the Oracle Database Configuration Assistant.

The installation provides me with the following messages:

ACTION: Specify a free port
Action - Refer to the logs or contact Oracle Support Services.

Debuging Information and Actions Attempted

When I run a tail on the error log I get the following messages related to the installation:

INFO: ... GenericInternalPlugIn: getting configAssistantParmas.
INFO: ... GenericInternalPlugIn: checking secretArguments.
INFO: ... GenericInternalPlugIn: starting read loop.
INFO: Read: [FATAL] [DBT-06103] The port (5,500) is already in use.
INFO: [FATAL] [DBT-06103] The port (5,500) is already in use.
WARNING: Skipping line: [FATAL] [DBT-06103] The port (5,500) is already in use.
INFO: Read:    ACTION: Specify a free port.
INFO:    ACTION: Specify a free port.
WARNING: Skipping line:    ACTION: Specify a free port.
INFO: Completed Plugin named: Oracle Database Configuration Assistant

Running sudo netstat -anp | grep 550 returns the results below:

unix  3      [ ]         STREAM     CONNECTED     37299    5502/gnome-pty-help
unix  3      [ ]         STREAM     CONNECTED     37283    5501/gconfd-2
unix  3      [ ]         STREAM     CONNECTED     37287    5501/gconfd-2

To try and remedy the problem, prior to subsequent fresh installation attempts I commented out the following statements in my services file as displayed through running cat /etc/services | grep 550

new-rwho        550/tcp                 # new-who
new-rwho        550/udp                 # new-who
3m-image-lm     1550/tcp                # Image Storage license manager 3M Company
3m-image-lm     1550/udp                # Image Storage license manager 3M Company
ads             2550/tcp                # ADS
ads             2550/udp                # ADS
ssmpp           3550/tcp                # Secure SMPP
ssmpp           3550/udp                # Secure SMPP
gds-adppiw-db   4550/tcp                # Perman I Interbase Server
gds-adppiw-db   4550/udp                # Perman I Interbase Server
#fcp-addr-srvr1  5500/tcp                # fcp-addr-srvr1
#fcp-addr-srvr1  5500/udp                # fcp-addr-srvr1
#fcp-addr-srvr2  5501/tcp                # fcp-addr-srvr2
#fcp-addr-srvr2  5501/udp                # fcp-addr-srvr2
#fcp-srvr-inst1  5502/tcp                # fcp-srvr-inst1
#fcp-srvr-inst1  5502/udp                # fcp-srvr-inst1
#fcp-srvr-inst2  5503/tcp                # fcp-srvr-inst2
#fcp-srvr-inst2  5503/udp                # fcp-srvr-inst2
#fcp-cics-gw1    5504/tcp                # fcp-cics-gw1
#fcp-cics-gw1    5504/udp                # fcp-cics-gw1
checkoutdb      5505/tcp                # Checkout Database
checkoutdb      5505/udp                # Checkout Database
amc             5506/tcp                # Amcom Mobile Connect
amc             5506/udp                # Amcom Mobile Connect
fg-sysupdate    6550/tcp                # fg-sysupdate
fg-sysupdate    6550/udp                # fg-sysupdate
cloudsignaling  7550/udp                # Cloud Signaling Service

I have additionally attempted to do step 5.1 of the installation guide prior to the installation with no success.

Screenshot

Question

Why am I getting an error that the port isn't open when it doesn't seem like anything is using that port and how to I remedy this issue?

Is there are method that can be used to change the port from 5500 to a complete different one? I'm just trying to get a dummy database installed for the HR schema.


texts:
I have a user with only CONNECT privileges and I'd like to know if RESOURCE would be enough to query objects in other schemas and create objects in my own schema based on that.

i.e.: create a view in my own schema by selecting tables from other schemas.

Would a SELECT ANY TABLE privilege be enough or does my user have to be granted specific object privileges for any action on another user?


texts: SQL Server service start automatically only after Windows Shutdown, how can I make restart automatically without batch file?

texts:
I am using MS SQL Server 2014, When I restart my Pc the SQL Server Service does not start automatically, however, it does after Windows Shutdown and run again, how can I make it restart automatically ?
Without using batch file


texts:
I have two Oracle 18c instances (instance 1 with schema DB1 and instance 2 with schema DB2) and I know that there ist a support for distributed lobs since 12.2. So i did the following:

CREATE TABLE DB1.TEST_TABLE ID NUMBER, FOO CLOB;
INSERT INTO DB1.TEST_TABLE (ID, FOO) VALUES (1, "RANDOM TEXT");

Now I created da databaselink to access DB1 from instance 2

SELECT * FROM DB1.TEST_TABLE@REMOTE_LINK

The select works perfect. But if I want to create a view with this select I get an error:

CREATE VIEW DB2.V_TEST_TABLE AS SELECT * FROM DB1.TEST_TABLE@REMOTE_LINK

ORA-22992: cannot use LOB locators selected from remote tables
22992. 00000 -  "cannot use LOB locators selected from remote tables"
*Cause:    A remote LOB column cannot be referenced.
*Action:   Remove references to LOBs in remote tables.

I don't undertand why selecting a distributing lob works fine but creating a view of this select not.


texts: After processing complete, SSAS DB slowly consumes all available space on disk, w. high memory use also

texts:
This problem is with an SSAS 2012 SP3 (11.0.6020.0) multi-dimensional server, hosting a single cube database, containing 1 cube and 16 dimensions. Aggregations have been designed using SQL Management Studio (to 30% gain), and extra aggregations added from the Usage Based Optimization wizard. The server has separate disks for Windows and the SSAS Data and Temp folders. This is what happens:

The cube database is fully processed successfully, and afterwards the disk containing the Data folder has a healthy 300GB out of 400GB free. This screenshot shows the largest files from particular partition folder under the .cub folder. All the files have the datestamp 21/04/2016 14:14 (the processing time).

Over the next few hours, the Data folder slowly grows, until it has consumed all of the available 400GB on the disk. This screenshot shows the largest files from same folder as above. Some of the original files can be seem, plus a number of new files. I don't think any of the files prefixed "176" were present immediately after processing.

During this time, the SSAS service is consuming almost all the available memory (24GB out of 32GB), and is very unresponsive - queries are very slow and attempts to access the properties of the server thru SQL Management Studio fail.

Once all the disk space is used, SSAS becomes unusable, and the SSAS service cannot be restarted.
Restarting Windows causes the disk containing the Data folder to get its 300GB free space back - the files dated after 21/04/2016 14:14 have disappeared. However, the process begins to repeat and the free disk space is again eaten up.

What could be going on and how do I stop it?


texts: How do I return JSON structured as the transpose of json_agg() in PostgreSQL?

texts:
I'm trying to convert an arbitrary subquery to JSON in PostgreSQL.

json_agg() seems like the obvious tool for the job. For example:

select json_agg(a) from (
    select count(lead_source), lead_source from leads_table group by lead_source order by count desc
) as a;

However, data is structured as the_data[row_number]["column_name"].
Suppose I want data structured as the_data["column_name"][row_number]. That is, I want the transpose structure of json_agg(). How do I do that?

My best effort is:

select row_to_json(row(array_to_json(array_agg(count)), array_to_json(array_agg(lead_source)))) from (
    select count(lead_source), lead_source from leads_table group by lead_source order by count desc
) as a;

But this has (at least) two drawbacks:

It doesn't work for an arbitrary subquery. The column names must be hardcoded each time array_agg() is used.
The column names become f1, f2, ...


texts: How to execute MySQL query after 1 minute

texts:
I have a query, but I want to call it now and wish it to be executed after few mins and seconds. Please note: I don't want to use SLEEP(), as it 'freezes' the entire database. I want one of the queries to be delayed while I perform the others queries in a default manner.

Small example that doesn't works:

WAITFOR DELAY '00:00:10'
update users set 'id' = 10 where `id` > 5

WAITFOR DELAY is a SQL Server command

How do I solve this problem?


texts: MySQL Shell Instance dump - strip_restricted_grants

texts:
I am reading the documentation https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
For the option strip_restricted_grants, there is a line which says, 'Remove specific privileges that are restricted by MySQL Database Service from GRANT statements'
I am wondering which privileges are they? They haven't mentioned any specific privileges in the documentation


texts: Running Powershell Code embedded in SSIS or TSQL, not in external file

texts:
Is there a method to embed powershell code in SSIS or T-SQL, without saving the code in an external file *.ps1? We know SSIS/Tsql can run powershell, however the code always seems to be saved somewhere else. We want powershell code embedded in SSIS package or t-sql stored procedure, which then can be executed.

Time issue with Dbas, setting up file share folders, and then deploying these powershell files into our Dev, QA, Prod environments (we have multiple servers), this can alleviate some of the Devops process.

1) The T-sql route may use xp_cmdshell which can be security issue, not sure if this is true. Is there safe way with xp_cmdshell or totally different route in T-SQL area?

2) The article below explains running Powershell in SSIS, however it is calling another file. Can we embed code in SSIS, and run powershell?

https://blogs.msdn.microsoft.com/ssis/2017/01/26/run-powershell-scripts-in-ssis/

At the end, I am trying to run a workflow parallel process.
Powershell Run stored procedures in parallel in database

Workflow TestRunParallelExecute
{
    $ServerName = "localhost"
    $DatabaseName = "testrun"
    $Procedure_Query = "select name from sys.procedures"
    $Procedure_List = (Invoke-Sqlcmd -Server $ServerName -Database $DatabaseName -Query $Procedure_Query)

    ForEach -Parallel ($Procedure in $Procedure_List.Name)
    {
         Invoke-Sqlcmd -Server $ServerName -Database $DatabaseName -Query $Procedure
    }
}
TestRunParallelExecute
cls


texts: how to get count(*) from table which either has column or not?

texts:
I have a post table as below:

CREATE TABLE `post` (
  `p_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` smallint(5) unsigned NOT NULL,
  `cat_id` tinyint(3) unsigned NOT NULL,
  `view_count` smallint(5) unsigned NOT NULL DEFAULT '0',
  `status` tinyint(3) unsigned NOT NULL,
  `abstract` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `content` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `title` varchar(50) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL DEFAULT '',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `meta_description` tinytext CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `picture` varchar(100) NOT NULL,
  PRIMARY KEY (`p_id`),
  KEY `user_id` (`user_id`),
  KEY `date` (`date`),
  KEY `pic_id` (`picture`),
  KEY `cat_id` (`cat_id`),
  CONSTRAINT `post_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `karbar` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `post_ibfk_3` FOREIGN KEY (`cat_id`) REFERENCES `category` (`cat_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

And I have a comment table as below:

CREATE TABLE `comment` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `p_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `name` varchar(50) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL DEFAULT '',
  `site` varchar(200) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `content` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `p_id` (`p_id`),
  CONSTRAINT `comment_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `post` (`p_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

I want to get one post(e.g p_id=8) and all of its comments. how to do that? How to get count(*) from comment table while your getting your post record?


texts: DBMS_SCHEDULER event schedule jobs not executed when triggered almost at the same time

texts:
I moved this question from stackoverflow to here as it was marked on hold as off-topic. On Oracle 10g2 DB I have set up multiple event triggered DMBS_SCHEDULER jobs and all of them are subscribed for the same event queue. The problem is when I call the function that enqueues the message in the advanced queue, therefore triggering the scheduler event all of the subscribed jobs are executed only once.

In the loop I am calling procedure that enqueues the messages in the
queue. (I did check the message count in the queue and it matches the
loop count);
There are multiple jobs (or a single job - I did test both cases the
result is the same) that have event schedule and are subscribed to
the particular queue;
After executing this loop, I do check the message count in the queue
and the instance count of the new jobs in sys.dba_scheduler_job_log.
Result - all the messages in the queue have been processed (meaning
it is empty) and only one instance per each job have been registered
in scheduler_job_log table;
The thing is that if I add DBMS_LOCK.sleep(1); after at the end of the each iteration in the loop then all of the job instances are registered in the scheduler_log_table fine.

Why do all of the other queue messages processed from the queue without running the job? Are there any DBMS_SCHEDULER settings to be adjusted?


texts:
I have a bunch of asmdisks:

/dev/oracleasm/disks/DATA01

/dev/oracleasm/disks/DATA02

..

/dev/oracleasm/disks/DATA07

Probably, they were created as:

oracleasm createdisk DATA01 /dev/sda1

oracleasm createdisk DATA01 /dev/sda2

But I'm not sure.

How to define relied block device for created ASM disk?
How to be sure that DATA01 is made of /dev/sda1?


texts: Oracle - how to reference to a field%TYPE using database links?

texts:
I have this database which includes a couple of schemas. Say I need to access schemaONE from schemaTWO. To do this, I have created a database link named "DB_ONE_LINK".

I am able to access to schemaONE's tables using the following syntax:

SELECT * FROM "Tbl1"@"DB_ONE_LINK";

Now, when creating SPs and functions in schemaONE, I can define variables like this:

tmp_amount "schemaONE"."Tbl1"."Amount"%TYPE;

The question is, how can I reference to a field type in schemaONE using "DB_ONE_LINK"?
something like this:

tmp_amount "Tbl1"."Amount"@"DB_ONE_LINK"%TYPE;

Is it even possible?


texts: How do I force SQL Server to use my spatial index through a view?

texts:
I have tables which holds transactions for properties that are stored as lat long pairs. (There are more columns and datapoints then in my example schema).

A common request is to find the transactions that happened within X miles of a particular point, and only retrieve the 5 most recent transactions that happened for each nearby property.

To make this work, I decided to add a view that encapsulates this logic of most recent:

create or alter view dbo.v_example
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent= iif(row_number() over (partition by latitude,longitude order by transaction_dt desc) < 5,1,null)
from dbo.example;

So a query might look like this::

select *
from dbo.v_example
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
and most_recent = 1

Unfortunately, SQL Server  does not want to use the spatial index when I query through the view. If I remove schemabinding and try to add a hint on the view, I get that the query processor cannot create a plan.

How can I encapsulate the logic and still get it to use my spatial index?

Here's a db<>fiddle with example data and plan shapes.

The table is much larger, and it is much slower to scan it then to do a clustered index seek and then find the near by point.


texts: Does Postgres automatically rotate WAL files out (pg_xlog) if archive_mode is off?

texts:
I'm relatively new to PostgreSQL administration and I'm attempting to pick up the nitty gritty details as I go. My question is, assuming I have no need for disaster recovery and I don't turn on archiving by setting up archive_mode = on, does Postgres have an internal mechanism to rotate WAL files out of pg_xlog? if so, what is the frequency?

On a slightly different note, what is the impact of setting archive_mode = on but not configuring an archive_command - is there a default location Postgres will attempt to archive to?


texts: Can a table be un-normalizable or am I misunderstanding the theory

texts:
I was considering normalizing a flat table (SQLite if its relevant) I'd been working with in a small personal project and it just didn't seem to make sense.
Consider an example table like below. Each row is independent of the others, identified by the unique Project Number. A given Customer can have multiple CustTypes, a given CustType can order various Products
ProjNum(K)    ProjName    DueDate       Customer      Product     CustType    Stage           LastAction
001            Proj1     01-12-2020      foocorp      widgets       A         closed          14-06-2020
002            Proj2     02-12-2020      barcorp      gadgets       B         in progress     17-06-2020
003            Proj3     03-12-2020      foocorp      sprockets     B         waiting          NA

At first I thought Customer, Product, Branch, and Stage need to be moved to other tables because they technically "repeat" data but at the same time it can be argued they don't. Every row is independent of all others, and every column tells a single atomic fact about a given unique Project. Because a Customer can have multiple CustTypes and order various Products they all ultimately depend solely on ProjNum.
If I move any of these columns to another table I'll always wind up with repeating entries. I could move Customer to another table and give "foocorp" a CustID but then I'd still wind up with a table tying ProjNum to CustID where that CustID shows up twice. It's the same thing as this table but with more steps.
Does that mean this can't be normalized?


texts:
I am using a Mongo sharded cluster with 4 shards.
All shards are a 3 members replica set with a PRIMARY with priority 1, a SECONDARY with priority 0.5 and an arbiter.
The Mongo config server is a 3 members replica set as well.

Mongo version 3.2.11
The config server is on the same server as the first shard.

My Java app execute requests through two mongos instances.

The issue:
The server of the shard 1 PRIMARY failed, the SECONDARY got elected after some time but no READ or WRITE was possible after the failure from my Java app.
When I restart the Java app, all came back to normal.

My guess:

The Java app was waiting until its current writes end, but it never happened? (and the restart freed the connections)
A timeout is missing on mongos?


texts: Does PostgreSQL always initialize database of same size?

texts: Let's say I run CREATE DATABASE abcd, PostgreSQL will create an empty database and if I run SELECT pg_size_pretty(pg_database_size('abcd'));, I get size of the database, let's say 8000kB. What I want to know is will the size of the empty database always be the same no matter what or does it depend? I specifically need to know if empty database size would change over time (create database 1 year from now) or if it would change over quantity (10000th database). I can't test for those cases and I couldn't find any information on that. Thank you.


texts: Collecting parameter values from Extended event session

texts:
I'm using extended events to trace the statements used by our application.
I have created a session that will collect the information that I want, except that I would also like to know the actual values of the parameters that was used.

I am performing this trace on a non-production environment and any performance hit by the trace is acceptable.

All the queries originate from Hibernate and have been parameterized in the form of:

SELECT a, b, c From Customer where CustomerId = @P0

This is my session setup.

CREATE EVENT SESSION [TracingForStatements] ON SERVER
ADD EVENT sqlserver.sp_statement_completed (
    ACTION (
        sqlserver.session_id,
        package0.collect_system_time,
        sqlserver.transaction_id,
        package0.event_sequence
    )
    WHERE (
        sqlserver.database_id=555
    )
)
ADD TARGET package0.ring_buffer(SET max_memory= 128000)
WITH (EVENT_RETENTION_MODE = NO_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 1 SECONDS)

How can I capture the actual values of the parameters that was used in the captured statements? Is it possible?

-- Edit (Workaround):
Changing the event to rpc_completed gives me the complete sql command(including all parameter values) called from Hibernate. For other cases it would still be good to know how to capture the parameter values, if it is possible.


texts: Backup compression in SQL server 2008R2 and above

texts:
Recently we got a new server to take care of and found that there are two data drive:

S: 1 TB where resides 10 databases(mdfs and ldf's, though they should be on different drives but that's how we got the set up) where max size of a DB being 780 GB and rest pretty small.

X: similar scenario  here as well a DB of 720 GB resides rest small and bits, total of 10 DB's

Now, we've been asked to perform the backup strategy and for that i am sure we are short of backup drive as there does not exist any other drives apart from C: and above two.

Since this being sql server 2008R2 enterprise, Compression feature has been enabled and we are good to take compressed backups:

But my question here would be how can i predict or calculate what size of drive would be require to accommodate the backups, means using the compression feature how can i predict close to the amount of disk space required to backup these 20 user databases.


texts: Can't connect to postgres on mac OS catalina

texts:
I have a problem with connecting to postgresql on mac os catalina.
I had postgresql installed, but I deleted it with the uninstall file (library > postgresql > uninstall). The file is gone and I also deleted the postgresql user from the system.
I want to do a fresh (re)install of postgresql. I did the following:
brew update
brew doctor
brew install postgresl

When I do $postgres --version, I get the following output: postgres (PostgreSQL) 13.0. Also, the commands brew services start postgresql and brew services stop postgresql work.
I have a folder named /usr/local/var/postgresl.
When I do the following command: psql, I get the following output:
psql: error: could not connect to server: could not connect to server: Connection refused
Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

I also cannot connect to the database with pgadmin.
What am I doing wrong?
Edit:
When I do initdb /usr/local/var/postgres -E utf8, I get the following output:
initdb: error: The program "postgres" is needed by initdb but was not found in the same directory as "/usr/local/Cellar/libpq/13.0_1/bin/initdb".
Check your installation.


texts:
I need to find the department that has the oldest employee. My plan is to merge everything into one big table and try to sort the employees by age, then find the department that has the oldest employee, and find the manager for that department. Currently this is my query:

SELECT *
  FROM employees emp
  JOIN dept_emp de       ON emp.emp_no=de.emp_no
  JOIN departments dep   ON de.dept_no=dep.dept_no
  JOIN dept_manager depm ON dep.dept_no=depm.dept_no
  JOIN employees emp2    ON depm.emp_no=emp2.emp_no
-- some part of query is lost here --
           DESC
 LIMIT 0, 1

;

Unfortunately, when I run that, it's saying that column 'birth_date' is ambiguous to sort - which makes sense bc I'll have 2 columns with the same date.


texts:
I have an specific table that is taking forever o VACUUM.

There are no locks in the DB and even letting it overnight the VACUUM didn't finish.

According to the statistics (ANALYZE works fine) the table has 45GB in size and around 250K rows.

Is there some way to show the current status of the VACUUM and/or improve the way it executes?


texts: MySQL: check max chracter length (ERROR 1118 (42000): Row size too large)

texts:
I'm trying to convert/copy MyISAM to InnoDB table and I'm getting the following error message:

mysql> INSERT INTO test.innodb_table
    SELECT * FROM test.myisam_table
ERROR 1118 (42000): Row size too large (> 8126).
Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC
    or ROW_FORMAT=COMPRESSED may help.
In current row format, BLOB prefix of 768 bytes is stored inline.

The MyISAM table has many VARCHAR(255) columns so it looks like it's reaching the maximum row size limit for InnoDB table.

Questions:

How can I check on which row(s) the query above is actually failing so I can potentially remove it? I tried to use where clause and I was able to insert ~5000 rows this way but the orignal table has over 2 million rows.
I would like to limit the size of these variable length columns. How I can check the max character length for all columns? Pseudocode: if column type equal to VARCH (255), show rows with max character length

UPDATE: Thu Apr 16 15:58:19 BST 2015:

I constructed the following query to get a list of rows that exceed the max row length:

mysql> SET SESSION group_concat_max_len = 102400;
    SELECT CONCAT('SELECT id, ',
             GROUP_CONCAT('LENGTH(', 'IFNULL(', COLUMN_NAME, ',
                      0)', ')' SEPARATOR '+'), ' AS total_length
        FROM myisam_table HAVING total_length > 8126;') AS 'Query'
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = (SELECT DATABASE())
      AND TABLE_NAME = 'myisam_table'\G

When I execute a query from the output of the query above, I receive a list of rows with a size greater than 8126 bytes:

+---------+--------------+
| id      | total_length |
+---------+--------------+
|     489 |        10938 |
|    5244 |         9141 |
|    5869 |         9680 |
|    6083 |        21284 |
|    6654 |         8644 |
...

I can then successfully insert this query so I don't understand how this exactly works and where is this size limit imposed:

mysql> INSERT INTO test.innodb_table SELECT * FROM test.myisam_table WHERE id = 489;


texts:
I have made a featureclass for a utility company where they can register measures set up to mitigate problems with rats, called "mitigation measure". From this feature class it is possible to state if the current mitigation measure has been removed or not. This field is named "Removed" and it is a domain value with possibilities "Yes", "No" and "Not set".

I now wish to create a sql trigger that can update a time field called "DateRemove" with the current timestamp for the moment the utility company changes the field "Removed" from "No" to "Yes". But only when this exact change happens, not when other changes occur within the feature class.