texts: SQLServer AlwaysOn Availability Groups in 2014 and 2016

texts:
We're designing a new, HA database cluster to support two of our vendor software applications; both applications are mission critical so we are trying to make sure we get this right.

One vendor will support SQLServer 2016 but the other is only validated against 2014. The second vendor is not preventing us from using 2016 but, I know customer support will be an issue down the road (it'll be the thing they blame any time anything goes wrong...)

Both applications are pretty vanilla in terms of how they use the database.

I'd like to know whether there are compelling enough reasons to use 2016 that I would risk the support issues with my second vendor. Stability and operational support issues vs. 2014 are my biggest concerns.


texts:
Unfortunately, I am assuming like most folks in this role, the title DBA came by default rather than design.

I am trying to manage our farm of 5 SQL Server 2016 servers, each having a warm stand-by for our Disaster Recovery.

We have queried our user base and have come to the decision that some downtown is acceptable as long as we can minimize data loss.  We do not need point in time recoveries, only to ensure that we have the most current data possible based on the users data loss acceptability.

What we have decide for our model is each database on each of the primaries will be backed up and restored on the appropriate stand-by (with the database left in Stand-By/Read Only mode) and then a log shipping job created to ship the log to the standby server every 30 seconds to 1 hour depending on the data loss sensitivity.  Then, on each stand by server, once every 15 minutes to 1 hour, a restore job runs to restore all the transactions shipped from the primary into the stand by database.  Once the transactions have been shipped to the stand by and restored successfully, I no longer care about those transactions.

This works great as I can query the stand by databases and see the updated data as expected.

Where I am lacking information in is, my transaction log is growing larger than my DB file on my primary.  I ASSUMED that the log shipping would mark the log as backed up, but I am apparently wrong.

I am hesitant to create a job that takes full backups as I do not want to break the log shipping chain so here is where I need some advise on how to control my log sizes.

I have queried the internet for answers, but I get about as many different answers as there are results, so I am turning here for advise.

Thanks in advance,
Keith


texts:
I have a user U in a database where I want it to be able to create types and stored procs in a specified schema say UserSchema but not be able to alter schema dbo. So I created the schema and also applied the following grant statement to it:

GRANT  ALTER, Execute ON SCHEMA ::UserSchema to [U];

When I login as user U and I try create a type in UserSchema it fails with a permissions failure...

Any Ideas?


texts: Locking/transactions in MySQL Stored Procedure

texts:
Multiple upstream Kapacitor servers are sending notifications to a load-balanced django app. If all the upstream servers are working correctly, the app will always receive duplicates of these notifications (since all the upstream notifiers should send the same notifications; it's just for redundancy). I want to be able to filter out these duplicates. However, since the python app is load-balanced, the only place we can check for these duplicates is in the database.

This means I'm using this stored procedure to control application logic, just inserting the data with the hash into the database and ignoring duplicates is not an option (the application might do something like send someone an SMS message based on the contents of the alert, so we definitely don't want dupes)

To do this, I'm hashing the messages, then invoking a stored procedure in the database that checks if a message received in the last 10 seconds had the same hash. I want to be 99% sure the stored procedure is safe against race conditions.

Here's some SQL code that seems to work:

DROP PROCEDURE IF EXISTS openduty_check_duplicate;
CREATE PROCEDURE openduty_check_duplicate(IN new_hash CHAR(40))
  BEGIN
    DECLARE now TIMESTAMP;
    DECLARE is_duplicate INT;
    SELECT CURRENT_TIMESTAMP INTO now;
    DO GET_LOCK('openduty_check_duplicate', 10);
      START TRANSACTION;
        DELETE FROM openduty_dedup WHERE DATE_SUB(now, INTERVAL 10 SECOND) > triggered;
        SELECT COUNT(*) FROM openduty_dedup WHERE request_hash = new_hash INTO is_duplicate;
        IF is_duplicate = 0 THEN
          INSERT INTO openduty_dedup (request_hash, triggered) VALUES (new_hash, now);
        END IF;
      COMMIT;
    DO RELEASE_LOCK('openduty_check_duplicate');
    SELECT is_duplicate;
  END;

Will this work as intended? DO I need to change the transaction isolation level? Is there a better way to do this in MySQL?

Note I'm using MySQL 5.6 in one environment and Amazon RDS in another environment, so it should be as compatible as possible.


texts:
Is there a specific term to describe a relation with a cardinality of one?

I'm thinking particularly of RDBMS "special" one-row tables, for example, DUAL (Oracle), SYSIBM.SYSDUMMY1 (DB2, Derby) and RDB$DATABASE (Interbase, Firebird).


texts:
I am trying to understand how the indexes are managed for below common data types:
1- Numeric (Integer, Decimals)
2- String (Varchar, Char)
3- DateTime

I have few questions:

1- How the indexes are stored for different data types? For example if have Numeric data like (100, 101, 100, 900, 700) & string data like (AAA,BBB,CCC,AAA,ABC) how the numericcolumn & stringcolumn index will store this data on disk?

2- Is there any different between the retrieval of a Numeric & String indexes? For example if i try the SELECT statements on numericIndexed column & on sringIndexes columns how they are retrieved?

3- Is the indexes are stored different by SQL Server & Oracle? OR they use the same logic?
Regards.


texts:
I have mongodb 2.4 database with some indexing. Now I want to upgrade that version to 3.0. I searched on google but did not find any firm answer. I tried installing 3.0 but then it gives me error of index.
Does anybody have idea about this?

Answer:

I am able to upgrade the DB. Below are the steps :
1. Install mongodb 2.6
2.  Update the index of admin collection
    db.getSiblingDB("admin").runCommand({authSchemaUpgrade: 1 });
3. Install mongodb 3.0


texts: What are the security implications of sp_executesql?

texts:
By default, in SQL Server, the [public] role has EXECUTE rights on sp_executesql.

However, I've inherited a database server where the previous DBA has revoked the EXECUTE right on sp_executesql.

As a temporary workaround, I've been granting EXECUTE rights to sp_executesql on an as-needed basis (through a role in the master database). But this is beginning to be a maintenance pain.

If I grant EXECUTE back to public, are there any repercussions I need to be aware of?


texts:
I recently installed MongoDB on an AWS EC2 instance running Ubuntu.

When I'm starting the MongoDB service, I get some warnings:

$ mongo
MongoDB shell version: 3.0.11
connecting to: test
Server has startup warnings:
2016-04-01T07:28:10.926+0000 I CONTROL  [initandlisten]
2016-04-01T07:28:10.926+0000 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
2016-04-01T07:28:10.926+0000 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2016-04-01T07:28:10.926+0000 I CONTROL  [initandlisten]
2016-04-01T07:28:10.926+0000 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2016-04-01T07:28:10.926+0000 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2016-04-01T07:28:10.926+0000 I CONTROL  [initandlisten]
>

I think that this is what prevents the migration from Parse.com, as I posted on this question.

How can I ignore these warnings and disable it?


texts:
I am trying to use built-in XML functions, but I'm getting an %s: invalid identifier error. Selects works fine without the XML functions.

Any ideas where is the mistake?

select id,full_name from users where id in(
with sample_data as
(
 (select pm_id
 from pm  where p_id=30369)
)
select cast(t.column_value.extract('//text()') as varchar2(40)) val
  from sample_data,
  table( xmlsequence( xmltype(
    '<x><x>' || replace(pm_id, '#@#', '</x><x>') || '</x></x>'
  ).extract('//x/*'))) t)


texts:
I have been trying to link my mssql database to Django. When I run the Django server I get the error below. For normal query (out of Django), it works fine, however, the Django doesnt even connect. The weird thing is that when I change the driver to 'SQL SERVER' instead of 'ODBC Driver 17 for SQL Server'. I get no error. However, I need 'ODBC Driver 17 for SQL Server' because it has a lot of features that I need.

File "C:\ProgramData\Anaconda3\lib\site-packages\sql_server\pyodbc\base.py", line 307, in get_new_connection
    timeout=timeout)
django.db.utils.InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'EMEA\\kmoh'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [28000]
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'EMEA\\kmoh'. (18456); [28000] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0)")

Windows 10
django-pyodbc-azure: version 2.1
Django 2.1
Pyodbc: 4.0.25

my Django settings.py looks like this:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'HOST': 'xxx', #has been hide for this post
        'PORT': '1433',
        'NAME': 'BBL_Result',
        #'ODBC_DSN': 'BBL'
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
            'extra_params': "Persist Security Info=FALSE;server=xxx", #has been hide for this post

        },
    },
}


texts: Strange Postgres Hstore syntax with curly braces {key=value}

texts:
I'm using the following program https://sourceforge.net/projects/jailer/ to extract a subset of data from my database with correct associations following my foreign key constraints. The program outputs raw SQL insert and update statements than can be loaded into my development database - all very nice and handy. However - I recently started using Hstore in postgres and now I'm encountering some strange syntax in the SQL files. If I have a table witch has a column of type store then update and insert statements are outputted like so:

Update public.some_table set brand='Ford', model_name='Focus', additional_attrs={engine_size=1.6, weight=1100}, created_at=timestamp '2013-11-05 17:21:38.667085', updated_at=timestamp '2013-11-05 17:21:38.667085' Where id=246747;

Witch gives me the following error, when I try to execute it:

ERROR:  syntax error at or near "{"
LINE 1: ...rand='Ford', model_name='Focus', additional_attrs={engine_si...
                                                             ^

********** Error **********

ERROR: syntax error at or near "{"
SQL state: 42601
Character: 81

As far as I know from postgresql hstore {key=value} is not a valid syntax so my questions are:

Is it in fact valid syntax?
Why would the program output syntax like this, is it likely a bug in the program or something more related to postgres e.g. the postgres jdbc driver?
Any suggestions on how I might overcome this problem?


texts: TSQL - Take a single row query and transpose based on field value

texts:
I'm trying to create a fact table with data I was given. We have a monthly goal number, but we want to be able to figure out how many weeks are in that month and divide the goal number by the weeks in the month.
My query is very straight forward. Get the goal number for the month, calculate the number of weeks in the month, and divide that by the goal number to get the weekly goal number.
SELECT SUM(Goal) AS Goal
     , (DATEPART(dd, EOMONTH(GETDATE())) / 7) + CASE WHEN (DATEPART(dd, EOMONTH(GETDATE())) % 7) > 0 THEN 1 ELSE 0 END AS NumberOfWeeks
     , SUM(Goal) / (DATEPART(dd, EOMONTH(GETDATE())) / 7) + CASE WHEN (DATEPART(dd, EOMONTH(GETDATE())) % 7) > 0 THEN 1 ELSE 0 END AS WeeklyGoal
  FROM reporting.SalesGoals
 WHERE MonthNumber = MONTH(GETDATE())
   AND Year = YEAR(GETDATE())
   AND Branch IS NOT NULL

This will return this
Goal        NumberOfWeeks       WeeklyGoal
5000        5                   1000

What I'm looking to do is produce rows based on the number weeks. Hopefully to get this
Goal        NumberOfWeeks       WeeklyGoal
5000        1                   1000
5000        2                   1000
5000        3                   1000
5000        4                   1000
5000        5                   1000


texts:
I need help formatting numbers in a specific way.

If a number has 3 digits or less, I would like it to remain the same.

If a number has more than three digits, I would like it place a decimal after the third decimal.

For instance:

123 --> Stays the same
1234 --> 123.4
1234567 --> 123.4567

I am using SQL Server and want to UPDATE the value in the table. The value is stored as a numeric.


texts: SQL Server stored procedure validity checking

texts: How can I check the validity of a stored procedure programmatically? Meaning that if no errors are found it will execute. How can I detect things like invalid tables names in "from" or "join" clauses?


texts:
I have a java application that:

Reads a .bak file from a windows fileshare and restores it in SQL Server on a remote windows server
Does various modifications on the database restored from the .bak file in step 1
Backup the resulting database to a temporary .bak file (called somename.bak_tmp) on a windows fileshare
Renames somename.bak_tmp to somename.bak (in the same directory on the same windows fileshare)

All the above steps works fine sometimes... I modified the rename code at some point which basically does this:

executeSql("master..xp_cmdShell 'rename "+tmpFile+" "+newName+"'");

to use the full path as newName. As a result the renaming failed. But now I cannot remove the somename.bak_tmp from the fileshare. Actually I cannot even see it (only the parent folder that contains it).

I have tried to delete the restored database from SQL Server but I still cannot delete the folder containing somename.bak_tmp. I get an Access Denied if I try to open it. If I right click the folder it says this on the owner tab:

  Unable to display current owner.

And I cannot change the owner to Administrator even though I am logged in as administrator. Any ideas on what have happened to the folder during the failed rename and how to remove it?


texts: How to find column which causing "Conversion failed" error

texts:
I've run into quite a lot of issues specially when it comes to conversion errors:
convert(datetime, [fieldname]) or SELECT CAST([field2] AS int).
Imagine a large select statement with 30+ conversion or cast functions, or a complex view with conversions, or even an insert statement on a field with wrong data type. I get so much headache trying to exclude fields one by one to find the troublemaker.
Is there a way to find out which field and/or value is causing the issue? How do people troubleshoot these without having to go through an arduous process of going field by field? Is there a log or information message that can help?


texts: Get list of DB2 databases and tables through linked server

texts:
Is there a way to get a list of the databases on a DB2 server through a linked server query?  I tried using an openquery to run "list database directory" but that didn't work.

I am attempting to do this because I do not have direct access to the DB2 server.

Please note that I already have the linked server set up.


texts: Does CREATE/REFRESH MATERIALIZED VIEW in PostgreSQL use the parallel planner?

texts:
I am wondering whether CREATE (with data) or REFRESH MATERIALIZED VIEW uses the parallel planner? When I EXPLAIN ANALYZE the query by itself (any query with parallel potential works here) without creating the materialized view, it shows that said query is parallel aware, and executing said query by itself shows multiple processors active using standard linux tools like top. Conversely, executing the query within a REFRESH or CREATE materialized view wrapper, does not show multiple processes, and using EXPLAIN on CREATE MATERIALIZED VIEW shows a query that is not parallel aware. Is this the intentional behavior and why? I was not able to find any information on this. (PostgreSQL 10.5 on CentOS 7)

EDIT: Checked that this behavior is the same when using CREATE TABLE AS and SELECT INTO. Is there any way to write a table using parallelism?

EDIT 2: Is there any work-around I can use to CREATE TABLE using parallelism without first outputting the results of query to a file? Such as somehow splitting the query into several hundred equal parts defined by a aggregation variable in the data, followed by a specified number of cores in parts getting INSERT'ed in parallel?


texts: SQLite error: foreign key mismatch when I try to make an INSERT command

texts:
I'm trying to create an Associative Entity (N:N) in SQLite like this:

  [ Pet ---< VaccinePet >--- Vaccine ]

And, I have the follow code of my associative entity:

CREATE TABLE  VACINAPET (
        vp_date TEXT NOT NULL,
        vp_is_applied INTEGER DEFAULT 0,
        fk_pet INTEGER,
        fk_vaccine INTEGER,
        FOREIGN KEY (fk_pet) REFERENCES pet (pet_id),
        FOREIGN KEY (fk_vaccine) REFERENCES vaccine (vaccine_id),
        PRIMARY KEY (fk_pet, fk_vaccine)
);

BUT, I'm getting an error:

  foreign key mismatch - "VACCINEPET" referencing "vaccine": INSERT INTO
  VACCINEPET (vp_date, vp_is_applied, fk_pet, fk_vaccine) VALUES
  ('23/05/2018', 0, 1, 1);

When I try to use the INSERT command:

  INSERT INTO VACINAPET (vp_data, vp_is_aplicada, fk_pet, fk_vacina)
  VALUES ('23/05/2018', 0, 1, 1);

What could be wrong? I'm not so good in database... :(

MASTER DETAIL: I have data in Pet table and Vaccine table, they are not empty


texts:
I have transactional replication with updatable subscribers set up in SQL Server 2008. It has 3 subscribers and 1 publisher. I had to setup replication again due to some errors related to the database and the application which uses the database. However, now I run into issues when I try updating a section in my application. It does not go through the with update and gives the following error:

  Execption Message: Updateable Subscriptions: Rows do not match between
  Publisher and Subscriber. Run the Distribution Agent to refresh rows
  at the Subscriber. Updateable Subscriptions: Rolling back transaction.
  The transaction ended in the trigger. The batch has been aborted.
  Exception Triggered by Method: OnError

The update statement obviously doesn't go through. However, when I try it the second time it works. Replication is working. Everything seems to be replication.

Can anyone explain why this error would occur and how I can resolve this issue. I would really appreciate the help!...


texts: How to remotely connect SQL Server 2016. Since we have 2012 and 2016 in same server

texts:
Issue

I am not able to connect to a SQL Server 2016 instance on a remote system/server.

My Existing Environment

Windows Server 2012
SQL Server 2012 and 2016 installed on same server

I tried to enable a firewall rule for SQL Server 2016 with port 1433. But it is not working.

Same thing I tried with SQL Server 2012 it is all working fine.

Settings I Have Tried

Enabled remote connection in SQL server.
Created Firewall rule to browse SQL Server.
Enabled TCP Port.
Enabled default port 1433.

Error Message I Am Receiving

It would be great if anybody could help me with this.


texts: Query to get the number of errors reported by PostgreSQL

texts:
Is there any internal table recording the number of errors produced by Postgres?

I was expecting something like:

select
    database, count(*)
from
    pg_errors
where
    type='error' and date > (now() - INTERVAL '1 HOUR')


texts: Set TERMOUT OFF is not working when I am using sqlplus in command prompt?

texts:
I am using the sql statement listed below in my spool sql script, and I am executing this script from sqlplus. But in terminal query output is still coming and I am using sqlplus in command prompt. But when I tried, directly launching sqlplus.exe and using this script I am not getting query output in terminal.

Can someone please help on how to stop terminal output when I launch sqlplus from command prompt?

SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 83
SET TRIMSPOOL ON
SET WRAP ON
SET RECSEP OFF

SPOOL '&1'

SELECT
    DATA_OUT_DATA
FROM
    STAGE.DATA_TABLE
ORDER BY
    DATA_UNIQ_ID,DATA_INV_ID, DATA_SEQ_NO;

SPOOL OFF

SET PAGESIZE 24
SET LINESIZE 80
SET FEEDBACK ON
SET TERMOUT ON


texts: Creating multiple windows login on a single databae

texts:
I am able to create a window login on a database I created. This windows authentication login is based on a windows user that I created earlier, but I don't seem to know how to login into SQL server with the windows authentication login I created. Am I doing things the right way or is there something else that I need to do? Please help me guys.
I want to have two windows authentication logins into a single database with both logins having different privileges


texts: Grant users privilege to use QUERYTRACEON 9481

texts:
Microsoft suggests using OPTION (QUERYTRACEON 9481) for queries that have "degraded" performance against SQL Server 2014 databases running under compatibility level 120.

In my situation, "degraded" means going from a few seconds to several minutes or more. Performance wise, 9481 works extremely well; however, regular users get a nice error instead of query results (running SSRS reports):

User 'xxxxx' does not have permission to run DBCC TRACEON.

The documentation indicates that users must be members of the sysadmin role to run DBCC TRACEON.

Are there any workarounds to allow non-sysadmins to run queries that use this hint?

I'd rather not drop the compatibility level back down to 110 because the majority of our queries are acceptable or working better.


texts: fast way to migrate existing mongo cluster data to new one

texts:
Currently, I have a existing mongo3.0 cluster(using WiredTiger storage engine and directoryPerDB is true) as below settings:

3 x shards(rs01, rs02, rs03)
2 x mongos
3 x mongo cfg

All the databases/collections in the 3 shards are disabled sharded.
and for example, there are below databases in the 3 shards.

in rs01: db01, db03
in rs02: db02, db05
in rs03: db04

Now, I bought new hardware and want to setup a new cluster for better performance. According to the usage of all databases, I need to change the positions as below:

in new rs01: db02, db04
in new rs02: db01
in new rs03: db05, db03

All the databases data are several TB size(about 25TB in total.). I tried to use mongo dump/export is slow. It may take several weeks to transfer all databases to new cluster?

Although in the old cluster, all nodes config directoryPerDB: true, I tried to create an empty database named db01 in new rs02 and then insert a document in db01.co01, then copy the local directory /var/lib/mongodb/db01 in old rs01 to new rs02. But it doesn't work.

Is there any faster way to do this? Say, copying the local files to new cluster. If I can make the process fast, I'll announce the downtime of mongo service within 1 week and do it.

@Stennie mentioned about Migrate a Sharded Cluster to Different Hardware. However, this looks like node to node data path copy.
In my case the position of all databases are redesigned for better performance, so I cannot do a node to node file copy.


texts: Postgres Plus Cloud Database on AWS for write heavy social game?

texts:
I'm one of four founders of a big social gaming iPhone app in Sweden. We now suffer from severe database server issues so I'm turning to you guys to get some help.
First of all, let me get this out there: I'm not the tech guy in charge of the servers but I assist with research.

We got about 8k querys/5 sec to our PostgreSQL DB and we're growing fast. The DB server is already behaving stressed out. It's a 8 core VPS with 16 gb ram.

We're looking into the possibilities of using a Postgres Plus Cloud Database solution from EnterpriseDB running on AWS. (http://www.enterprisedb.com/cloud-database)

1) Anyone have experience in this matter?

2) We've also been looking into the options to use a dedicated server but we need the possibilities to scale the server up to 20-30 times. Is this relatively easy with EnterpriseDB's solution?

3) If i got it all right: EnterpriseDB offer a cluster solution which theoretically would solve our problems. Am I right?


texts: Remove duplicate records with timestamps

texts:
I have a table in my DB where I keep track of user data. So I have rows like that:

(row_id,user_id, stat1,stat2,..., statN, timestamp)

Now I want to remove all duplicate records, where user_id and stat columns are the same, but only between other rows with different data. So if I have

(row_id, user_id, stat1, stat2, stat3, timestamp)
(1,1,1,0,1,1000)
(2,1,1,1,1,2000)
(3,1,1,1,1,3000)
(4,1,1,0,1,4000)

It should became

(1,1,1,0,1,1000)
(2,1,1,1,1,2000)
(4,1,1,0,1,4000)

And not

(1,1,1,0,1,1000)
(2,1,1,1,1,2000)

So I want to save record progression but remove meaningless duplicates.


texts: MySQL Workbench Forward Engineer from only the current diagram

texts:
I want to create a test database from a huge schema in MySQL, but when I try to do Forward Engineer from a Diagram with about 6 tables, it try to create all the tables, views and routines groups that I have in the schema.

I'm dealing with more than 100 tables and it is really annoying to have to select the tables of the diagram you are working on.

Is there an automatic way to do that instead of selecting one by one the tables I want?


texts: MySQL Workbench - Create new server instance error

texts:
When attempting to create a new server instance I get an error at the 'Test Settings' step of the creation wizard.

The error:

What check_admin_commands/local
Operation failed: /usr/local/mysql/support-files/mysql.server start is invalid

I'm using MAMP and new to OSX so I'm not sure what the problem is here. I can't find the directory /usr on OSX, should I be changing Workbench's configuration to look elsewhere for mysql.server?


texts: How to improve performance of frequently updated table in mysql?

texts:
my table structure::

CREATE TABLE `gps_device_data`(
  `imei_no` bigint(15) NOT NULL DEFAULT '0',
  `ioelementpriority` int(1) DEFAULT NULL,
  `longitude` int(15) DEFAULT NULL,
  `latitude` int(15) DEFAULT NULL,
  `speed` tinyint(3) unsigned DEFAULT NULL,
  `iovalue` varchar(254) DEFAULT NULL,
  `odom` bigint(20) DEFAULT NULL,
  `alarm_category_realtime_id` varchar(70) DEFAULT NULL,
  `angle` int(4) DEFAULT NULL,
  `altitude` int(4) DEFAULT NULL,
  `satellites` int(4) DEFAULT NULL,
  `data_received_time` bigint(20) NOT NULL DEFAULT '0',
  `continue_data_received_time` bigint(20) NOT NULL DEFAULT '0',
  `movement` int(2) DEFAULT NULL,
  `eventsource` int(4) DEFAULT NULL,
  `data_location` varchar(250) DEFAULT NULL,
  `data_poi` varchar(100) DEFAULT NULL,
  `data_validity` enum('valid','invalid') DEFAULT NULL,
  `createdby` varchar(10) DEFAULT NULL,
  `createddate` datetime DEFAULT NULL,
  `modifiedby` varchar(10) DEFAULT NULL,
  `modifieddate` datetime DEFAULT NULL,
  `deletedby` varchar(10) DEFAULT NULL,
  `deleteddate` datetime DEFAULT NULL,
  PRIMARY KEY (`imei_no`,`data_received_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

query that takes too much time to generate report for vehicle.

select  odom,latitude/10000000 as latitude,longitude/10000000 as longitude,
       imei_no,iovalue,data_received_time,continue_data_received_time,
        data_location
    from  gps_device_data
    where  imei_no IN (7781112889,70433357641,356173061134123,
               865190010626029,354188046466178,356307040285917,863354010100132,
               863354010100107,8511139808,4209501721,9974052815,356307044188414,
               5252563256,7709111078,7043335764,2121212121,7043333691,
               7043333684,7043338378,100345,356307048930860,356307040328584,
               358899052926188,7621013796,7043132917,7043312765,9016102493,
               ...
               7820046194,8585858585,559862234897,123456789001,78000000021,
               2220002220,12345678911,863771023760608,863771023758834,
               9898787854,140507978,140908877,140507779,356173061103755,
               7485961230,454545454,147852390,1203203695,358174067325903
                      )
      and  data_received_time >= 1430463600000
      and  data_received_time <= 1446274800000
    Order by  imei_no,data_received_time;

my explain plan

+----+-------------+-----------------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | gps_device_data | range | PRIMARY       | PRIMARY | 16      | NULL | 629357 | Using where |
+----+-------------+-----------------+-------+---------------+---------+---------+------+--------+-------------+

what my project do is track the vehicle get its information and display that information to specific owner of vehicle.

questions:

Are there any alternative queries  which gives me performance than above specified query?

Is it ok to use a Composite key with IN block?

my select query takes 25.93 seconds to execute.  If my above select query takes 2 or 3 seconds to execute its good form me. How can I reduce the time for the select query?

I have 10 millions rows in the gps_device_data table.


texts: Why does Oracle not support an autonumber data type?

texts:
I know that Oracle implements autonumber functionality using sequences and triggers, but why doesn't it have a built-in autonumber data type as do most relational DBMSs? There are two specific parts to my question:

I know that sequences with triggers are generally more flexible than what autonumber fields in other DBMSs provide. However, what are the some particularly superior features of Oracle's implementation?
Considering that autonumber for primary key attributes is such a universal use case, what is the disadvantage of implementing it that keeps Oracle from adding such built-in functionality? After all, they can still keep sequences and triggers with any additional features they might provide.

I know that this question might be difficult, since it is not a "how-to" question but rather a question asking background behind Oracle's design decisions for their DBMS. However, I feel there is much to be learnt in understanding Oracle's thinking in this matter.


texts: Is using a Trigger to Insert a new Record into a View seen as bad practice?

texts:
So I was recently having a conversation with one of my new work colleagues and we got onto the topic of how best to INSERT data into multiple tables.

Their teaching taught them to not use a TRIGGER to split an INSERT onto a VIEW into multiple INSERTS into their respective tables, is this seen as bad practice?

Following is how I attempted to solve the INSERT issue without creation of duplicate values in the base tables.

VIEW

CREATE VIEW [Person].[vPeopleAllInformation] AS
SELECT pt.Type_Name AS Type,
p.Firstname,
p.Lastname,
e.Email AS Email,
ph.Number AS PhoneNumber
FROM [Person].[People] p
FULL JOIN [Person].[Person_Types] pt ON p.Person_Type = pt.Person_Type_ID
FULL JOIN [Person].[Person_Emails] e ON e.Person_ID = p.Person_ID
FULL JOIN [Person].[Person_Phones] ph ON ph.Person_ID = p.Person_ID

TRIGGER

CREATE TRIGGER [Person].[tPerson_Email_Phone] ON [Person].[vPeopleAllInformation]
INSTEAD OF INSERT
AS
BEGIN

INSERT INTO [Person].[People] (Person_Type, Firstname, Lastname)
SELECT p.Person_Type_ID, i.Firstname, i.Lastname FROM INSERTED i
INNER JOIN [Person].[Person_Types] p ON i.Type = p.Type_Name
WHERE NOT EXISTS (SELECT 1 FROM [Person].[People] WHERE Firstname = i.Firstname AND Lastname = i.Lastname);

INSERT INTO [Person].[Person_Emails] (Person_ID, Email)
SELECT p.Person_ID, i.Email FROM INSERTED i
INNER JOIN [Person].[People] p ON p.FullName = CONCAT(i.Lastname, ', ', i.Firstname)
WHERE NOT EXISTS (SELECT 1 FROM [Person].[Person_Emails] WHERE Email = i.Email);

INSERT INTO [Person].[Person_Phones] (Person_ID, Number)
SELECT p.Person_ID, i.PhoneNumber FROM INSERTED i
INNER JOIN [Person].[People] p ON p.FullName = CONCAT(i.Lastname, ', ', i.Firstname)
WHERE NOT EXISTS (SELECT 1 FROM [Person].[Person_Phones] WHERE Number = i.PhoneNumber);

END

Is this the right way of doing it or should I indeed split it into multiple queries when the user attempts to add another Record to the view?


texts: Extracting files from partitioned database

texts:
The company I work for performs services for a couple of hundred customers, all have their own database with identical schemas.

We are planning to merge all of these databases into one, partitioned, database as it will make reporting across all customers easier.

Each table in the database has a customer_id column, so this seems to be the best data item to partition on.

As I understand it, following the merge each partition will have its own mdf and ldf files and these will contain all the data for one customer.

One reason that we have kept the databases separate up to now is that it makes copying a database to our own development environments for debugging or testing new functionality quick as each database is relatively small (100GB typically).

My question is, given that I can get the files for a single customer from the database server is it possible to copy them to my development SQL Server and create a database for that single customer?


texts: Improving the performance of this select query

texts:
i have two tables  TABLE 1 and TABLE 2

And i am running this select query to fetch the data , it takes long time to run

SELECT TABLE1.* , TABLE2.COL1 , TABLE2.COL4 FROM TABLE1, TABLE2
WHERE TABLE1.COL1 IN('1','TEXT','HO TED')
AND TABLE1.COL2= 1800 AND TABLE2.COL1 = 10519198
AND TABLE1.COL3>= 100 AND TABLE1.COL4= TABLE2.col2
AND TABLE2.COL3=('TEXT')

Any suggestions ? so that i could improve performance of this select query .

Does too many AND affects my performance ?

Thanks in advance


texts: accelerating a SQL quadruple self-join with a complex alternation in the WHERE clause

texts:
The following Sqlite query has a triple self-join on table t:

SELECT "update_variable",
    lhs_acc.name_suffix || ":" || rhs_var.name_suffix,
    op.span,
    op.path
FROM t op
JOIN t lhs_acc ON (lhs_acc.path GLOB op.path || "?*")
JOIN t rhs_acc ON (rhs_acc.path GLOB op.path || "?*")
JOIN t rhs_var ON (rhs_var.path GLOB op.path || "?*")
WHERE (op.name_prefix = "assignment"
    AND lhs_acc.name_prefix = "assignment_lhs_identifier"
    AND rhs_acc.name_prefix = "assignment_rhs_atom"
    AND rhs_var.name_prefix = "assignment_rhs_atom"
    AND rhs_acc.name_suffix != rhs_var.name_suffix
    AND lhs_acc.name_suffix = rhs_acc.name_suffix)
OR (op.name_prefix = "augmented_assignment"
    AND lhs_acc.name_prefix = "assignment_lhs_identifier"
    AND rhs_acc.name_prefix = "assignment_rhs_atom"
    AND rhs_var.name_prefix = "assignment_rhs_atom")
OR (op.name_prefix = "method_call"
    AND rhs_acc.name_prefix = "method_call_name"
    AND lhs_acc.name_prefix = "method_call_object"
    AND rhs_var.name_prefix = "call_argument"
    AND rhs_acc.name_suffix != rhs_var.name_suffix
    AND rhs_acc.name_suffix REGEXP "(append|extend|insert|add|update)$")
GROUP BY op.span,
        lhs_acc.name,
        rhs_var.name

It is far too slow for my application. I can accelerate it enough by restricting each instance of t to the lines I am interested in, which somehow duplicates the WHERE conditions. So, the FROM clause becomes:

FROM
(SELECT *
FROM t
WHERE t.name_prefix IN ("assignment",
                        "augmented_assignment",
                        "method_call")) op
JOIN
(SELECT *
FROM t
WHERE t.name_prefix IN ("assignment_lhs_identifier",
                        "method_call_object") ) lhs_acc ON (lhs_acc.path GLOB op.path || "?*")
JOIN
(SELECT *
FROM t
WHERE t.name_prefix IN ("assignment_rhs_atom",
                        "method_call_name") ) rhs_acc ON (rhs_acc.path GLOB op.path || "?*")
JOIN
(SELECT *
FROM t
WHERE t.name_prefix IN ("assignment_rhs_atom",
                        "call_argument") ) rhs_var ON (rhs_var.path GLOB op.path || "?*")

Is there some technique which would lead to a less verbose / ugly / redundant query ?

Edit: The test duplication (but not the ugliness) can be avoided as follows:

SELECT "update_variable",
    lhs_acc.name_suffix || ":" || rhs_var.name_suffix,
    op.span,
    op.path
FROM
(SELECT *
FROM t
WHERE t.name_prefix IN ("assignment",
                        "augmented_assignment",
                        "method_call")) op
JOIN t lhs_acc ON (lhs_acc.name_prefix = (CASE op.name_prefix
                                            WHEN "method_call" THEN "method_call_object"
                                            ELSE "assignment_lhs_identifier"
                                        END)
                AND (lhs_acc.path GLOB op.path || "?*"))
JOIN t rhs_acc ON (rhs_acc.name_prefix = (CASE op.name_prefix
                                            WHEN "method_call" THEN "method_call_name"
                                            ELSE "assignment_rhs_atom"
                                        END)
                AND (rhs_acc.path GLOB op.path || "?*"))
JOIN t rhs_var ON (rhs_var.name_prefix = (CASE op.name_prefix
                                            WHEN "method_call" THEN "call_argument"
                                            ELSE "assignment_rhs_atom"
                                        END)
                AND (rhs_var.path GLOB op.path || "?*"))
WHERE op.name_prefix = "augmented_assignment"
OR (op.name_prefix = "assignment"
    AND lhs_acc.name_suffix = rhs_acc.name_suffix
    AND rhs_acc.name_suffix != rhs_var.name_suffix)
OR (op.name_prefix = "method_call"
    AND rhs_acc.name_suffix REGEXP "append|extend|insert|add|update)$"
    AND rhs_acc.name_suffix != rhs_var.name_suffix)
GROUP BY op.span,
        lhs_acc.name,
        rhs_var.name


texts: joining the where clause to select statement of two different tables

texts:
How can I properly structure the below query to make it work? I would like to have the Ref_CD = MBR_ID_TYPE_ID to be my select statement on the beginning of it.

select MBR_ID_TYP_ID || '-' || (
    select ref_desc
    from ref
    where ref_nm='memberIDType'
        and REF_CD = MBR_ID_TYP_ID) as "Member Type", id.*
from mbr_id id
where mbr_id = 0989879;


texts: What is the impact of DROP SCHEMA on concurrent read only workloads in PostgreSQL?

texts:
I have an ELT process that deals with a pretty small amount of data. I would like to have 1 schema called prod with the results of the previous ELT run and 1 schema called dev where I will perform the transformations of the current ELT run. Once the current ELT run is complete I would like to make the switch by running something along the lines of:

start transaction;
drop schema prod;
alter schema dev rename to prod;
end transaction;

All queries issued outside of the ELT process are read only. How would MVCC handle this? What would happen to read only queries that were running when the drop schema command is issued? What would happen to read only queries issued after the drop schema command? What about after the end of the transaction?


texts: A constant scan takes either 0 seconds or 2-3 minutes

texts:
A query like the one below that is guaranteed not to return any rows, takes anything from 0 to 160 seconds on one of our servers:

select col1, col2, col3
from tab1
where 0 = 1

Two weeks ago, this happened six times in a 48 hr interval. Last week the same query took ~0 seconds. I have logs of our application's SQLs but haven't found any suspects yet. Besides, I thought a top 0/where 0=1 type query never hit the data pages, so it should be resistant to row/page/table-level data locks? The schema is not touched by any (known) SQLs.

Since the problem is not consistent, and the server is under very heavy load I'd like to understand the theory behind what is happening before attaching SQL profiler. Other queries run without problems during these delays. A known problem in the application is a high number of dynamically created SQL queries - around 200k unique queries of 850k total (logged) queries over a period of 48 hrs, can this cause problems like this?

The server is running SQL Server 2005 standard edition, 96 GB RAM, disks on SAN and 4 CPUs/16 cores. Database files and filegroups are well optimized and shouldn't be a problem (but we're looking into this separately).

Any pointers where to look is greatly appreciated.

Edit: Perfect! Replayed the query to add the execution plan, and it took 1min 35secs. Here's the execution plan and screenshot showing the query duration:

Edit 2: statistics time details for a second run. Seems to be consistently slow right now, so we'll be attaching profiler and perfmon:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 97402 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.


texts:
I'm working on an enterprise dashboard as a web application. The users can insert new operations every day, the data is stored and visualized in the web app dashboard as they are entered.
I designed the database as a star schema, because it seemed to be the only way to model the database, but I'm not sure it's good because the database here is transactional, but dimensional models are analytical.
Is it possible to use a dimensional model as a web app database, that has read, and write frequently?


texts:
I took hot backup (dump) from my Master MySQL DB with below command

  mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A  > ~/dump.sql

Master Version: MySQL-server-5.5.41-1.el6.x86_64

At the time of restoration on slave we are getting this error, have I done something wrong.

  [root@Slave ~]$ mysql -u root -p < dump.sql
  Enter password:
  ERROR 1449 (HY000) at line 150536: The user specified as a definer ('lipl_ga_app'@'%') does not exist

Slave Version: mysql-community-server-5.6.28-2.el6.x86_64

The DB is huge and we don't want to start the restoration process again.

@ Nawaz Sohail I have taken backup of mysql user from below command

  mysqldump -u root -p mysql user > user_table_dump.sql

Restored

  mysql -u root -p mysql < user_table_dump.sql


texts:
I've transaction replication set up and running fine but today my Log Reader agent started having this warning :

  sql the job was stopped prior to completion by shutdown sequence 0 . The job was invoked by start sequence 0.

I've looked through all the SQL/agent/Windows logs and I can't see anything special. Has anyone ever faced the same issue?

PS: This server has log shipping running for 30dbs and also has other 3 transactional publications if it helps. This is the only job that is giving me this error. We are running on SQL Server 2008.

Thank you


texts:
I have the most simple of questions:

I've got a Microsoft SQL Server with two instances, default and a named instance called Instance1.

The default instance is running on static TCP/IP port 1433 and the named instance is running on static TCP/IP port 5555.

I'm trying to create an alias to Instance1 and went to SQL Server Configuration Manager and under SQL Server Native Client 11.0 Configuration (32 Bit) I created an alias as follows:

Aliasname: TEST1
Portnumber: 5555
Server: <ServerName>\Instance1

Then, directly on the SQL Server box I tried the following from a cmd:

sqlcmd -S TEST1 ... and I'm connected

Then I did:

1> select name from sys.sysdatabases
2> go

and I get the DBs that exist on the default instance, rather than Instance1.

I've tried changing the alias port to a random port, anything at all, and it will always connect to the default instance, I don't get it.

There are no MSSQLSvc/ SPNs at all registered for this server, but I can't imagine that having something to do with it.

Basically, no idea what's going on here.

This is a Windows Server 2016 and SQL Server 2016.


texts: django-admin: postgresql trigger function

texts:
I have this table in django admin site the (Student_enrollment_record),
if i select an student and update/save the Section field

and it will search the Section field on what i select on the table  (Student_enrollment_record(Section)) in table (subject_section_teacher(Section))

and the result will automatically save here.

do i have to use an postgres trigger to solve my problem or should i use django_signals? Please provide an solution for my problem.


texts: Microsoft Certification - 70-764 Administering a SQL Database Infrastructure

texts:
Firstly, I apologise if this is not necessarily the best place to post such a question.

I am currently preparing for my 70-764 exam. I have purchased the following book:

70-764

I have a few questions regarding the book and its resources:

Previously, I have purchased other Microsoft Certification books and have normally got a PDF version, along with the printed book. Is this not the case anymore? Am I missing a disk or something?
The book has many URLS for supporting resources, however, these dont appear to be valid any longer. For example, early on within the book, there is a URL given for a list of useful links which accompany the book, I cannot find these online at all.
Practice tests also used to come with the book, however this does not appear to be the case any longer. Is this by design? Or am I just missing something here?

Many thanks in advance for any assistance.


texts: Permissions for runtime user on all objects in schema (including future objects)

texts:
In PostgreSQL 10 - when using an "owner user" (login role owning a schema and all tables, not used by the application at runtime) and a "runtime" user I can GRANT Select/Update/Delete permissions on all existing tables in the schema to the runtime user:

GRANT SELECT ON ALL TABLES IN SCHEMA owner TO runtime;

But this only applies to existing objects and not new tables which might get created later on.

Is there a way to avoid granting on all new objects by way of inheriting roles or schema permissions or similar?


texts:
I'm trying to create a carry-forward wallet system.

Recharge part
I'm developing an app that has the carry forwarding token wallet concept. However, when an user recharge their wallet, an amount of the token will be credited to their wallet with an expiration date (suppose expiration date will be 1 month).
If the user recharges their wallet compared to the amount of the new token before the expiration of their current token, it will add to the amount of the old token and extend the expiration date as well.
In between that when ever an action performs that related to token deduction,code will check the expiration date before deduct the token.

I've already designed a database structure. Please have a look.

Order Table to manage all token transactions.

token_order
+----+---------+------------+-----------+-----------+----------------+-------------+------------+--------+------------+-------------+
| id | user_id | order_date | sub_total | tax_total | discount_total | grand_total | ip_address | status | created_at | modified_at |
+----+---------+------------+-----------+-----------+----------------+-------------+------------+--------+------------+-------------+
| 1  | 20      | 2020-04-20 | 1000      | 100       | null           | 1100        | 127.0.0.1  | 1      | 2020-04-20 | 2020-04-20  |
+----+---------+------------+-----------+-----------+----------------+-------------+------------+--------+------------+-------------+

Token_order and token_order_details are linked to fetch order information.

token_order_details
+----+----------------+-------------+-----------+------------+----------------+-----------------+---------------+-------------+--------+------------+-------------+
| id | token_order_id | token_price | token_qty | tax_amount | tax_percentage | discount_amount | purchase_date | expiry_date | status | created_at | modified_at |
+----+----------------+-------------+-----------+------------+----------------+-----------------+---------------+-------------+--------+------------+-------------+
| 2  | 1              | 10          | 100       | 100        | 10             | null            | 2020-04-20    | 2020-05-20  | 1      | 2020-04-20 | 2020-04-20  |
+----+----------------+-------------+-----------+------------+----------------+-----------------+---------------+-------------+--------+------------+-------------+

This table connects the wallet with respective user.

token_wallet_details
+----+-----------+-----------+-----------+-------------------------+------------+--------------------+------------+-------------+
| id | wallet_id | action_id | token_qty | transaction_description | ip_address | transaction_status | created_at | modified_at |
+----+-----------+-----------+-----------+-------------------------+------------+--------------------+------------+-------------+
| 1  | 1         | 1         | 1.5       | dummy description       | 127.0.0.1  | success            | 2020-04-20 | 2020-04-20  |
+----+-----------+-----------+-----------+-------------------------+------------+--------------------+------------+-------------+

This table holds the token deduction details.

token_wallet_details
+----+-----------+-----------+-----------+-------------------------+------------+--------------------+------------+-------------+
| id | wallet_id | action_id | token_qty | transaction_description | ip_address | transaction_status | created_at | modified_at |
+----+-----------+-----------+-----------+-------------------------+------------+--------------------+------------+-------------+
| 1  | 1         | 1         | 1.5       | dummy description       | 127.0.0.1  | success            | 2020-04-20 | 2020-04-20  |
+----+-----------+-----------+-----------+-------------------------+------------+--------------------+------------+-------------+

Am I going to the right direction with this tables and their fields to handle these things mentioned above?


texts: How can I enable audit on selected accounts only to know where they logged on from?

texts:
I want to audit certain DB accounts (the ones used for applications), to know which machine the user is logging on from.

To save space, I don't want to enable this audit for every account in the database.

Is this possible? If so, how can I do this?

Having the an option to make this fire only when an invalid password was entered (which causes the account to get locked), would be a bonus.


texts: Syncing writes and not deletes - mongoDB

texts:
Is it possible to have a secondary instance of mongoDB which syncs all the write operations to the database and not the delete operation?

I'm looking for this solution so that I have a backup even if some humane error occurs and data is lost.


texts: Can I use a Synonym to point to an object in a schema using another schema?

texts:
I don't have too much experience in Oracle, so I am sorry if this is a simple question.

I have to move many objects in the database from one schema to another.
I am trying to update all dependencies as well, but just to be in the safe side I would like to leave behind a link.

Example:

Right now I have in my Schema 1 (S1) a MyTable.
Users reference it like:

S1.MyTable
I would like to move MyTable to Schema 2 (S2)

S2.MyTable
I would like my users to be able to still use MyTable as they used to.

S1.MyTable /MyTable is really in S2, but users can access it with S1/

I have been reading about Synonyms and it seems you can create public ones so users can reference an object directly, like:

MyTable

But I need them to be able to write S1.MyTable

Can I use synonyms for this?


texts: Oracle - How do I grant a user permission to create and change passwords for other users (like a security admin)

texts: I have a user that is a security admin in the I.t. dept. I need to grant his Oracle user account privileges to be able to create new users, change passwords and change membership in other groups we created. This is basically a security admin that I do not want to give full sysdba/dba privileges to. That would be overkill. What exactly do I grant and how?


texts: MariaDB replication from master to slave

texts:
After doing some research around this topic, I should be attempting this task soon. However, I have two questions going around my head - could do with an experienced professional answering them.

I am using MariaDB 10.3 and the latest version of Centos 7.

Q1. From the MySQL console, when granting user permission rights, you would grant these for the Private IP and not the Public IP?

Q2. Currently, root permissions are disabled for remote sign-in. Is it a good practise to create a slave user instead and don’t touch root user permissions?

Thank you all!


texts: Query strategies using SQL Server 2016 system-versioned temporal tables for Slowly-Changing Dimensions

texts:
When using a system-versioned temporal table (new in SQL Server 2016), what are the query authoring and performance implications when this feature is used to handle Slowly Changing Dimensions in a large relational data warehouse?

For example, assume I have a 100,000-row Customer dimension with a Postal Code column and a multi-billion-row Sales fact table with a CustomerID foreign key column. And assume I want to query "Total 2014 sales by customer's postal code".  Simplified DDL is like this (omitting many columns for clarity):

CREATE TABLE Customer
(
    CustomerID int identity (1,1) NOT NULL PRIMARY KEY CLUSTERED,
    PostalCode varchar(50) NOT NULL,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON);

CREATE TABLE Sale
(
    SaleId int identity(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    SaleDateTime datetime2 NOT NULL,
    CustomerId int NOT NULL FOREIGN KEY REFERENCES Customer(CustomerID),
    SaleAmount decimal(10,2) NOT NULL
);

Where it gets interesting is that customers may have moved during the year so the same customer may have different postal codes. And it's even remotely possible that a customer moved away and then moved back, meaning there could be multiple history records for the same customer with the same postal code!  My query of "sales by postal code" should be able to calculate correct results regardless of how customers' postal codes change over time.

I understand how to use temporal tables to query the customer dimension alone (e.g. SELECT * FROM Customer FOR SYSTEM_TIME FROM '2014-1-1' TO '2015-1-1') but I'm not sure how to most accurately and efficiently join to the fact table.

Is this how I should query it?

SELECT c.PostalCode, sum(s.SaleAmount) SaleAmount
FROM Customer c FOR SYSTEM_TIME FROM '2014-1-1' TO '2015-1-1'
    JOIN Sale s ON s.CustomerId = c.CustomerId
WHERE s.SaleDateTime >= '2014-1-1' AND s.SaleDateTime < '2015-1-1'
    AND c.SysStartTime >= s.SaleDateTime
    AND c.SysEndTime < s.SaleDateTime
GROUP BY c.PostalCode

And what are the performance considerations that I should watch out for when making queries like this?


texts: PostgreSQL crashes during count row query

texts:
Currently I have only two tables - small one that works fine and another presumably large one.
When I try to find out row count in pgAdmin I get the following error:

The application has lost the database connection:
⁃ If the connection was idle it may have been forcibly disconnected.
⁃ The application server or database server may have been restarted.
⁃ The user session may have timed out.
Do you want to continue and establish a new session?

I also checked the Postgres server logs:

2020-03-30 03:06:06.764 MSK [37356] LOG:  server process (PID 30712) was terminated by signal 9: Killed: 9
2020-03-30 03:06:06.764 MSK [37356] DETAIL:  Failed process was running: SELECT COUNT(*)::text FROM ios.task_info;
2020-03-30 03:06:06.764 MSK [37356] LOG:  terminating any other active server processes
2020-03-30 03:06:06.765 MSK [30810] WARNING:  terminating connection because of crash of another server process
2020-03-30 03:06:06.765 MSK [30810] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2020-03-30 03:06:06.765 MSK [30810] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2020-03-30 03:06:06.765 MSK [30709] WARNING:  terminating connection because of crash of another server process
2020-03-30 03:06:06.765 MSK [30709] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2020-03-30 03:06:06.765 MSK [30709] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2020-03-30 03:06:06.767 MSK [37356] LOG:  all server processes terminated; reinitializing
2020-03-30 03:06:06.776 MSK [30816] LOG:  database system was interrupted; last known up at 2020-03-30 02:51:14 MSK
2020-03-30 03:06:06.818 MSK [30817] FATAL:  the database system is in recovery mode
2020-03-30 03:06:06.853 MSK [30816] LOG:  database system was not properly shut down; automatic recovery in progress
2020-03-30 03:06:06.855 MSK [30816] LOG:  redo starts at 0/4F4DE850
2020-03-30 03:06:06.855 MSK [30816] LOG:  invalid record length at 0/4F4DE888: wanted 24, got 0
2020-03-30 03:06:06.855 MSK [30816] LOG:  redo done at 0/4F4DE850
2020-03-30 03:06:06.862 MSK [37356] LOG:  database system is ready to accept connections

I run everything on my OS X Catalina laptop and use PostgreSQL 12 EnterpriseDB.

Just in case if it is important - I created the large table sending several large CSV COPY queries in my Python script.
What can be the reason of these errors?


texts: SQL Server Resource Pool and Workload Groups

texts:
Below is the scenario:

Scenario 1:
I got two workload groups assigned in the same resource pool with MAX CPU 20%. In the event of CPU bottleneck, the limit will come into effect with 10% each workload group?

Scenario 2:
I got two workload groups assigned with two different resource pool with MAX CPU 10% each. In the event of CPU bottleneck, each resource pool/workload group will get 10% each?

Is it both scenario will get the same resource allocation?


texts: Query runs in parallel but it is showing as blocked by itself

texts:
I have a query which runs in parallel with a MAXDOP of 8.
When i looked at sp_who2 i see that the same session ID is being repeated several times(>8) with different connection id.

I used the below query and see that the wait type is still CXCONSUMER wait type.
But i see 130 different exec_context id.

SELECT
    dot.session_id,
    dot.exec_context_id,
    dot.task_state,
    dowt.wait_type,
    dowt.wait_duration_ms,
    dowt.blocking_session_id,
    dowt.resource_description
FROM sys.dm_os_tasks dot
    LEFT JOIN sys.dm_os_waiting_tasks dowt
        ON  dowt.exec_context_id = dot.exec_context_id
            AND dowt.session_id = dot.session_id
WHERE dot.session_id = 96
ORDER BY exec_context_id;

If the query is running in parallel does it have different spid when i the load gets distributed to different worker thread.?

Looked at Kendra Little's write up.
Very helpful one.

I used the query to see different schedulers used in my parallel processing query.

I see that same shcheduler_id/worker address is used to multiple times.Is it normal.?

Also my MAXDOP is 4,but i see 5 different scheduler_ids which is weird.


texts: Oracle Newbie Question - How can one run selects from different tables but show output on separate rows

texts:
E.g. Table 1 Japan Customers
     Table 2 USA Sales People
     Table 3 HK Engineers

There are selects from the 3 tables each. However, can I run all 3 selects within a single/overall select statement and output 1 result set, so one can see
each row labeled based on the table name? all these tables don't have similar columns but share a common id column

Col1        Col2       Col3...
Japan
USA
HK


texts: TSQL Convert FLOAT to STRING Truncation/Rounding Issue

texts:
SQL Server 2008 R2

Why when converting a FLOAT to a string (Varchar / Nvarchar) using Cast or Convert, does a rounding or truncation occur?

Using CAST

DECLARE @floatVal FLOAT = 4.76758527755737

SELECT
    CAST(@floatVal AS VARCHAR(20)) 'VARCHAR',
    CAST(@floatVal AS NVARCHAR(20)) 'NVARCHAR'

Output

VARCHAR NVARCHAR
4.76759 4.76759

Using CONVERT:

DECLARE @floatVal FLOAT = 4.76758527755737

SELECT
    CONVERT(VARCHAR(20), @floatVal) 'VARCHAR',
    CONVERT(NVARCHAR(20), @floatVal) 'NVARCHAR'

Output

VARCHAR NVARCHAR
4.76759 4.76759

With STR however, there's no truncation/rounding

DECLARE @floatVal FLOAT = 4.76758527755737

SELECT
    LTRIM(STR(@floatVal, 17, 14)) 'STR'

Output

STR
4.76758527755737

Thanks in advance


texts: Trying to add permission to a Role is failing

texts:
I am trying to add a role into Create and Restore permissions and After researching online, it sounds like it should be as easy as using the GRANT statement. So far so good. However, my simple grant statement which looks like following:

GRANT CREATE, Restore ON dbo TO EventmanagerAdmin

Throws following error :

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

I wish I had more information to debug but that is all it says. Some more details in environment and background:

I am using SSMS 2012
The role does exist with Execute permission on this DB
I used script from List all permissions for a given role? to confirm that what permissions does the role have

Is there anything that I might be missing?


texts:
I'm trying to take ownership of the "public" schema in the database I'm the owner of (but not superuser). I'm running alter schema public owner to username, where username is my user name. I'm getting the following error: ERROR: must be owner of schema public.
Is it possible for me as a non-superuser database owner to take ownership of a schema, (or generally any object) in my database?


texts:
When I try to stop the acfs I get the following error (all volumes was already disabled with voldisable -G diskgroup_name volume_name)
server03:/>acfsload stop

server03:/>(ucfgacfsctl): ACFS-11038: failed to unconfigure kernel extension oracleacfs.ext, errno 16 (Device busy)
ACFS-9119: oracleacfs.ext driver failed to unload.

When I check processes 'ps -ef | grep acfs' few acfs related processes are seen
server03:/>fuser -f /dev/ofsctl
/dev/ofsctl: 7012562

server03:/>
servers03:/>ps -ef|grep 7012562
oracle 7012562 1 0 Aug 09 - 4:14 asm_acfs_+ASM3

I guess it is holding the acfsload stop... How can I stop this process?


texts: Shell: How to time a script running in SQLPlus and kill it after x amount of minutes?

texts:
I have a little tool which automatically runs a series of SQL scripts when and outputs to .XLS when an appropriate request is made. However, some SQL scripts need to be ran on a live database, and I don't want them to run for over 5 minutes.

Is there a way to - within my shell script - time the script's run time and kill it after 5 minutes?

Thanks in advance.


texts: Can a single database be excluded from an AG backup?

texts: I want to backup all the databases in an availability group except one. Using the @AvailabilityGroups option in Ola's Maintenance Solution scripts, can a single database in an Availability Group be excluded from a backup?


texts:
I'm designing a database for opening times and created this solution.

The specifications of the database which will be a MySQL are, that a location have standard opening times in a week and can have special opening times for example on christmas or something like that.

So if I want the opening times of an entry I would first search in 'special_opening' for the given day and if it given no results back, I would load the data from 'opening'.

Is this a legit way to realize the database?
Are there better ways to do it?


texts: Configure MariaDB to log row deletion ONLY

texts:
I'm investigating some rows/tables that intermittently go missing on a Magento site, and of course there's no entries in the error log. (I suspect Magento queries/rules or human error might be the culprit) I know I can enable the General log, but I don't want to log EVERYTHING as this site processes a huge amount of sales every day and the log would be massive.

I'm hoping someone might know of a way to configure either the general log or log table to record only DELETE commands. Open to any suggestions you might have of a better way to go about this.


texts: Upgrading Microsoft SQL 2014 and Always On AG Configurations

texts:
I'm getting ready to perform an upgrade from SQL 2014 to 2017 on two servers that are configured for Always On. The concern on the upgrade is how to break Always On to stop replication and to keep any sort of systems from failing over during the upgrade.

Microsoft's documentation for upgrading says to

  Remove automatic failover on all synchronous-commit replicas

But there's no mention of a best practice on how to do this. Microsoft's documentation talks about disabling it in the Server Configuration Manager, then bringing back on once the upgrade is done prior to starting on the second one. But I also see the documentation mention removing the secondary from the availability group.

It looks like the upgrade process is straightforward, but the Always On portion is the confusing side. Can anyone recommend a best practice on this that's worked for them while maintaining data integrity?

For reference, this is the link from Microsoft I've been working from for the upgrade.


texts: Backup / Export data from MySQL 5.5 attachments table keeps failing!

texts:
Can anyone please help! - I have a large table in a MySQL 5.5 database. It is a table which holds a mixture of blobs/binary data and just data rows with links to file paths. It has just over a million rows.

I am having desperate problems in getting the data out of this table to migrate it to another server.

I have tried all sorts - mysqldump (with and without -quick), dumping the results of a query via the command line. Using a MySQL admin tool (Navicat) to open and export the data to file, CSV, or do a data transfer (line by line) to another DB and/or another server but all to no avail.

When trying to use the DB admin tool (Navicat), it gets to approx 250k records and then fails with an "Out of memory" error. I am not able to get any error messages from the other processes I have tried, but they seem to fall over at approximately the same number of records.

I have tried playing with the MySQL memory variables (buffer size, log file size, etc) and this does seem to have an effect on where the export stops (currently I have actually made it worse).

Also - max_allowed_packet is set to something ridiculously large as I am aware this can be a problem too.

I am really shooting in the dark, and I keep going round and round trying the same things and getting no further. Can anyone give me any specific guidance, or recommend perhaps any tools which I might be able to use to extract this data out??

Thanks in hope and advance!

A little more information below - following some questions and advice:

The size of the table I am trying to dump - it is difficult to say, but the sql dump gets to 27gb when the mysqldump dies. It could be approximately 4 times that in total.

I have tried running the following mysqldump command:

mysqldump --single-transaction --quick mydatabase attachments --password=abc123 -u root > d:\attachments.sql

And this gives the error:

  mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table attachments at row: 251249 – Mayb2Moro 4 hours ago

The server has 8gb RAM, Some of the relevant settings copied below. It is an INNODB database/table.

innodb_buffer_pool_size=3000M
innodb_log_file_size=1113M
max_allowed_packet=2024M
query_cache_size=52M
tmp_table_size=500M
myisam_sort_buffer_size=50M


texts: Get the data from the table in the form of fieldname and fieldvalue in SQL Server

texts: Is it possible to get the data and column name as a key-value pattern like the table tblemplyee shown below?


texts:
I have a dimension with a column called 'Type'. There is a list of default 'Types' in this dimension.

However, this 'Type' column can be overriden. i.e. each user can replace the default value with their own value. So in theory there could be a different type for each user. Or multiple users could be using the same type.

What is the best way of doing this? Add a 'Type_User_Value' column in the fact table? Or how would I do this in the dimension?

Thanks!


texts: Deciding what columns to use for partitioning

texts:
I am having trouble deciding what fields of my table to base the partition. Currently there are a total of 8 indexes on the table out of a total of 14 columns. The problem is the indexes are taking up too much space 2 to 1 compared to the data and we need to reduce the size of the DB, and at least maintain current speed. 3 of the indexes have a significantly lower cardinality (in the teens) than the rest (in the millions). There are over 40 million rows in the table.

The index data type looks something like this:

datetime
smallint(6) (low cardinality)
varchar(20) (low cardinality)
int(15)
int(15)
varchar(100)
varchar30
char(1) (low cardinality)

and a few others. All the indexes are non unique and of type BTREE.

I created a test table and removed the indexes with the lowest cardinality but the queries take a bit longer. I'd like suggestions on two aspects:

What indexes should be kept and, which should be removed.
What columns should my partitioning be based on.

The table definition:

Create Table: CREATE TABLE `mytable` (
  `date` datetime DEFAULT NULL,
  `time` smallint(6) DEFAULT NULL,
  `source` varchar(20) DEFAULT NULL,
  `b` int(15) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `ttype` char(1) DEFAULT NULL,
  `ag` char(1) DEFAULT NULL,
  `direction` char(1) DEFAULT NULL,
  `mode` char(1) DEFAULT NULL,
  `username` varchar(30) DEFAULT NULL,
  `name` varchar(15) DEFAULT NULL,
  `authentication` char(1) DEFAULT NULL,
  `authenticated_id` varchar(75) DEFAULT NULL,
  `status` char(1) DEFAULT NULL,
  KEY `date` (`date`),
  KEY `time` (`time`),
  KEY `b` (`b`),
  KEY `name` (`name`),
  KEY `username` (`username`),
  KEY `source` (`source`),
  KEY `b2` (`b`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts: Distributed Availability groups & Listeners

texts:
I'm currently doing my first setup with Distributed Availability groups. This is currently a POC setup but will normally be implemented soon. I have 4 nodes, 2 nodes located together and 2 other also together but with some distance between them.

I set up the Distributed AG with the automatic seeding and everything is fine so far. The database got synchronised to the secondary Availability group without any issues.

Now comes the actual question: I have created two listener for my AG's (Primary & Secondary), but I can't create a listener for my Distributed AG (Not much information on it). This poses the problem that my application will have to be Listener aware...

Is there a way to create a listener for the Distributed AG, through which the application will always be able to connect even if we failover the distributed availability group?


texts: Connecting to SQL Express Server 2017 with SQL Server Authentication Remotely

texts:
I have two systems that are on the same network. I am able to ping back and forth between these systems with their IP address and with their system names. I have enabled the TCP port 1433 inbound and outbound on both systems. I have enabled TCP in the SQL Server Configuration Manager.

I have created an SQL Server Authentication and have the server on mixed login.

I am able to connect to the server from the system it is running on by using the following:

Server name: DESKTOP\SQLEXPRESS
Authentication: SQL Server Authentication
Login: admin

However, if I change the Server name to tcp:DESKTOP,1433\SQLEXPRESS I am unable to connect. If I change the Authentication mode back to Windows I am able to connect using the above server name.

This is my first time using SQL and I am very confused why I am having this issue. I would like to be able to connect to this server through TCP so that I may connect to it remotely with another system on the same network. Any information or links would be greatly appreciated.


texts: Calculate total running time of pods using two date/timestamp columns

texts:
I am currently storing all the pods in PostgreSQL db. I track each pods creation and deletion through created_time and deleted_time column. When pod is not deleted, its deleted_time columns remains null.

SqlFiddle can be found here: SQLFiddle

The schema is given below for quick reference:

create table pods (
  name text primary key,
  created_time timestamp without time zone,
  deleted_time timestamp without time zone default null
);

insert into pods (name, created_time)
values
('pod-1', '02-01-2020 01:00:00'),
('pod-2', '02-01-2020 02:00:00');

update pods set deleted_time = '02-01-2020 03:00:00' where name = 'pod-1';

The challenges while calculating the running time are as follows:

Handling NULL values in deleted_time column (pod is still running)
Clipping created_time and deleted_time to a user-specific range (start and end time).

So I came up with following query which calculates the usage hours, but it's a bit slow when you are dealing with million of pods in a selected date range.

-- end_time = '2020-02-01 04:00:00'
-- start_time = '2020-02-01 02:00:00'

with model as (
  select
    name,
    case when created_time < '2020-02-01 02:00:00' then '2020-02-01 02:00:00' else created_time end as created_time,
    coalesce(
      case when deleted_time >= created_time and deleted_time <= '2020-02-01 04:00:00' then deleted_time else null end,
      '2020-02-01 04:00:00'
    ) as deleted_time,
    extract(
      epoch from(
          coalesce(
          case when deleted_time >= created_time and deleted_time <= '2020-02-01 04:00:00' then deleted_time else null end, '2020-02-01 04:00:00'
          ) - case when created_time < '2020-02-01 02:00:00' then '2020-02-01 02:00:00' else created_time end)) / 3600::float as usage_hours
  from pods
  where created_time <= '2020-02-01 04:00:00' and
        (deleted_time is null or deleted_time >= '2020-02-01 02:00:00')
)
select sum(usage_hours) from model; -- should return 3.

So here are my questions:

I would like to know if there is any more efficient way to calculate these statistics?
Is it even possible to calculate it with the given schema?


texts:
I have this delete statement

delete from dbo.HighSchoolGradeLevel where Id=@P0

This is the execution plan

https://www.brentozar.com/pastetheplan/?id=H1qzvl3nW

when the application is busy and there are many deletes coming, the performance become so bad, My questions are.

How to make this delete perform better?

why the granted memory is so high, it steals the memory from the buffer pool ?

Note: there is on delete cascade on the 2 tables Highschoolcourse and highschoolgrade.

CREATE TABLE [dbo].[HighSchoolCourseGrade](
    [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [TermSubTypeLkupId] [int] NULL,
    [Grade] [varchar](50) NULL,
    [GradePoint] [float] NULL,
    [CourseId] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [UpdatedDate] [datetime] NOT NULL,
    [CourseUnits] [float] NULL,
 CONSTRAINT [PK_HighSchoolCourseGrade] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [ix_HighSchoolCourseGrade_CourseId_includes]    Script Date: 10/11/2017 4:33:38 PM ******/
CREATE NONCLUSTERED INDEX [ix_HighSchoolCourseGrade_CourseId_includes] ON [dbo].[HighSchoolCourseGrade]
(
    [CourseId] ASC
)
INCLUDE (   [Id],
    [TermSubTypeLkupId],
    [Grade],
    [GradePoint],
    [CreatedDate],
    [UpdatedDate],
    [CourseUnits]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[HighSchoolCourseGrade]  WITH CHECK ADD  CONSTRAINT [FK_HighSchoolCourseGrade_CourseId] FOREIGN KEY([CourseId])
REFERENCES [dbo].[HighSchoolCourse] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[HighSchoolCourseGrade] CHECK CONSTRAINT [FK_HighSchoolCourseGrade_CourseId]
GO

CREATE TABLE [dbo].[HighSchoolCourse](
    [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [CourseName] [varchar](200) NULL,
    [CourseTypeLkupId] [int] NULL,
    [EntryTypeLkupId] [int] NULL,
    [SubjectKey] [int] NULL,
    [GradeLevelId] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [UpdatedDate] [datetime] NOT NULL,
    [TotalCourseUnit] [float] NULL,
    [TotalGradePoint] [float] NULL,
 CONSTRAINT [PK_HighSchoolCourse] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [ix_HighSchoolCourse_GradeLevelId_includes]    Script Date: 10/11/2017 4:32:58 PM ******/
CREATE NONCLUSTERED INDEX [ix_HighSchoolCourse_GradeLevelId_includes] ON [dbo].[HighSchoolCourse]
(
    [GradeLevelId] ASC,
    [SubjectKey] ASC
)
INCLUDE (   [Id],
    [CourseName],
    [CourseTypeLkupId],
    [EntryTypeLkupId],
    [CreatedDate],
    [UpdatedDate],
    [TotalCourseUnit],
    [TotalGradePoint]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[HighSchoolCourse]  WITH CHECK ADD  CONSTRAINT [FK_HighSchoolCourse_GradeLevelId] FOREIGN KEY([GradeLevelId])
REFERENCES [dbo].[HighSchoolGradeLevel] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[HighSchoolCourse] CHECK CONSTRAINT [FK_HighSchoolCourse_GradeLevelId]
GO

This is from sp_blitzcahce

Update

I find this Delete in the Query Store , there were 3 plans and I forced SQL to use different plan. I am wondering Why SQL choose to use the most expensive execution plan?


texts: Is it okay to use MySQL Workbench with MariaDB?

texts: Workbench 8 gives me a warning when connecting to MariaDB, but it appears to work fine. Since MariaDB is based on MySQL, would it be a problem to keep using it?


texts: to_number, ORA-01722: invalid number error

texts:
I have a batch job which is trying to load data from a .dat file into an Oracle table.

Below is the script within our control file:

LOAD DATA
INTO TABLE TABLE_1
WHEN (1:1)="1"
(
A_id                       POSITION(0002:0005)    CHAR             ,
c_code                     POSITION(0006:0008)    CHAR             ,
status                     POSITION(0009:0009)    CHAR             ,
FTC                        POSITION(0011:0024)    char
                           "to_number(:FTC,'S99,999,999.99')"      ,
TCER                       POSITION(0029:0038)    decimal external ,
ATC                        POSITION(0039:0052)    char
                           "to_number(:ATC,'S99,999,999.99')"      ,
FTR                        POSITION(0053:0066)    char
                           "to_number(:FTR,'S99,999,999.99')"      ,
TRER                       POSITION(0067:0080)    decimal external ,
ATR                        POSITION(0081:0094)    char
                           "to_number(:ATR,'S99,999,999.99')"
)

Records in the file:

1GSXBGBPNR        384.66    0.60801391        632.00       -384.66    0.60801391       -632.00
1GSXBEURNR         10.42    0.70548409         14.00          0.00    0.00000000          0.00
1GSXBEURRE        136.01    0.70533631        192.00          0.00    0.00000000          0.00
1GSXBUSDRE         95.91    0.90472597        106.00        -95.91    0.90472597       -106.00

While the job runs we get the ORA-01722: invalid number error  error on the column FTC. How can I fix it?


texts: how to judge sql performance through these two explain?

texts:
If I give you 2 explain results from MySQL,how do you know which query is better?
the first sql explain
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest2
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 700100
        Extra:
1 row in set (0.00 sec)

and the second sql explain
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: b.id
         rows: 1
        Extra:
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: sbtest2
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 700100
        Extra: Using index
3 rows in set (1.46 sec)

I think I will choose the first query because there are "a lot of" things to do in the second explain,but actually the second query is much faster than the first query , so my question is if I don't see the response time,how to judge their performance through explain ?
the first query's Duration: 4.43704325
select * from sbtest.sbtest2
order by id
limit 700000,100;

after restart mysql to clean cache and the second one is Duration: 2.08889400 (faster)
select * from sbtest.sbtest2 a
inner join
(select id from sbtest.sbtest2  order by id limit 700000,100 ) as b
on a.id=b.id;

table structure is above
mysql> show create table sbtest.sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=5265529 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
1 row in set (0.00 sec)

can anybody help me ?

additional
mysql> explain select id from sbtest.sbtest2  order by id limit 700000,100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest2
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 700100
        Extra: Using index
1 row in set (0.00 sec)

time is 4.28456200


texts: Database Migration Failed from MSSQL server 2014 to Oracle 19c

texts:
We have SQL server 2014 and we are trying to migrating the database to Oracle 19C eventhough after following the proper steps mentioned in Oracle and followed by some youtube videos with proper admin rights.
https://www.oracle.com/database/technologies/migrating-sql-to-oracle.html
**oracle.dbtools.migration.workbench.core.MigrationLogResourceBundle WARNING oracle.dbtools.migration.workbench.core.ui.FullMigrateTask Error:ORA-06550: line 1, column 14: PLS-00201: identifier 'SS2K5ALLPLATFORM.STAGECAPTURE' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored FAILED : Database Migration : FAILED
oracle.dbtools.migration.workbench.core.logging.LogInfo@5f96e7a0**
This is the above error we found in the migrationlog.xml file, we also followed the process mentioned in the below blog also https://community.oracle.com/tech/developers/discussion/4333777/sql-developer-migrate-sql-server-to-oracle-fails-in-generation-phase How to resolve the above issue?


texts:
I have a database with 5 .ndf (secondary) datafiles on SQL Server 2008 R2. (Each of these datafiles is assigned to a separate filegroup.) I want to know what data is there in those files. Is it possible? I know there are full text indexes in these filegroups (and since there is only one file per filegroup maybe the sole purpose of creating this many files were to place full text indexes in different filegroups, I don't know). I want to make sure there is no data other than full text indexes.
Thank you


texts: do MYSQL views occupy physical space? Or in other words, is a MYSQL database limited to a certain number of views?


texts: Can I ignore queries about extendedproperty?

texts:
I want to reuse the structure of a database in a new project. So using SSMS I use Generate Script and in settings I set to generate schema only.

In the result there are too many queries about extendedproperty. Is there any important data or settings in these queries or I can completely igonre them and only reuse queries on database tables and fields to start a similar project?

And if they ar unnecessary how can I use "generate script" without them?

sample query:

EXEC sys.sp_dropextendedproperty @name=N'CollatingOrder' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'billboard', @level2type=N'COLUMN',@level2name=N'url'


texts: Standard data types for email address, telephone number , first name, rixh text editor, etc

texts:
I want to build a database for our system using Sql Server 2008 R2. but i need to know what are the best data types for storing these info:-

Email address
Telephone number with extension, such as +44 12345 ext 123
First Name
Rich Text editor. we have a field named "Position Role" or "Main duties", which stores a full description of each employee's roles.

Can anyone adivce on this please?
Thanks


texts:
I'm lost in the sea with the following example of decomposition without loss of dependencies...

Let be the dependencies F= {A → BC, C → AD} R is decomposed in R¹(A,B,C) & R²(A,D) I want to know if such a decomposition is without loss of dependencies and without loss of information.

I understand that it is without loss of information but why is it without loss of dependencies?

It is said so because

F¹={A → BC, C → A, C → B} because C → A and C → B since
decomposition of A → BC. I don't understand why C → A and C →
B
F²={A → D} but why? A → BC, C → AD therefore A → AD, no?

C → D shouldn't be lost  but isn't in F¹ nor in F². Why is it important to say that?

C → A (from F¹) and A → D (from F²) therefore, C → D

The result which I don't understand at all is that it is without loss of dependencies...


texts: The best way to log access to SSRS reports

texts:
We have a number of SSRS reports created using BIDS 2008 on SQL Server 2008 r2. The business wants us to log access to these reports. For example who and when run the Report A.

What is the best way of achieving that? Is it suitable if we use SQL Auditing feauture for  that?

Thank you.


texts: sp_publication_validation procedure doesn't seem to work properly

texts:
I am trying to use sp_publication_validation stored procedure to validate that my replication setup is working correctly.  This procedure is pretty straightforward and is described here.

I am running this command:

EXEC sp_publication_validation @publication = 'MyPublication'
    ,@rowcount_only = 1     -- (1 - rowcnt only, 2 - rowcnt and checksum)
    ,@full_or_fast = 0      -- (0 - full count using COUNT(*), 1 - use sysindexes.rows, 2 - conditional)

Initially everything seems to be ok and the results indicate that all of the tables match.  Next I go over to the subscriber and manually delete a few rows from some of the tables.  I manually verify that the row counts in my tables are now different between the publisher and the subscriber.  Finally I run the sp_publication_validation procedure again and .... it says that everything is still OK.  This is wrong!  I also tried to return both rowcnt and checksum and it still doesn't detect the fact that there are differences between the publisher and subscriber.

I appreciate any ideas.

Thank you!


texts:
Because internet is not always available i cannot just use one central database.
So there are is a DB in every location.
I need to have them all in sync, but there can be a delay of 30 minutes or maybe even 1 hour between syncing.
I use mysql 5.5.62 but i could probably upgrade if needed.
I could also have a server that every location can reach in a different place. Would that help if one DB goes down?
What would be my best strategy?
Thank you very much


texts: Changing data directory for postgres on CentOS

texts:
I've got a postgres 9.2.18 installation on CentOS which used the standard installation path. Now I realised that on this particular partition, there isn't sufficient hard-disk space for some bigger queries.

The default data_directory is on:

/var/lib/pgsql/data

$ df -h /var/lib/pgsql
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-root   50G   39G   12G  77% /

So, I'd like to move the data directory to my home repository (it's a test-DB only for myself)

$ df -h /home/mlu
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-home  395G  171G  224G  44% /home

For doing so, I've tried:

1) Copying postgres data using rsync:
sudo rsync -av /var/lib/pgsql /home/mlu/postgres-data

2) Addressing the new path within postgresql.conf:
sudo -u postgres nano /var/lib/pgsql/data/postgresql.conf
  data_directory = '/home/mlu/postgres-data/pgsql/'

3) starting postgres:
sudo systemctl start postgresql

Starting postgres service doesn't works now, because of the new data_directory path within the postgresql.conf. (It works, if I comment out the directory path again though)

EDIT: Ok, I've figured out, that it's a permission conflict. But shouldn't rsync just have copied the correct permission as well?

sudo service postgresql.service status
Redirecting to /bin/systemctl status  postgresql.service.service
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Tue 2018-02-13 18:34:54 CET; 21s ago
  Process: 27416 ExecStop=/usr/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
  Process: 27484 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=1/FAILURE)
  Process: 27473 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 27290 (code=exited, status=0/SUCCESS)

Feb 13 18:34:53 axonlu-ws026.mappuls.int systemd[1]: Starting PostgreSQL database server...
Feb 13 18:34:53 axonlu-ws026.mappuls.int pg_ctl[27484]: FATAL:  could not read permissions of directory "/home/mlu/postgres-data/pgsql": Permission denied
Feb 13 18:34:54 axonlu-ws026.mappuls.int pg_ctl[27484]: pg_ctl: could not start server
Feb 13 18:34:54 axonlu-ws026.mappuls.int pg_ctl[27484]: Examine the log output.
Feb 13 18:34:54 axonlu-ws026.mappuls.int systemd[1]: postgresql.service: control process exited, code=exited status=1
Feb 13 18:34:54 axonlu-ws026.mappuls.int systemd[1]: Failed to start PostgreSQL database server.
Feb 13 18:34:54 axonlu-ws026.mappuls.int systemd[1]: Unit postgresql.service entered failed state.
Feb 13 18:34:54 axonlu-ws026.mappuls.int systemd[1]: postgresql.service failed.

Not sure why I still get a permission denied, since postgres should have all necessary rights as owner of the folder:

$ ls -l
total 4
drwxrwxrwx. 6 postgres postgres 4096 Aug  3  2017 pgsq


texts: When using DBCC INPUTBUFFER to find query associated with spid, is there a way to show variable values of that query?

texts:
I have been troubleshooting an issue within a database that has some serious blocking issues.

I found the command DBCC INPUTBUFFER a while back that allows me to see the query run for that spid.  I have used this to see what is running and causing the issue.

What I am confused about is that the query that DBCC INPUTBUFFER shows does not include the variables associated with the query.  For example, if the query joined on the @ID of a table, the provided query by DBCC INPUTBUFFER does not show the @ID value.

I am just wondering if there is a way to see what variable is associated with the query.  And/or if maybe this is not the norm and normally DBCC INPUTBUFFER should show you the variable associated with the query.

If it is the latter, then my problem probably rests in the fact that a variable is not being provided and it is just querying a set of rather enormous tables and taking an eternity.


texts: does last_insert_id() returns correct result when writes from multiple sessions?

texts:
I have a table in which there is a combination of 4 columns that will act as a unique key.

Instead of using a composite primary key, I want to keep an id column as the primary key. This field is on auto_increment. The insert to the database are done through a C interface. When a row is inserted, I want to know the id it was assigned. LAST_INSERT_ID() can be used to retrieve that, but there will be multiple sessions inserting in the table. Will LAST_INSERT_ID() return the correct value in that case?

I have gone through this post: Is MySql's LAST_INSERT_ID() function guaranteed to be correct?
It says that id of first inserted row is returned when multiple rows are inserted.

When multiple sessions write to the same table but a single session writes only one row at a time, is it possible to retrieve the correct value of the id generated? Will the Isolation Level also matter?


texts: Is Oracle's dbms_utility.get_hash_value algorithm documented do reverse engineered anywhere?

texts:
I'm working with an existing database where passwords are hashed like this:

DBMS_UTILITY.GET_HASH_VALUE(the_username || the_password, 1000000000, 1073741824)

I know that this is bad for various reasons (not a proper cryptographically safe hash and the algorithm oracle uses may change between versions). But that database exists and it's unlikely that we can get them to change this since they have lots of users and cannot simply switch to a proper hashing algorithm without having each of them re-enter or reset their password.

Now I need to use this database for LDAP authentication (based on OpenLDAP). Unfortunately there is no way to make OpenLDAP pass the plaintext password to the database (where I could then simply call the hashing function on the plaintext password). However, modifying the LDAP client would be possible, so if I could create the hash there everything would be fine.

I'd rather avoid connecting to the Oracle database to do this since that would require the Oracle client, database login data, etc. on that machine, too. Therefore I wonder if there is any information about the algorithm used by GET_HASH_VALUE() available. I couldn't find anything on Google but maybe it's buried between all the somewhat unrelated results... In case the algorithm changed recently, the server version is 11.2.0.3.0.


texts:
I have a simply Products data table with 100k records (just generates some random data):

set nocount on

create table dbo.temp_Products
(
    [ProductID] int,
    [Type] tinyint,
    [Price] float,
    [Weight] float
)

declare @rowcnt int = 0
while (@rowcnt <= 100000)
begin
    insert into dbo.temp_Products
    select @rowcnt, 1+rand()*4, 1+rand()*100, 1+rand()*10

    set @rowcnt = @rowcnt + 1
end

And a scalar value function which does a simple logical calculation:

create function dbo.usvf_CalculateShipping
(
    @PricePerKG float = 1,
    @Type tinyint,
    @WeightInKG float = 1
)
returns float
as
begin

    return  /*get the appropriate factor to apply*/
            case
                when @Type = 1 then 0.1
                when @Type = 2 then 0.2
                when @Type = 3 then 0.35
                when @Type = 4 then 0.43
            end * @PricePerKG * @WeightInKG

end

However, when I run a query to call the SVF the resulting performance is impaired relative to the query calling the logic inline.  The executed SQL statements and results are as follows:

SQL:

select ProductID, case
                when [Type] = 1 then 0.1
                when [Type] = 2 then 0.2
                when [Type] = 3 then 0.35
                when [Type] = 4 then 0.43
            end *Price*[Weight] from temp_Products
where [Weight] between 2 and 8

select ProductID, dbo.usvf_CalculateShipping(Price, [Type], [Weight]) from temp_Products
where [Weight] between 2 and 8

Results:

Table 'temp_Products'. Scan count 1, logical reads 390, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 109 ms,  elapsed time = 103 ms.

(59938 row(s) affected)
Table 'temp_Products'. Scan count 1, logical reads 390, physical reads 0, read-    ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads     0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 609 ms,  elapsed time = 629 ms.

But if I get the execution plan of running both queries at once, the relative cost of each query is 50%, even though the inline SVF is considerably slower.

Why does SQL server run ths inline SVF query slower - both in CPU and elapsed time?
By centralising some simple logic it appears I impede performance through code reuse.  My problem is I need to call this SVF in numerous places without duplicating logic. If SQL server always impedes performance when using SVF why would I ever use them and is there a different solution?