texts: PostgreSQL 8.3: Slow GROUP BY on large table

texts:
I have a table with about 10 million records. I want to do a simple group by, but it's using a sequential scan and is slow...

select run_id, count(*) from result group by run_id;

I have an index defined on the run_id column.

How can I speed this up?


texts:
I create user C##ADMIN in database for manage pdbs .

sqlplsqlplus sys/rootpass@172.17.0.2/orcl as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 26 15:17:09 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create user C##ADMIN identified by adminpass container=all ;

User created.

SQL> GRANT CREATE SESSION, SYSOPER,PDB_DBA,DBA, CREATE PLUGGABLE DATABASE TO c##admin CONTAINER=ALL ;

Grant succeeded.

SQL>

My problem is C##ADMIN user can not list pdbs !

sqlplus C##admin/adminpass@172.17.0.2/orcl

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 26 15:20:14 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sat May 26 2018 15:17:32 +04:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select pdb_name,status from cdb_pdbs;

no rows selected

SQL>

Which permission is forgotten?


texts: Change SELECT DISTINCT to UPDATE DISTINCT

texts:
How can I modify my SELECT DISTINCT query to an UPDATE DISTINCT query?

It is important that it only updates the distinct records, as there are multiple records associated with each [Finance_Project_Number] (due to CRUD operations). I only want to update a single record, as this will simply set in motion a different process of validating the data etc.

If there are multiple records collapsed into a single row as a result of the DISTINCT, any of these can be updated - it does not matter.

When I run my select query, I get a result of: 6 982:

SELECT DISTINCT
    [Finance_Project_Number]
FROM [InterfaceInfor].[dbo].[ProjectMaster]
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM [IMS].[dbo].[THEOPTION]
        WHERE
            [InterfaceInfor].[dbo].[ProjectMaster].[Finance_Project_Number] =
            [IMS].[dbo].[THEOPTION].[NAME]
    );

Here is my attempt at converting my query to a DISTINCT UPDATE query, but this updates 15 353 records:

UPDATE [InterfaceInfor].[dbo].[ProjectMaster]
SET
    [Processing_Result_Text] = 'UNIQUE',
    [Processing_Result] = 0
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM [IMS].[dbo].[THEOPTION]
        WHERE
            [InterfaceInfor].[dbo].[ProjectMaster].[Finance_Project_Number] =
            [IMS].[dbo].[THEOPTION].[NAME]
    );


texts: timelines and history files PostgreSQL 9.1

texts:
What is the purpose of timelines, history files, and the setting 'recovery_target_timeline' in recovery.conf?

The vague understanding I have from the PostgreSQL 9.1 documentation is when the slave completes recovery it switches to a new timeline to prevent overwriting of the WALs of a previous timeline.

I am not clear how this is used in a recovery scenario, and the purpose of the .history file and the 'recovery_target_timeline' setting to 'latest'.


texts:
I have a series of SQL statements that look like the following:

BEGIN;
SELECT counter FROM table WHERE id=X FOR UPDATE;
REALLY COMPLEX QUERY;
UPDATE table SET counter=Y WHERE id=X;
END;

I'd like to prevent the counter from being read while I recalc its value, but according to the Postgres docs "Row-level locks do not affect data querying; they block only writers to the same row."

Questions:

What's the point of an "exlusive" row lock if it doesn't prevent reads?  Is it only to prevent other transactions from taking share locks?
If I read the row with SELECT ... FOR SHARE, does that achieve the same affect as an "exlusive" lock?
Is is possible to turn off MVCC for a table/schema/database and allow in-place writes?


texts: Mongodb rs.add() new member in STARTUP state

texts:
I have been running a shell script that starts mongod instances on different machines, initializing them (the first machines) and adding them to a replica set (the others). The following is a summary of what I ran:

  Machine 1 - mongod and initiate as primary by running rs.initiate()

  Machine 2 - mongod and contact primary to run rs.add()

However, when I add the second mongod instance, the response was ok, but running rs.status() on primary it shows Machine 2 in STARTUP state

  {
                          "_id" : 1,
                          "name" : "Machine 2",
                          "health" : 1,
                          "state" : 0,
                          "stateStr" : "STARTUP",
                          "uptime" : 30,
                          "optime" : {
                                  "ts" : Timestamp(0, 0),
                                  "t" : NumberLong(-1)
                          },
                          "optimeDurable" : {
                                  "ts" : Timestamp(0, 0),
                                  "t" : NumberLong(-1)
                          },
                          "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
                          "optimeDurableDate" : ISODate("1970-01-01T00:00:00Z"),
                          "lastHeartbeat" : ISODate("2018-06-14T07:07:11.336Z"),
                          "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
                          "pingMs" : NumberLong(0),
                          "configVersion" : -2

Further investigation using the db.runCommand({getLog:"rs"} reveals the following:

  "2018-06-14T12:23:54.223+0800 I REPL     [replexec-5] Member Machine 2 is now in state RS_DOWN"

Why is my machine on STARTUP when I first add it? What is this RS_DOWN? I'm using MONGO 3.64 for both machines. I always delete local db before trying out the scripts


texts: Extract four digits from a column by regexp

texts:
I am sitting on a database where one column contains two data types ;)

Example

"1";"When the Wind Blows   0275";"James Patterson "
"4";"The Reluctant Fundamentalist      1026";"Mohsin Hamid";

The user thought it would be easily searchable this way...

Now I am trying to fix this, taking the ID out of the title field and putting it into it's own column.

How do I select the title with a regexp so that I only get the four digit number at the end of the title? That number can start with a zero and the title could contain more digits than just those 4.

There is also no guarantee that the digits are the last four characters in the title, sometimes there are some spaces following. SUBSTRING will not work in this case

thanks for help!


texts: Delete all records in tables with reference to another reference

texts:
Sorry if the title to this question makes no sense.  I really wasn't sure how else to word what I needed to do.

So I have the following tables in my database:

customers
    customer_id (PK)
    customer_name
    website

customer_address
    customer_id (FK) (PK-A)
    street (PK-B)
    city
    region
    code

customer_contact
    contact_id (PK)
    contact_name
    customer_id (FK)

customer_contact_phone
    contact_id (FK) (PK-A)
    phone_number (PK-B)
    extension

I am trying to set up a procedure which would allow me to delete all the data pertaining to a specific customer.  Now I know how to delete any or all of the addresses pertaining to the customer whose data i am trying to delete.  However, I am not know how to properly delete all of the contact data.

My issue is that there can be multiple contacts for a customer, and each contact can have multiple phone numbers.  I am not sure how to go about setting up these records for deletion because of how many foreign keys are set up.


texts: In case of multiple updates of the same record in a single transaction, how many versions are being stored?

texts:
According to MSDN article by Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation

"... All the earlier versions of a particular record are chained in a linked list; and in the case of long-running row versioning–based transactions, the link will have to be traversed on each access to reach the transactionally consistent version of the row"

which is in the section "Understanding Row Versioning" common for following both "Row Versioning in Read Committed Using Row Versioning" and "Row Versioning in Snapshot Isolation" sections.

Further on the example of row-versioning due to modifications is given in context of multiple updates of the same record by multiple transactions (T1, T2, T3) in SNAPSHOT only.

In case only one transaction updates a record multiple times (by multiple statements), will multiple version stores be stored (linked) or only the one taken at the moment of the SNAPSHOT "image" taken?

Well, the answer to this question should immediately answer my other pending related questions:

Why do row-versioning isolations use a linked list of versions of record?
Does RCSI use less or same space than SNAPSHOT? Why?


texts: Regex replace with empty string results in NULL characters in between

texts:
Replacing a character with regexp_replace results in NUL characters in between.

SELECT REGEXP_REPLACE(aColumn,'c', '')
INTO OUTFILE 'Replaced.csv';

for abcd results in:

ab d (infact: abNULd)

This does not occur with just REPLACE

using MySQL 8


texts:
I have a mongo DB that store user's public messages.
Each message should be categorized by at least one TAG (3 max in an array)

The TAG can be anything up to 32 characters. ([0..9a..z])

the Tag field is not part of the message field, and a search will only be occurring on the Tag field. (i.e. not full text index)

Since there is almost infinite possibilities for the Tag, my though is that at some point the mongoDB index will be huge, with some Tag referencing millions of documents, while some others will only reference a few.

What it the best practice to keep performances high ? Are my worries not founded ?

P.S. :
- The server's RAM is 64GB.
- The message's date is also indexed (for sorting)


texts: Problem with View When Restoring a MySQL Backup

texts:
I have a database (InnoDB) with some tables, views and routines...

Periodically I do a backup using the command line:

mysqldump -u user -ppassword --routines db_name > backup.sql

But I have a problem also using workbench to create the backup

Btw the backup process terminate without problems or warining...

After restoration I got some problems, the restore process ends well without errors, but some views are corrupted

if I try to alter it or view the structure I got an empty/blank result
if I perform a select on the view I receive the correct results (like the view is not corrupted)...

I cannot alter the view itself..

How can I fix it? And why do I have this problem?

I get a blank/empty result using MySQL Workbench 5.2.35 CE....

using plain sql (describe view_name) I got the following error:

  Error Code: 1356. View 'db.view_name' references invalid table(s) or
  column(s) or function(s) or definer/invoker of view lack rights to use
  them

But the select * from view_name still return the expected result

SHOW CREATE VIEW view_name

  Error Code: 1356. View 'view_name' references invalid table(s) or column(s) or function(s) > or definer/invoker of view lack rights to use them

SELECT * FROM INFORMATION_SCHEMA.TABLES
where table_schema = 'db_name' and table_name = 'view_name'

TABLE_CATALOG   def
TABLE_SCHEMA    db_name
TABLE_NAME      view_name
TABLE_TYPE  VIEW
ENGINE  VERSION NULL
ROW_FORMAT  NULL
TABLE_ROWS  NULL
AVG_ROW_LENGTH  NULL
DATA_LENGTH NULL
MAX_DATA_LENGTH NULL
INDEX_LENGTH    NULL
DATA_FREE   NULL
AUTO_INCREMENT  NULL
CREATE_TIME NULL
UPDATE_TIME NULL
CHECK_TIME  NULL
TABLE_COLLATION NULL
CHECKSUM    NULL
CREATE_OPTIONS  NULL
TABLE_COMMENT  'View ''db_name.view_name'' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them'

This view is a view generated by others views joined each other.


texts: Want to add more options on User database schema to premade users

texts: So I have this User MongoDB Schema, and I want to add other options to it. but if I do then the current users have to create a NEW Account to see the new changes. is there a way to update all the current users' schema to show the new one?


texts: -skipErrors in Transactional Replication not happening

texts:
I have set up a transactional replication initialized using backup for a big database(1.6TB).Due to some error(space issue in the subscriber) the replication was not happening and there were replication error for the past 5 days.
So i added space and now i am getting the 20598 and 547 error in Distribution to Subscriber.

I changed the Agent profile and created a custom profile with the -skipError values as 20598:547.
I restarted the Distribution agent by stopping and starting the distribution job in the subscriber(PULL subscriber).
After restarting i see that, Distributor to Subscriber is initialized in replication monitor,but i don't see that the skipping is happening.

How can i solve this issue.?

I prefer not to drop Publisher/Subscriber as i am initializing using backup and it is a big database.


texts: How do I import the _id index from a MongoDB collection as a hash index for a PostgreSQL table (from a CSV export from MongoDB)?

texts:
I want to be able to keep track of the _id index values that get the MongoDB collection uses when I export the MongoDB collection (via CSV) and import the data into a PostgreSQL DB table (basically for archiving older data not frequently reported on from a MongoDB collection to a PostgreSQL DB table).
That said, if I ever wanted to add the records back into MongoDB for any reason, I want to ensure that the _id MongoDB collection values stay aligned with other collections in MongoDB and tables in PostgreSQL.
MongoDB uses a BSON object 24 character hash for its index (_id) and I want to be able to archive the data from MongoDBto a PostgreSQL DB using that _id value. It looks like it's possible to set a PostgreSQL table column datatype to character(24) and set it to the primary key as a hash using CREATE INDEX idx_hash ON 'pg_table_name_goes_here' USING HASH (_id); rather than requiring it to be a BSON like I originally though I would have to do, but will that work in a typical PostgreSQL table JOIN? and is there a better way to do this?
To put it another way, I'm taking a fair amount of data (about 5 to 8+ Million records a month) that is originating from a MongoDB and trying to archive previous/older data into PostgreSQL. So I want to keep that archived data in PostgreSQL exactly (or as close as possible) the same, in the event I might want to move the archived PostgreSQL back into some kind of NoSQL datastore in the future. In the meantime, I want to make querying the data in PostgreSQL as fast as possible. I want identical index columns / values.
Is this possible (using PostgreSQL v12 and MongoDB v4.4)?
I've been going down some pretty deep rabbit holes and finding a lot of weird and bizarre things about PostgreSQL.  I've traditionally used the MySQL family of Relational DBs and PostgreSQL has some great features but it's been a little more awkward to get the hang of than I expected.  I stumbled across this video series, and it makes sense: idx and seems to fit my use case as well as line up with your above comment. FWIW I did find this: pgbson but the complexity for deployments doesn't seem worth it.


texts: How to grant or allow a particular SQL Server Login/User to always view a Column Level Encrypted data?

texts:
I used Column Level Encryption to encrypt one of the columns in my SQL Server database. Now everytime I want to SELECT to see that encrypted data, I have to open the symmetric key to decrypt the column and then close it.

I want to a particular user or login to always view the encrypted data. Is it possible in Column Level Encryption, if yes, how?


texts: Sync Production Postgres Database to Laptop - One Way

texts: I am running PostgreSQL 11 on my Windows Laptop and would like to sync with our production database on a (semi) regular basis and then test a number of queries and view creation without running the risk of damaging the master data.  The master data set is pretty big and I work at remote offices over a WAN so a simple one-way compare and replace if necessary option would work best.  Most of the utilities I have found are *nix based, so not good for me.   Need some form of 'add in' to PGSQL or a Windows application. Any help appreciated.  Cheers Bill


texts: How can i sync a table between two MySQL RDS insatance

texts:
I am trying to sync one table in two of mysql RDS instances
in AWS something like a db link or FEDERATED engine.


texts: What is a standard or conventional name for a column representing display order?

texts:
For example, a junction table associating a product and its pictures:

create table product_picture (
  product_id bigint not null references product(id),
  picture_id bigint not null references picture(id),
  some_column int not null
);

What is a common/conventional, short, general, name for some_column if it represents the display order of the photos?

order, sort, and sequence are out, as they are keywords.


texts: invalid default value (mysql 5.7) for timestamp field

texts:
EDIT:
When updating an existing database from mysql 5.6 and executing:

UPDATE phppos_register_log SET shift_end = '2015-01-01 00:00:00' WHERE shift_end = '0000-00-00 00:00:00';

This produces:

#1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'shift_end' at row 1
#1067 - Invalid default value for 'shift_start'

This worked in mysql <=5.7. I couldn't find any documentation about this...What is the issue with this?

CREATE TABLE `phppos_register_log` (
      `register_log_id` int(10) NOT NULL AUTO_INCREMENT,
      `employee_id_open` int(10) NOT NULL,
      `employee_id_close` int(11) DEFAULT NULL,
      `register_id` int(11) DEFAULT NULL,
      `shift_start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `shift_end` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `open_amount` decimal(23,10) NOT NULL,
      `close_amount` decimal(23,10) NOT NULL,
      `cash_sales_amount` decimal(23,10) NOT NULL,
      `total_cash_additions` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
      `total_cash_subtractions` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
      `notes` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `deleted` int(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`register_log_id`),
      KEY `phppos_register_log_ibfk_1` (`employee_id_open`),
      KEY `phppos_register_log_ibfk_2` (`register_id`),
      KEY `phppos_register_log_ibfk_3` (`employee_id_close`),
      CONSTRAINT `phppos_register_log_ibfk_1` FOREIGN KEY (`employee_id_open`) REFERENCES `phppos_employees` (`person_id`),
      CONSTRAINT `phppos_register_log_ibfk_2` FOREIGN KEY (`register_id`) REFERENCES `phppos_registers` (`register_id`),
      CONSTRAINT `phppos_register_log_ibfk_3` FOREIGN KEY (`employee_id_close`) REFERENCES `phppos_employees` (`person_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


texts: How do I add a time dimension to this forecasting database without having to duplicate every data point @ each reforecast?

texts:
I have a database designed to facilitate revenue forecasting. Revenue forecasting consists of tracking Orders which are recognized into Revenue at multiple points in time (each, a revenueDate). There are two tables (keys in italic):

Order: id, orderDate
Revenue: ForeignKey(Order.id), revenueDate, amount

At any point in time, querying the Revenue table will give me the total revenue for each revenueDate.

I'd like to add a time component so that I can track what the revenue forecast looked like at any given point of time. Something like the following:

Order: id, forecastDate, orderDate
Revenue: ForeignKey(Order.id), ForeignKey(Order.forecastDate), revenueDate, amount

Doing the table this way would allow me to query (forecastDate, revenueDate) to get my answer. Unfortunately, it would also require me to duplicate all data points for every reforecast, even those that don't change.

Is there a more efficient way for me to design my tables so that each new forecast only requires appending changes or the changed data points, while still allowing me to view a forecast at any historical point in time?

===Update===

I actually don't expect there to be a definitive answer on this, as it seems like luminaries in the field like Snodgrass and Darwen have fundamental disagreements about how best to model time within a database (see ISO's rejection of temporal elements back in 1996).


texts: MongoDB Write Concern when Journaling is off

texts:
I'm running mongo 3.4.7 (WiredTiger) replica set with two secondaries.

According to the docs, when journaling is enabled and write concern is {w: "majority", j: 0}, the ack is sent after the data is written to "In memory".

However the docs don't specify what happens when journaling is off on the server (--nojournal). What happens in that case? What is the write concern waiting for?


texts: In Interactive SQL 12.0.1, How do I declare a temporary table and then run a compound statement in a single SQL Statement?

texts:
I am trying to load a csv file into a temporary table and then run a compound SQL Statement on that table. I can get the DECLARE LOCAL TEMPORARY TABLE ... INPUT INTO ... FROM [filename] to work on its own, but trying to run a compound statement after that produces an error -131 After Insert Trigger Failed. I have no idea what that means as I'm not even using INSERT or triggers to my knowledge.
Here is the code.
DROP TABLE DynamicPLU;
    //delete the temp table

//First Table from First File, Rename Dynamic PLU Items
DECLARE LOCAL TEMPORARY TABLE DynamicPLU (price VARCHAR(14), newName VARCHAR(255), inventory VARCHAR(14));
INPUT INTO DynamicPLU
FROM 'C:\\Shared Stuff\\Database Cleanup - Peter\\Project - Grab-n-Go\\Grab-n-Go Item Entry Test.csv'
DELIMITED BY ',';

CREATE TEMPORARY PROCEDURE GrabnGo_Update()

BEGIN

DECLARE @shortInventory INT ;
SET @shortInventory = 8 ;

//a big update statement

END;
CALL GrabnGo_Update();

I've also tried including the DECLARE statement in one big BEGIN END compound statement, but that produces the same error -131 right after the BEGIN statement, except in this case the code after the BEGIN statement would be DECLARE ... instead of CREATE ...


texts: db_datareader role along with selected table update

texts:
I have given one data base user read permission only, using db_datareader role.
Now, I need to give the same user permission to update only one table (PROD.CustomerRelations).

Is there any way to give this permission ?

EDIT:
I have tried the below method, but still I cannot update. I'm accessing SQL Server DB thru Microsoft Access.

Allow update access to only 2 tables in a database schema

I guess the above method does not work because I used Deny for Delete, Update and Insert in the screen Database Properties -> Permissions -> Explicit permissions for [domain\user1]


texts: MySQL Replication lag vs. no Lag using MariaDB

texts:
I've got a Master-Master Replication set up which currently works fine. I want to add another server to the cluster. I've done all steps necessary to build the new Master (using 5.7.17 on ubuntu xenial) but as soon as I'm starting the Slave Seconds_Behind_Master always keeps increasing and the Slave never catches up.

Using exactly the same setup just with MariaDB is working fine and the Slave immediately catches up - without any hiccup. It's 10.0.29-MariaDB-0ubuntu0.16.04.1.

CPU Load is at zero before the Replication start, the Server is using an SSD and nothing else is installed but Mysql/MariaDB when starting the Repilcation.

Has anyone ever experienced such issue ?

Can someone tell me if I can just use MariaDB as a 1:1 replacement in a Mysql Environment so that I would have an MariaDB server in the middle of my replication setup without problems ?

Thanks !


texts: Transaction and Try-catch in SQL Server Job

texts:
We have DML operations in each step of a SQL Server job. To ensure the update/insert will be rolled back in case something goes wrong, I have wrapped the data modifications of each step in TRY CATCH and TRANSACTION blocks:

BEGIN TRY
    BEGIN TRANSACTION

        [[INSERT/update statements]] ...

    IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRANSACTION
        PRINT 'Successful.'
    END

END TRY

BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION
        PRINT 'Unsuccessful.'
    END
END CATCH

Does it ensure the data manipulations will be rolled back in case of error(s)? Or other considerations should be taken into account?

Would be any better way of doing that (using configurations, etc)?

Thank you.


texts:
SQL> desc dual
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)

SQL> select 4*5 from dual;

       4*5
----------
        20

SQL>

I find it really strange. If there is no column named 4*5 in dual, how does the select statement work?

Also, why don't I see the same behaviour when I create my own dual table?

SQL> create table dual2(dummy varchar2(1));

Table created.

SQL> desc dual2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)

SQL> select 4*5 from dual2;

no rows selected

SQL>


texts: Lots of CLOSE_WAIT and SYN_RECV Captured From ARBITERS With 'netstat'

texts:
I have a cluster with 7 nodes: 1 primary, 3 secondary and 3 arbiters.
I also have several more app servers which communicate with the cluster.
Recently, we've received lots of errors in the mongodb log (prim / secondary) like

  Error in heartbeat request to ARBITER01:<port>

Also, from mongodb logs of the 3 arbiters having the same errors of timeout when trying to connect to other arbiters.
The loads are high in the 3 arbiters due to we captured lots of CLOSE_WAIT (>300) and SYN_RECV (>100) and most of them pointing to the app servers

Questions:

What can cause these errors while the app doesn't have anything to do with ARBITERS?

~]# netstat -an | grep <app IP>

  tcp 0 1 ::ffff::48098 ::ffff::27757 SYN_SENT
  tcp 0 1 ::ffff::44796 ::ffff::27737 SYN_SENT
  tcp 0 1 ::ffff::60354 ::ffff::27721 SYN_SENT
  tcp 0 1 ::ffff::49100 ::ffff::27719 SYN_SENT
  tcp 0 1 ::ffff::52536 ::ffff::27717 SYN_SENT
  tcp 0 1 ::ffff:.159:44446 :::27720 SYN_SENT

Is there any bug from java driver used with mongodb 3.2.19?

rpm -qa | grep mongo

  mongodb-org-server-3.2.19-1.el7.x86_64
  mongodb-org-shell-3.2.19-1.el7.x86_64
  mongodb-org-tools-3.2.19-1.el7.x86_64
  mongodb-org-mongos-3.2.19-1.el7.x86_64
  mongodb-org-3.2.19-1.el7.x86_64

The reason we have 3 arbiters is because we have 3 regions for the replica set of 7 nodes.
We use VM Centos 7 for all the nodes and the arbiters have 8 CPUs.
Is it true that we can reduce the load if all arbiter members are set to priority 0 and hidden = true as we are in MongoDB version 3.2.12 (arbiter's priority default is 0 for 3.6)?
The issue here is we see too many CLOSE_WAIT and SYN_RECV sockets with several connections from the app servers. They come and go but the numbers are always high. It's also caused sometime, the primary/secondaries' logs showing heartbeat timeout to these arbiters.


texts: Is it safe to change the name of a database?

texts: I have a SQL Server database which has data for several customers/devices.  We are changing the prefix of our website (asp.net) and our application dynamically loads the proper connection string based on the first part of the URL.  Is it safe for me to change the name of my database accordingly, or should I simply put a conditional statement in the connection string logic to make the new URL point to the old database? Thanks!


texts: Oracle 9.2 - Bring standby up-to-date using RMAN

texts:
I have a situation whereby an Oracle Standby (managed by data guard) is really out of date.  The redo logs are now being properly sync'd however there is a big gap.

I have the current SCNs for primary and standby but cannot find out how to get RMAN 9.2 to create an incremental backup from the specific SCN.

Does anyone have any experience with this?

TIA


texts: how to check if a procedure is running, and what permissions are required?

texts:
I have a process run by a domain account MY_LOGIN that needs to execute a procedure THE_PROCEDURE but only when another specific procedure, called PROCEDURE_TO_TEST is not running.

All this is within one specific database called MY_DATABASE

How can I do for MY_LOGIN to check if the procedure PROCEDURE_TO_TEST is running ?

What permissions should I grant to MY_LOGIN so that it can do this check?

How can I test if this is working?


texts: How to find SQL Server database is in use or not?

texts:
What query do I need to write to know if the database is in use or not? I tried following query

SELECT
    name
FROM
    sys.databases
WHERE
    state != 0;

but it is not working.

I want to check there should not have a open connection with that database and should not executing a query against that DB


texts: Resource Hosting Subsystem was terminated which caused Availability group to fail

texts:
The following error was occurred in cluster events and the availability group was failed which resulted the databases in non-synchronizing state.

A component on the server did not respond in a timely fashion. This caused the cluster resource 'AG' (resource type 'SQL Server Availability Group', DLL 'hadrres.dll') to exceed its time-out threshold. As part of cluster health detection, recovery actions will be taken. The cluster will try to automatically recover by terminating and restarting the Resource Hosting Subsystem (RHS) process that is running this resource.

Please help me to find the root cause (A component on the server did not respond in a timely fashion).


texts: How the get the number bigger than the average number in Postgresql?

texts:
I have three tables in my db which were created via the following declarations:

create table employee (
id serial primary key,
name varchar (20) not null,
surname varchar(30) not null,
registration_date date default current_date);

create table task (
id serial primary key,
title varchar (50) not null,
created_date date default current_date,
empl_id integer references employee (id) on cascade update,
cat_id integer references category (id) on cascade update);

create table category (
id serial primary key,
name varchar (30) not null );

How ca I get the employee.id of employees who have the amount of tasks assigned to them more than the average amount of tasks assigned to employees?


texts:
To improve query response will it be better to shard the data or replicate existing shards for faster response.

In case of sharding the data might be nicely distributed and hence the queries.

In case of replicating existing shards, there will be more hosts to respond to a query request.


texts: How can I verify that MySQL's slave_compressed_protocol is working

texts:
I've set slave_compressed_protocol=1 on both my master and slave.

Using SHOW VARIABLES, I can see that it's set.

How can I check that it's actually working?


texts: Is this correct normalization for my tables?

texts:
I think I'm confusing myself and making this too complicated. At this point, I need outside advice.
I have a Laravel 7 application where a user will enter orders. Each order has one customer. Each order has one contact. Each customer has many contacts, but contacts only belong to one customer.
When the user is entering the order and chooses a customer, another select field populates with contacts associated with that customer.
Here is my structure:
orders table:
id
customer_id
contact_id
Customers table:
id
name
Contacts table:
id
customer_id
Is this good or do I need to change the structure? I'm having trouble setting up the relationships in Laravel.


texts: How is the password file accessed when it's on an ASM diskgroup and required to connect to ASM?

texts:
When a RAC is turned on, it will try to accessing the OCR registry which is located on ASM.
ASM also holds the asm password file.
However to open ASM, it needs to access the password file.

If the password file is missing or corrupted etc... when turning on the CRS stack, this happens:

2016-01-12 12:00:51.317680*:kgfo.c@1115: kgfoSaveError: ignoring existing error:
ORA-01017: invalid username/password; logon denied
ORA-15077: could not locate ASM instance serving a required diskgroup

Oracle's solution to this is the following:

You can make a backup of the Oracle ASM password file, and if the Oracle ASM
password file is lost or the disk group becomes inaccessible, then you can
restore the backup password file.

Even if ASM is working, how can it access the password file which is inside ASM if it needs a password to access ASM?


texts: Database storage limit for open source PostgresSQL

texts: I just started using PostgresSQL.  When I installed SQL Server Express years back, you typically had a database storage limit if you didn't install the full version.  I think I remember it being 4GB, but that might have changed.  Is there a database storage size limit for PostgresSQL without buying additional licenses or hardware?  What is that limit?


texts: SQL Server 2016: Adding temporal tables to existing application

texts:
I have a big PHP application, developed by an external company, which is being completed right now; I cannot ask for changes to it.
It uses a database Microsoft SQL Server 2016, which is accessed by some users via SSMS, including me, and by some scheduled tasks at specific times of day.
Sometimes I find unexpected content in some tables, and it is frustrating because I don't know when it got there.
I just discovered about temporal tables: knowing the exact date and time when a record changes would be extremely useful.

Is it possible to add temporal tables to 2-3 key tables in the application, without changing the PHP code? Are there risks of compatibility or performances?


texts: how to store multiple value in column in sql server

texts:
I am creating job post application. In my first table the columns are This is my master table

ID
Position
jobDescription
minExp
maxExp
LastDate
InterviewDate
Project
HiringManager
interviewer
Primaryskills
SecondorySkills

and second table contains the primary skills PrimarySkill table

ID
PrimarySkills

third Table contains the secondory skills

ID
SecondarySkills

fourth Table contains the Interviewer

ID
Interviewer

the problem is that if a person have multiple skills and multiple interviewer for a job, Then how can insert the value from primary skill table, secondory skill table and interviewer table in Master table. i do not want to store value in comma separated


texts: How do you generate large OLTP and reporting workloads?

texts:
My current skillset lies towards plain vanilla data warehousing, with < 20k users, our setups are rather simple..

I am looking for ways to enhance my technical skills, however, as much theory as I read, I'm struggling to figure out how I can actually generate workloads of 100k+ transactions/second  , or 10k users hitting a report from different parts of the world.

How do people normally gain expertise in large scale database systems outside of what they do at work?  (I have a MSDN subscription, so $150/month on Azure to play with)


texts: Monitor and alerting tool for specific queries

texts:
I need to monitor and alert a MySQL DB which is hosted on AWS. I'd like an alert to trigger if certain SQL query criteria aren't met (e.g. SELECT * FROM my_table returns less than 100 results).

What open-source tools are available for this type of monitoring? Most I've come across tend to monitor low level MySQL DB stats but I'd like specific queries to trigger alerts. I've come across Prometheus but was wondering if there others I could look into?


texts:
How can I capture or list all queries that use hints and have executed since the last instance restart?

I am using SQL Server 2005 Standard Edition.


texts: How to identify a user (Oracle) that is locked due to unsuccessful login attempts

texts: How to differentiate in oracle a user deliberately locked by the security administrator and locked due to unsuccessful login attempts


texts: Column attributes error while importing to MS Sql Server from MUMPS

texts:
I'm attempting to import data into MSSQL from a MUMPS database. I've configured an ODBC connection to the MUMPS database and can see the expected table structure in the MSSQL Import / Export wizard.

However if I proceed beyond the 'Select Source Tables and Views' screen I get an error,

Cannot load the column attributes from the schema using the current content descriptor

with four column attributes (ORDINAL_POSITION, COLUMN_SIZE, DECIMAL_DIGITS, and COLUMN_SIZE) listed as the error source.

As suggested in the error message I've tried editing the file  ProviderDescriptors.xml (in C:\Program Files (x86)\Microsoft SQL Server\110\DTS\ProviderDescriptors) but to no avail, still get the same error message.

I found and followed this entry on Microsoft Developer
https://blogs.msdn.microsoft.com/dataaccesstechnologies/2010/09/09/sql-server-import-export-wizard-fails-while-trying-to-retrieve-the-data-from-pervasive-database/
but no difference.

Can anyone offer any pointers on where I'm going wrong?


texts: Configured MySQL replication but its not working

texts:
I have configured MySQL master and slave replication setup on 5.5.x.
But for some reason data is not getting replicated properly. What might be the reason?

At Master:

/etc/my.cnf

[mysqld]
log-bin=/var/lib/mysql/mysql-bin.log
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-do-db=cmdb
binlog-do-db=nagios

mysql> show master status;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |   168019 | cmdb,nagios  |                  |
+------------------+----------+--------------+------------------+

At Slave:

/etc/my.cnf

[mysqld]
server-id=2
log-bin=/var/lib/mysql/mysql-bin.log
innodb_flush_log_at_trx_commit=1
sync_binlog=1
report-host=10.10.0.22
replicate-do-db=cmdb
replicate-do-db=nagios

mysql>  show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.0.21
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 198123
               Relay_Log_File: CentOS62-relay-bin.000012
                Relay_Log_Pos: 198269
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: cmdb,nagios
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 198123
              Relay_Log_Space: 198574
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)


texts: How does order of index affect performance in SQL Server?

texts: I am wondering how does order of index affect performance (ASC, DESC) ? If it affects, why ? And which order should I choose ?


texts: How to split a table in SQL Server into two different related tables inserting foreign key?

texts:
I have a table OriginTable with the following structure

EventID | Date       | Value1     | Value2     | Value3
-----------------------------------------------------------
5       | 2019-10-07 | MyValue1_1 | MyValue2_1 | MyValue3_1
5       | 2019-10-07 | MyValue1_2 | MyValue2_2 | MyValue3_2
5       | 2019-10-07 | MyValue1_3 | MyValue2_3 | MyValue3_3
6       | 2019-10-07 | MyValue1_4 | MyValue2_4 | MyValue3_4
6       | 2019-10-07 | MyValue1_5 | MyValue2_5 | MyValue3_5
6       | 2019-10-07 | MyValue1_6 | MyValue2_6 | MyValue3_6

where EventID is reinitiated every day, but the combination of EventID and Date values provides a suitable unique identifier.

I want to split this table into two tables FirstDestinationTable and SecondDestinationTable like

ID | EventID | Date
-------------------------
1  | 5       | 2019-10-07
2  | 6       | 2019-10-07

and

FirstDstTblID | Value1     | Value2     | Value3
----------------------------------------------------
1             | MyValue1_1 | MyValue2_1 | MyValue3_1
1             | MyValue1_2 | MyValue2_2 | MyValue3_2
1             | MyValue1_3 | MyValue2_3 | MyValue3_3
2             | MyValue1_4 | MyValue2_4 | MyValue3_4
2             | MyValue1_5 | MyValue2_5 | MyValue3_5
2             | MyValue1_6 | MyValue2_6 | MyValue3_6

where FirstDstTblID references FirstDestinationTable(ID).

The purpose of this task is to save storage space since OriginTable is 120MB each day and I want to execute this on a daily basis.

Thanks.


texts: How to backup a running SQL Server CE database with full data?

texts:
I need to move my running windows-based app (built in C#) to another machine, but I also need to transfer the whole data, how can I take a backup from the .sdf file and use it with the new installed app?

I tried to take a copy of the .sdf file, but I couldn't find it !

Any help ? ...


texts: MYSQL: What is the benefit of compound indexes, over separate ones?

texts:
I've been experimenting with the performance of SELECT queries on a table with about a million records, consisting of these columns(among others..):

id, lastName, firstName,

Wehn i perform this query:

SELECT *
FROM `user`
WHERE lastName = 'someLastName'
AND firstName = 'someFirstName'

I get more or less the same performance(in terms of miliseconds), whether i have two separate indexes on those fields, or have a compound index(lastName,firstName).

I came across this thread:difference between creating index with two columns and creating separate index on two columns

The guy explains the case where a compound index might fall short, bu he didn't give any example where it can actually be beneficial, over setting up separate indexes.

Can someone provide an example/simple explanation of a scenario where a compound index is a good choice?


texts: odbc connection from windows machine to mysql server running on ubuntu?

texts:
I was able to install mysql server on an ubuntu machine and set up a test data base, created a user and granted that user access to the database.
I also changed the binding address to the IP of the server and opened up port 3306.

I am also able to access the mysql database from another remote linux machine using the mysql client.

My question is, is it possible to access the mysql server from a windows machine using the odbc data source administrator with SQL server native client 10.0? is it even possible to do this?


texts: Activity Monitor % Processor Time grayed out on SQL Server

texts:
The % Processor Time graph at Activity Monitor for remote connections stopped working and it grayed out after the physical SQL Server restart (image below), but when I get in the server with the same Domain User it works fine. It was also working fine on remote connections before the restart and I have noticed that it takes significantly more time to load the Processes than before.

The problem is happening to all users that try to see the Processor Time remotely including sa and server admins so I would think it´s not a permission issue. The server runs Windows Server 2012 and SQL Server 2012.

What could be happening? or How can I solve this?

Thanks in advance! and sorry about my English.


texts:
I am building a web based app that has the potential to have very high traffic and collect a lot of data. Most of this traffic will be utilizing the database. Database is MySQL InnoDB. I want to give users the ability to define their own data fields along with a predefined set of columns that are common. As an example I would have a table (restaurants) with the following columns:

record_id //INT PK auto_inc
  record_uuid //indexed stored as binary(16)
  user_uuid //indexed stored as binary(16)
  restaurant_uuid //indexed stored as binary(16)
  date //indexed
  price //indexed
  party_count //indexed
  rating //indexed
  ...

User "A" would then add his own fields such as (chef_name,phone,table_count,rank)
While user "B" might add fields such as (contract_amount,license_fee,cuisine_type)

My thinking is to have a number of tables to hold a row of data for each one of the custom fields.  So I would have restaurants_extension_varchar, restaurants_extension_int and restaurants_extension_float.

Each of the three tables would look something like this:

extension_id //INT PK auto_increment
extension_uuid //indexed stored as binary(16) used by app to update data value
record_id //indexed INT - used as a key to the main restaurants table
field_identifier // TINYINT
the_data //indexed  varchar/int/float - depending on which table this is

I would then partition each of these tables into 6 partitions. The field identifiers would be held in a memory array for that user and would map the record to a label and page placement data for that custom field. These custom fields will be updated individually through Ajax and not as a group like when you click submit on a multi field form. I expect these tables to hold millions of rows. I have decided to use an auto increment ID as the primary key for all tables plus a UUID ID as the application ID that I will pass to and from the web app to prevent users from manipulating the primary key ID in an attempt to effect other rows.

All of the custom fields need to be searchable.

I have two questions. The first being about the structure. From a pure performance angle, is this a good solution or would I be better off with one row with an abundance of columns.

The second question is regarding the use of an auto increment primary key ID along with an app generated uuid key which will be indexed. From what I understand if I use the uuid as the primary key it will be duplicated in each index that is created. I'm not a DBA so if there is any advice that can be offered I will be grateful. Thanks you!


texts:
I am Amazon Redshift. I tried to calculate the time difference between two time stamp columns in minutes, but the timestampdiff function does not work on it. The error is telling me that minute column does not exist in the table I query. At the same time some answers suggest that timestampdiff does not work for PostgreSQL, but function like date_part does not work on my end as well. Could anyone share experience or any document for the grammar and function on Amazon Redshift?
  select
      email,
      createddate,
      lastloggedin,
      timestampdiff(minute, createddate, lastloggedin)
       from udb.user
       where createddate >= '2019-09-01' and createddate <= '2019-09-30'

Error: [Amazon](500310) Invalid operation: column "minute" does not exist in user;
[SQL State=42703, DB Errorcode=500310]
1 statement failed.


texts: Primary key error when running in C# but not SSMS

texts:
I have written a stored procedure to insert a record into a table that is used with our ERP system.  When a record is written to this table there are triggers on the table that were written by the company we purchased the ERP system from.

When I run the stored procedure I wrote from SSMS the data gets added without any errors.  When I run the C# code I wrote for an ASP.NET app to call that stored procedure I get an error that I can't add a record with a duplicate primary key.

Any ideas on why I'm not getting an error when running the stored procedure from SSMS I get no errors, but when I call the stored procedure from C# and trying to insert the same data I'm getting a primary key error?  I've deleted the record that I added in SSMS and I've verified that the primary key doesn't exist, but I still get the error in my C# code.

Below is the stored procedure I wrote:

/*
declare @Site       nvarchar(8),
    @BatchDef   ApsBatchType,
    @WagonNo    int,
    @Infobar    InfobarType
exec TM_CreatePaintBatchSp 'WI_Plt2','18B84-Paint_S',5,@Infobar OUTPUT
*/

ALTER PROCEDURE [dbo].[TM_CreatePaintBatchSp]
@Site       nvarchar(8),
@BatchDef   ApsBatchType,
@WagonNo    int,
@Infobar    InfobarType = null OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;

DECLARE @CustOrderNo    nvarchar(6) = SUBSTRING(@BatchDef,1,6),
        @SpoolNo        nvarchar(5),
        @Job            JobType,
        @Suffix         SuffixType,
        @OperNum        OperNumType,
        @BatchedProdId  ApsBatchNumberType

BEGIN TRY
    BEGIN TRAN

    EXEC SetSiteSp @Site,null;

    EXEC GetBatchedProdSp @BatchedProdId OUTPUT

    INSERT production_batch
    (prod_batch_id, batch_definition_id, batch_job, batch_suffix, [status], Uf_WagonNo)
    VALUES (@BatchedProdId, @BatchDef, ' ', '0', 'A', @WagonNo)

    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN
    SET @Infobar = ERROR_MESSAGE()
    RETURN
END CATCH
END

ApsBatchType is data type nvarchar(30)

And the primary key is prod_batch_id. The call to SP GetBatchedProdSp gets the next available number to use for the INSERT

Below is the C# code:

SqlConnection Conn = new SqlConnection(GetConnectionString());
        string infobar = "";

        Conn.Open();

        try
        {
            SqlCommand comm = new SqlCommand("TM_CreatePaintBatchSp", Conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@Site", "WI_Plt2");
            comm.Parameters.AddWithValue("@BatchDef", txtBatchDef.Text);
            comm.Parameters.AddWithValue("@WagonNo", Convert.ToInt16(wddWagonList.SelectedValue));
            comm.Parameters.Add("@Infobar", SqlDbType.NVarChar, 2800);
            comm.Parameters["@Infobar"].Direction = ParameterDirection.Output;
            comm.Parameters["@Infobar"].Value = null;

            comm.ExecuteNonQuery();
            infobar = comm.Parameters["@Infobar"].Value is DBNull ? default(string) : (string)comm.Parameters["@Infobar"].Value;
            if (!string.IsNullOrEmpty(infobar))
            {
                DialogMessage.Text = infobar;
                ModalWebDialogWindow.Header.CaptionText = "Error";
                ModalWebDialogWindow.WindowState = Infragistics.Web.UI.LayoutControls.DialogWindowState.Normal;
                return;
            }
        }
        catch (SqlException SqlEx)
        {
            DialogMessage.Text = SqlEx.Message;
            ModalWebDialogWindow.WindowState = Infragistics.Web.UI.LayoutControls.DialogWindowState.Normal;
        }
        catch (Exception ex)
        {
            DialogMessage.Text = ex.Message;
            ModalWebDialogWindow.WindowState = Infragistics.Web.UI.LayoutControls.DialogWindowState.Normal;
        }
        finally
        {
            Conn.Close();
            Conn.Dispose();
        }


texts: What are the differences between SQL I/O Sim and SQL IO tools?

texts:
I want to test my different SQL Server alternatives with a benchmark tool.
I found two tools by Microsoft:

SQLIO Disk Subsystem Benchmark Tool
SQL IO Sim

What are the differences between them? is it the same thing?

Thanks!


texts: Does read-only activity on synchronous secondary replica slow down primary replica in Availability Group?

texts:
Suppose that we have an Availability Group configuration (SQL Server 2017) where primary and synchronous secondary do not share any resources
(each replica have dedicated CPU, memory, disk, etc.)

Will read-only activity on synchronous secondary have performace impact on primary replica ?

The reason I ask because different sources tell different (contradictory) things:

Some sources say it has impact and not recommended to offload read-only activity to sync secondary
(recommend only offload to asynchronous replica), and others tell that is best practice to offload reporting load on secondary even if it is sychronous

What do you think on this ?


texts: SQL Server Cannot drop database  because it is currently in use... but no sessions displayed

texts:
When I try to drop a database I get the error "Cannot drop database "dbname" because it is currently in use". However, when I run sp_who2, there are definitely no sessions connected to this database.  I've also set the database to single_user mode with rollback immediate.

Why is this happening?


texts: how to update records else insert records in postgresql

texts:
How to perform update and insert in same query? i need to update the record, if it is not present then it has to perform insertion i tried this

UPDATE bank SET  Address= $2,"PANCard"= $3,"IFSC" = $4  WHERE user_id = $1;
INSERT INTO bank (user_id,bank_details,"PAN",bank_acc,"UAN",tax)
       SELECT  '$2','$3','$4','$5','$6'
       WHERE NOT EXISTS (SELECT * FROM bank WHERE user_id=$1);

but am getting

{
  "name": "error",
  "length": 123,
  "severity": "ERROR",
  "code": "42601",
  "file": "postgres.c",
  "line": "1274",
  "routine": "exec_parse_message"
}

please help me here


texts:
Is it possible to define recursive composite types in Postgres 13?
create type "t" as (
  "a" int,
  "b" t
);

I am aware that I can use a table with a parent-child relationship. The only disadvantage is that I will need to query each nested level instead of directly accessing the nested data.
I am also aware that I can use jsonb, but I am not sure if it can access the nested data directly without querying each nested level?
What are the other alternatives using PL/pgSQL only?
I need to access each nested level separately.


texts: migrate APEX to a new database with newer APEX version?

texts:
what is the best approach to migrate an APEX app to a newer version of APEX (4.2.3) on a fresh installed database on a new server?

is there any straightforward step by step?
I never done that before!


texts: How can I write procedures that I can loop over to insert a series of files into my database?

texts:
My goal:
Insert a series of csv files into my database by creating procedures for each individual table and then looping over them. My csv files will all be named very similar to this:

1_to_be_inserted_into_table_1
1_to_be_inserted_into_table_2
1_to_be_inserted_into_table_3
1_to_be_inserted_into_table_4
2_to_be_inserted_into_table_1
2_to_be_inserted_into_table_2
2_to_be_inserted_into_table_3
2_to_be_inserted_into_table_4
3_to_be_inserted_into_table_1
3_to_be_inserted_into_table_2
3_to_be_inserted_into_table_3
3_to_be_inserted_into_table_4

This is the pseudocode for the final loop where I'd like to reference all of my procedures:
CREATE OR REPLACE DIRECTORY all_the_data AS 'D:\Favorites\1. Programming\Projects\LYS_database\DATA TO INPUT';

DECLARE @file_selector INT
SET @file_selector=1

BEGIN
    FOR files IN all_the_data LOOP

        EXEC procedure_1 ((file_selector || 'to_be_inserted_into_table_1'|| '.csv')),
        EXEC procedure_2 ((file_selector || 'to_be_inserted_into_table_2'|| '.csv')),
        EXEC procedure_3 ((file_selector || 'to_be_inserted_into_table_3'|| '.csv')),
        EXEC procedure_4 ((file_selector || 'to_be_inserted_into_table_4'|| '.csv')),

    SET @file_selector= file_selector+1
    commit;

END;
/

QUESTION 1: What am I doing wrong with creating the procedure below? It worked perfectly fine to insert data into a table before I tried to make it a procedure.

CREATE OR REPLACE PROCEDURE INSERT_CPP
    (file_name IN varchar2)

IS
    cpp_data VARCHAR(200) := 'D:\Favorites\1. Programming\Projects\LYS_database';

BEGIN
    insert into cpp
    SELECT * FROM EXTERNAL (
        (
      cpp VARCHAR2 (50),
      rfu1 NUMBER (6, 2),
      rfu2 NUMBER (6, 2),
      mean_rfu NUMBER (6, 2),
      charge_ph7_4 NUMBER (2),
      hydropathy NUMBER (3, 1))

        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY (" || cpp_data || ")
        ACCESS PARAMETERS (
            RECORDS DELIMITED BY NEWLINE
            skip 1
            badfile (' || cpp_data || '\badflie_cpp.bad')
            FIELDS TERMINATED BY ','
            MISSING FIELD VALUES ARE NULL
            )
        LOCATION (file_name)
        REJECT LIMIT UNLIMITED) ext
        where not exists (
            select * from cpp c
            where c.cpp = ext.cpp );
END;
/

I get an error:
5/5       PL/SQL: SQL Statement ignored
16/27     PL/SQL: ORA-00922: missing or invalid option
30/1      PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ;

QUESTION 2. Is there a way to write a FOR files IN all_the_data LOOP  in SQL? I tried this solution but the code from the first step wasn't recognized as a command. I did
EXEC sp_configure 'show advanced options', 1

RECONFIGURE

and got
RECONFIGURE
Error report -
Unknown Command

QUESTION 3. Can i write commit; at the end of every loop so that if something goes wrong on the very last file it doesn't rollback everything? Will that work?


texts: Need help with Regex in SQL Server query editior

texts:
I have a file with thousand lines of codes with index details like below:

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test1' AND [object_id] = OBJECT_ID(N'Test1', N'U'))
BEGIN
 CREATE UNIQUE INDEX [IX_Test1] ON [Test1]
(
[cd] ASC
)
 WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,  DROP_EXISTING = OFF ) ON [PRIMARY]
END

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test2' AND [object_id] = OBJECT_ID(N'Test2', N'U'))
BEGIN
 CREATE INDEX [IX_Test2] ON [Test2]
(
[AdID] ASC , [Date] ASC
)  INCLUDE ( [AID], [LM] )
 WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,   DROP_EXISTING = OFF ) ON [PRIMARY]
END

I want to format this data like below for comparison purposes:

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test1' AND [object_id] = OBJECT_ID(N'Test1', N'U'))  BEGIN   CREATE UNIQUE INDEX [IX_Test1] ON [Test1]  (  [cd] ASC   )    WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,  DROP_EXISTING = OFF ) ON [PRIMARY]  END
IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test2' AND [object_id] = OBJECT_ID(N'Test2', N'U'))  BEGIN   CREATE INDEX [IX_Test2] ON [Test2]  (  [AdID] ASC , [Date] ASC   )  INCLUDE ( [AID], [LM] )    WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,   DROP_EXISTING = OFF ) ON [PRIMARY]  END

So basically everything between IF and END should come as one line. I tried to do it using REGEX but didn't get it work. If someone could help me in this regard, it would be of great help.

Please let me know if you need any further details.


texts: What is the Difference between "Internal" and "External" Source Systems in Data Warehousing?

texts:
I am reading about data warehousing and I run into the concept of internal and external source systems.

  Data are extracted from various internal and external source system files and databases.

I couldn't clearly distinguish between the concept of internal and external.

Any explanation of the key difference?

Thank you,


texts: Pivot on datetime with matching multiple column

texts:
I have the following table with column school, state, subject and timedate.

I would like to have output with column school, state, act and english

filter subjects to only Act and English
pivot table to match school and state

I did tried but all queries are not work. Hopefully someone may help. Thanks.


texts:
Our legacy application has over 4000+ triggers.

In order to diagnose a bug, we have to trace which trigger(s) inserted records into a particular table.

Had the legacy triggers logged their activity into a common table, we could have easily traced the activity on a particular table.

But this is not how the triggers were coded.

Is there a way to setup some kind of auditing process to trace following activity on the table :

 a. which trigger inserted record into the table
 b. what was the statement
 c. when the activity took place.

Since this is a development environment - we are not too concerned about performance implications.

thanks all.


texts: SELECT other rows based on one matching row

texts:
I am trying to get related rows in a tag table based on one tag, where "related" means "having the same area_id". Example:

tag       area_id
--------  -------
turkey    1
stuffing  1
carrots   1
chicken   2

I want to get rows 'stuffing' and 'carrots' based on knowing 'turkey'. How might I accomplish this with one query (and/or subqueries) please?


texts: Optimal data types for UUID and ENUM AWS Aurora for MySQL table DDL

texts:
Hi i have a table in MySQL 5.7 Aurora and current data types for the table is
CREATE TABLE `table` (
`ID` varchar(255) NOT NULL,
`USER_ID` varchar(255) NOT NULL,
`PATTERN_TYPE` varchar(255) NOT NULL,
`USES_TYPE` varchar(255) NOT NULL,
`OBJECT_TYPE` varchar(255) NOT NULL

PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

NOTE :I need to  have Index on USR_ID column
To use Aurora parallel query for this table i need to change varchar(255) to varchar(192)
I can do that for ID columns but before doing just wanted to know if i can optimize further ?
I have ID which is UUID generated from application and it is of two kind
6tgbcrq9pfhtyizsdo82mcrzz
0a3687fr-612f-2345-984c-65c100076267

so for above two kind of UUID we can use varchar(192) or even we shrink more ?
Query 2 : We have fixed types for PATTERN_TYPE (88 distinct types) ,USES_TYPE(5 distinct types) and OBJECT_TYPE (10 distinct) .
what would be the best data types for this two columns ? Can we use ENUM for this ?
Why need to convert to varchar(192 ) because of below limitation from Audora MySQL

TEXT, BLOB, JSON, and GEOMETRY data types aren't supported with
parallel query. A query that refers to any columns of these types
can't use parallel query.
Variable-length columns (VARCHAR and CHAR data types) are compatible
with parallel query up to a maximum declared length of 768 bytes. A
query that refers to any columns of the types declared with a longer
maximum length can't use parallel query. For columns that use
multibyte character sets, the byte limit takes into account the
maximum number of bytes in the character set. For example, for the
character set utf8mb4 (which has a maximum character length of 4
bytes), a VARCHAR(192) column is compatible with parallel query but a
VARCHAR(193) column isn't.


texts: Impact of deleting 100GB table on Mirroring

texts: I'm about to delete a large table which is approximately 100GB in size.  I'd like to understand the impact on the mirroring that's in place on this db.  Can anyone confirm?


texts: Setting up triggers based on exposed columns in a view's source table

texts:
I have a table where I want to do a AFTER UPDATE trigger if one of the columns modified was in a specific view

CREATE VIEW [dbo].[iv_EmployeesView]
AS
SELECT        EMPLOYEE_GUID AS EmployeeGuid,
              VID AS Vid,
              PREFIX AS Prefix,
              FIRST_NAME AS FirstName,
              LAST_NAME AS LastName,
              SUFFIX AS Suffix
FROM            dbo.EMPLOYEES_M
GO

CREATE TRIGGER [dbo].[iv_EmployeesChanged]
   ON  [dbo].[EMPLOYEES_M]
   AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT(UPDATE(EMPLOYEE_GUID) OR UPDATE(VID) OR UPDATE(PREFIX) OR UPDATE(FIRST_NAME) OR UPDATE(LAST_NAME) OR UPDATE(SUFFIX))
        RETURN;

    insert into iv_PendingEmployeeChanges (RecordGuid, ChangeType)
         select EMPLOYEE_GUID, 'U' from INSERTED
END

Right now I add all of those IF NOT(UPDATE(EMPLOYEE_GUID) OR ... checks so I only trigger on the fields exposed by the view.

Is there a better way to do this to dynamically get all of the fields exposed by the view? I am concerned about future maintainability where someone will update the view but not update the triggers.


texts: Attaching database to an existing connection

texts:
Is attaching the database supported only by SQLite and MS SQL Server?
All other DBMSes can only have one database per connection?
Scenario:
Let's say there is an archived database with some historical data. The application needs to compare with the current data for the same period.
The database in use is not the two mentioned above.
How do people solve that?
EDIT:
The plain SQL supports the syntax of catalog.schema.table which produces fully qualified table name to the query.
However I believe that in order to use that syntax I have to have a connection to the second database.
Its very easy if 2 databases (2 database files) are running on the same server. Then, in case of SQL Server, for example you connect to the server, issue use db1 and then just use the appropriate syntax.
However if those 2 databases are physically located on 2 different servers, I think I need to create a connection to the second database in order to use fully qualified name. Am I right? I think I am because otherwise the DB engine will not know anything about the second DB file(s).
Now, my understanding is that there should be only one connection in order to use the syntax as SELECT catalog1.schema1.table1, catalog2.schema2.table2 FROM catalog1.schema1, catalog2.schema2 WHERE ....;. Am I right? I don't know for sure, as I'm not a DB administrator - just a software developer trying to work with different DBMSes.
Now in the answer below I was told that the way to have this one connection to 2 different DBMSes - for PostgreSQL and mySQL. I'm sure the same mechanism exists for Oracle/SAP/Sybase/DB2.
TIA for confirming or deniying my suspicions.


texts: Compare ID and if true update another table

texts:
I have 3 tables. 1 Table (Table A) with patients of a certain disease (heart disease for example), and another table with patients (Table B) with all kinds of diseases. The third table is the results table.
What I want to do is compare heart disease patients from table A (table A is only heart disease patients) to kidney disease Patients in table B. IF the there is a hit (returns results) in table B, update the results table with an 'x' under column kidney disease for that ID.
The results table is currently in this format:

ID|Kidney|CHD|Diabetes ... all the way for another 10 types of diseases

To summarise: We check table A against table B with the specified disease name and if there is a return of result for that patientid, the results table updates with an 'x' under that disease name for that specific patientID. I will then rerun the query changing the disease names (from Kidney to cancer for example) to find out if the heart disease patient has cancer?
Sample Data
Table A

PatientID
1
2
3
4
5
.... all the way to 812 (812 patients of heart disease)

Table B

PatientID|Age|Disease1|Disease2|Disease3...all the way to Disease 10
1         50  kidney   lung
2         35  kidney   heart     diabetes
3         94  cancer   pneumonia CHD

What is meant to happen is, for every PatientID in Table A, we compare to table B for that specific disease (example kidney) from disease 1-10 in table B, if there is a result, we then update the results table with an 'x' under that specific disease column in the results table. So in our example, for patient 1 and 2 should have an 'x' under kidney.
With our sample data, the result table should look like this when we run for kidney:

ID|Kidney|CHD|Diabetes
 1     x
 2     x

Can anyone point me in the direction to writing the query for this? The query checks all patientID (812 of them) from table A for a specific disease in table B and if it returns results it updates for that disease and patient with an 'x' in the results table.
If there is a better way to do this, please advise me on how to go about it!


texts: a query that shows the load request received through GPRS by salesman

texts:
I have mobile sales system, in which the salesman sends their stock request in handheld device and through GPRS the data comes to MS SQL database. I need o show a report that shows the which request is received at what time to warehouse administrator and by clicking on request number they can see the detail of that request (each item how much quantity) on daily basis.

  WarehouseTransaction

WarehouseID  TransactionID      TransactionDate             RequestedBy      TransactionTypeID
27             36101398         2012-04-18 14:49:34.000     3                 1

  Warehousedetail

 WarehouseID PackID  Quantity
    27       4          25.000000000
    27       26         175.000000000
    27       30         25.000000000

Whenever I run this query the data should be specific to today date only.


texts: SELECT highest quantity in related table, exclude if there is a tie

texts:
I have a SELECT statement that pulls in product information from a related table, and finds the product with the highest quantity associated to it for that transaction. This is shown below and working as expected:

SELECT
qty.[Id],
qty.[Product_Name],
qty.[Product_Type]

FROM
( SELECT td.[Id], prd.[Product_Name], prd.[Product_Type],
         ROW_NUMBER() OVER (PARTITION BY prd.Receipt_Number ORDER BY prd.Quantity DESC) AS Row
  FROM [Transactions] td
  INNER JOIN [Products] prd
  ON prd.[Receipt_Number] = td.[Receipt_Number]
  WHERE prd.Quantity > 1
) qty

WHERE
qty.Row = 1

The issue is that there may be multiple products that have the exact same quantity. Current the system will pull in the first record it finds of the multiple matches, but I would like to exclude this inner query entirely if there is a tie on the highest quantity, returning a null value. Is this possible?


texts:
Is it possible to limit what values are allowed in a column based off of other values in the row?

For example, my table:

ID  Test_mode  Active
--  ---------  ------
1   1          Null
2   0          1
3   1          0

Is there a way to either change the value of Test_mode to 0 if a 1 is inserted into Active

OR

If Test_mode is 1 not allow insertion/update of Active

OR

Throw some kind of error if Test_mode is 1 and an insertion/update of Active is attempted.

Active can only be NULL, 1, 0, AND only 1 with Test_mode as 0.

I hope this makes sense, if not let me know and I'll update the question.


texts:
I have a table on database temp_db named external_objects and an index on the field id, which is an MongoDB id (like 5458d717cd07870859000003). When I explain this select at this database, I got this

 explain verbose SELECT origin_id
    from external_objects
    where id = '5458d717cd07870859000003'

"Index Only Scan using eoid_lookup_tmp_ix1 on public.external_objects  (cost=0.56..8.57 rows=1 width=16)"
"  Output: origin_id"
"  Index Cond: (external_objects.id = '5458d717cd07870859000003'::text)"

I connected this table to another database, via postgres_fdw with

-- Creating external_objects table
CREATE FOREIGN TABLE external_objects_mosql
(
  id text NOT NULL,
  created_at date,
  origin_id text,
  _extra_props text
) SERVER mosql_data  OPTIONS (table_name 'external_objects', schema_name 'public');

on the staging server, but when I try explain the same query on this foreign table, I see this

 explain verbose SELECT external_objects_mosql.origin_id
    from external_objects_mosql
    where external_objects_mosql.id = '5458d717cd07870859000003'

"Foreign Scan on public.external_objects_mosql  (cost=100.00..147085.60 rows=1 width=16)"
"  Output: origin_id"
"  Remote SQL: SELECT origin_id FROM public.external_objects WHERE ((id = '5458d717cd07870859000003'::text))"

Seems like that when using postgres_fdw, I'm unable to use indexes, and this is costing me too much time. is there any workaround for this?


texts: Most efficient way to perform table operations (spec. drop) on tables having table names that match a certain criteria

texts:
I am performing a schema dump of certain tables. This accompanied by dropping the tables that will be dumped. For example I need to drop all tables that start with, let's say, "SRC_". You guys think of a more efficient way of dropping those tables than the script below? Thanks.

begin
  for rec in (select TABLE_NAME
                from ALL_TABLES
               where TABLE_NAME like 'SRC|_%' escape '|'
                 and OWNER = 'SOME_SCHEMA')
  loop
    execute immediate 'drop table ' || rec.TABLE_NAME;
  end loop;
end;
/


texts: SQL Availability Group restore database to standalone server

texts:
Running: SQL Server 2012, Windows Server 2008 R2

I want to test a backup by restoring onto an isolated server. The backup was taken from an Availability Group (HADR) configuration, but the target server does not have the clustering/HADR configuration and I don't want to have to set that up.

When I try to perform the restore, this following is returned:

Could not process the operation. AlwaysOn Availability Groups replica manager is disabled on this instance of SQL Server. Enable AlwaysOn Availability Groups, by using the SQL Server Configuration Manager. Then, restart the SQL Server service, and retry the currently operation. For information about how to enable and disable AlwaysOn Availability Groups, see SQL Server Books Online.

The message makes sense and I understand it, but have no way to take it out of an AG that doesn't exist on my target, and can't exist due to no cluster, etc.

Is there a way to make this scenario work?


texts: PostgreSQL: streaming binary data to a bytea field is very slow

texts:
I am trying to stream binary data into a bytea field.
The process is quite simple:

loop until the end of the incoming stream
  UPDATE myTable SET data = data || $chunk WHERE id = myId

$chunk is a binding for the current stream chunk
The row (myId) already exists with NULL data
each chunk is about 64k
the final data is around 4MB

Everything is working properly expect that concatenating data become more and more slow as chunks are stored.

Since I need to keep my SQL quite portable, I cannot use "PostgreSQL Large Objects"

Is it possible to optimize this process ?

maybe by pre-allocating with with an empty data, and then use overlay() to place each chunk ?


texts: Problems with Insert( simple query - can't think something )

texts:
I have 2 tables, Tblservicedesk and Tblservicedesk_temp

Columns:

CodServiceDesk
ID_Solicitacao
Solicitante
Departamento
Tecnico
Grupo
Status
Prioridade
Data_Abertura_Chamado
Data_Vencimento_Chamado
Data_Conclusao_Chamado
Nota
Comentario_Atendimento
data_criacao_registro
FlagEmailNota
FlagEmailSLA
FlagEmailFechado
data_resolved

I need to update the Tblservicedesk, with values of these 3 columns (FlagEmailNota, FlagEmailSLA, FlagEmailFechado ) from the Tblservicedesk_temp table.

All of this 3 columns is NULL in Tblservicedesk, and we have values in some columns in Tblservicedesk_temp.

I need to use ID_SOLICITAÇÃO as PK.


texts: How to track SSIS memory and CPU performance?

texts:
I may be missing some pieces to this puzzle, but this is what I have:

All of this was written for us by an outside firm.

There is a .Net application that runs as a service on Server A.

Inside that application SSIS packages are being called. DTEXEC?

Those packages (37 total) reach out to two different SQL Servers (B&C), pull in some data, perform calculations on it, then insert the results into a database on Server C.

Of the 37 packages, only a few of them are my problem area.  I've seen the details, and there are many, many tasks in them.

There are no SQL Server components (database engine or SSIS) on Server A where the .Net app lives, so this is all churning internal to the application. .Net is using 1 NUMA node of 20 cores, 1TB RAM on the box.

This app is 3.0 version, with new functionality added.  2.4 (previous version) works fine on identical hardware.

All of that background to ask this:

Is there any way I can find poorly performing T-SQL inside the SSIS packages that are inside the .Net application?   Or am I missing something hugely obvious in the equation?
What it boils down to is: "where are the calculations/T-SQL actually running" (A vs B/C) and how do I trace/tune them if inside the .Net space?

Profiler, sp_whoisactive, etc. all point to SQL instances. This is all happening solely inside the .Net app, with small exceptions to select data from B and insert to C after calculations.  All network, disk, etc. is performing perfectly.   I'm looking for a tuning tool that can reach into the .Net SSIS if such a thing exists.

It's definitely a tuning issue, but Profiler, sp_whoisactive, etc. show nothing of more than maybe 3 seconds, most well under 1/2 second and less. I don't know enough about .Net to know if those T-SQL tasks are actually running on server A inside that memory space like I think they are, or if there are just so many of them that its all aggregating into "too long".

I wish I was allowed to enable heavy logging in SSIS, but this is part of the vendor code I am not allowed to touch, even for logging :(


texts:
Setup:
SQL AOAG(SQL ALways On Availability Group)

PDC(Primary Data Center) has 2 Nodes(PDCDB01 and PDCDB02)
DRC(Disaster and Recover Center) has 1 Node(DRDB01)
FSW(File Share    Witness)

Quorum Votes:

PDCDB01 - 1 Vote
PDCDB02 - 1 Vote
FSW - 1 Vote
DRCDB01 - 0 Votes

If PDCDB01 and PDCFB02 goes down, I need to force quorom on DRDCDB01 with the sciprts below:
---POWERSHELL
Import-Module FailoverClusters

$node = "Always OnSrv02"
Stop-ClusterNode -Name $node
Start-ClusterNode -Name $node -FixQuorum

(Get-ClusterNode $node).NodeWeight = 1

$nodes = Get-ClusterNode -Cluster $node
$nodes | Format-Table -property NodeName, State, NodeWeight

OR
----CMD SCRIPT
net.exe stop clussvc
net.exe start clussvc /forcequorum

I was using the cmd since I am creating a batch script.
I was reading an article: Manual Failover of Availability Group to Disaster Recovery site in Multi-Site Cluster
It stated that:

Once the remote node has been granted a vote for quorum, then remove
the votes of the two nodes in the primary datacenter.

So I created a cmd script instead of the powershell script mentioned in the article:
  cluster.exe node DRDB01 /prop nodeweight=1 /prop:NodeWeight /status
  cluster.exe node PDCDB01 /prop nodeweight=0 /prop:NodeWeight /status
  cluster.exe node PDCDB02 /prop nodeweight=0 /prop:NodeWeight /status

I know that General rule of Quorom is votes should be ODD number.
Upon including it in my batch script, i get the error below:

System error 5954 has occurred (0x00001742).
The request is invalid either because node weight cannot be changed
while the cluster is in disk-only quorum mode, or because changing the
node weight would violate the minimum cluster quorum requirements.

Only PDCDB02 has been change to 0 vote.
Qurom Votes:

DRDB01 - 1 Vote
PDCDB02 - 0 Vote
FSW - 1 Vote
PDCDB01 - 1 Vote

In that case, my question is do I still need to change the votes of PDCDB01 of PDCDB02 to zero. Otherwise, I will just run:
cluster.exe node DRDB01 /prop nodeweight=1 /prop:NodeWeight /status


texts: Returning true or false in a custom function

texts:
I have this mysql table schema

CREATE TABLE `nxt_records` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `tel_number` VARCHAR(50) NULL DEFAULT NULL,
    `nxt_status` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

and this function

DELIMITER $$
CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar
BEGIN
 if (exists(select tel_number from nxt_records where tel_number = in_number ))then
return 'true';
else
return 'false';
end if;
END$$
DELIMITER ;

which i want to use inside this stored procedure

DELIMITER //
CREATE PROCEDURE nxt()
BEGIN
if (the_nxt(44) = true)then

select 'found.';
else
select 'nothing found.';
end if;

END //
DELIMITER ;

when i run the function code,i get the error

  SQL Error (1064): You have an error in your SQL syntax; check the
  manual that corresponds to your MySQL server version for the right
  syntax to use near 'BEGIN   if (exists(select tel_number from
  nxt_records where tel_number = in_num' at line 2

What is causing this error?.


texts: Table was not locked with LOCK TABLES when recreating an index

texts:
I have a weird problem when recreating the index. This is what happens. This is done through JDBC, in Java btw.

A table 'stuff' is truncated by a process. The application is reinitalizing a cycle (for some processes) by recreating data in this table. Data is around 700 000 records and changing for each cycle. So happens:

First:

lock tables stuff write, inventory read;

Next step is:

insert into stuff (x,y,z) select x,y,z from inventory;

And here is a trick. We experienced that when this table is recreated this way, we need to recreate an index, because if we don't do this, the processes querying by this field are locked while the index is rebuilt. So this is what is done:

alter table stuff drop index idx_UpdatedTime;
alter table stuff add index idx_UpdatedTime(UpdatedTime);

The interesting thing is what happens: either the first alter or the second fails.

1 - The first one fails, if the index doesn't exist (this isn't a problem, this is expected behaviour). The exception says:  "Can't DROP 'idx_UpdatedTime'; check that column/key exists". This is fine. In this case, the index is created by the second alter command. This will lead to point 2- in the next cycle.

2 - If the second one fails, the error says: "Table 'stuff' was not locked with LOCK TABLES". This is only happening when the first alter succeeds. This means that the index gets dropped, but isn't recreated. This will lead to point 1 - in the next cycle.

So, this means that these two behaviors are changing each other. 1,2,1,2,1,2... forever. I don't understand why is this happening, what am I missing.


texts:
How can I select strings that have line breaks in them?

I can think of these possibilities when it comes to line breaks in strings:

create table test1 (
    id number(4,0),
    f1 varchar(25));

insert into test1(id,f1) values (1,'a');
insert into test1(id,f1) values (2,chr(10) || 'a');
insert into test1(id,f1) values (3,'a' || chr(10));
insert into test1(id,f1) values (4,'a' || chr(10) || 'a');
insert into test1(id,f1) values (5,'a' || chr(10) || chr(10) || 'a');
insert into test1(id,f1) values (6,chr(10));
commit;

chr(10) is the value for line breaks.

There might be more possibilities that I haven't thought of.


texts:
i am not getting what is the issue in the query probably i am not following the correct way to put the string and char sign , i am inserting the data in c# to local host with where clause please check the query and Error i am getting

String insertQuery = "insert into exam_add (id,session_id,Title,From_date,To_date,class_id,is_Post,is_Lock) select '"+id+ ",s.session,'" + title.Text+",'"+ from.Value.Date.ToString("yyyy-MM-dd")+",'"+to.Value.Date.ToString("yyyy-MM-dd")+ ", c.class_name,'"+x+",'"+x+" from year_session s, classes c where s.id = '1' and c.id='" + cls + "'";

Error


texts:
I am working with 2 tables and need help to produce an output by converting rows to columns, and i need to sum the value first be grouping
Here is the fiddle: https://www.db-fiddle.com/f/kmQjRvvensRTfYsSELxMF2/1
Here is the table:
CREATE TABLE teacher (
TeacherId INT, BranchId VARCHAR(5));
INSERT INTO teacher VALUES
("1121","A"),
("1132","A"),
("1141","A"),
("2120","B"),
("2122","B"),
("2123","B");

CREATE TABLE activities (
ID INT, TeacherID INT,    Hours   INT);

INSERT INTO activities VALUES
(1,1121,2),
(2,1121,1),
(3,1132,1),
(4,1141,NULL),
(5,2120,NULL),
(6,2122,NULL),
(7,2123,2),
(7,2123,2);

My SQL:
    SELECT totalhours hours
             , branchid
             , COUNT(*) total
          FROM
             ( SELECT COALESCE(y.hr,0) totalhours
                    , x.branchid
                    , x.teacherid
                 FROM teacher x
                 JOIN
                    ( SELECT teacherid
                           , SUM(hours) hr
                        FROM activities
                       GROUP
                          BY teacherid
                       ORDER
                          BY hr ASC
                    ) y
                   ON x.teacherid = y.teacherid
             ) a
         GROUP
            BY hours
             , branchid
         ORDER
            BY hours
             , branchid;

Output:
   +---------------+-------------------+--------------------+
   |     hours     |     branchid      |       total        |
   +---------------+-------------------+--------------------+
   |       0       |        A          |         1          |
   |       0       |        B          |         2          |
   |       1       |        A          |         1          |
   |       3       |        A          |         1          |
   |       4       |        B          |         1          |
   +---------------+-------------------+--------------------+

Explanation:
Table teacher consist teacher id and branch id, while table activities consist of id, foreign key teacher id, and hours. Hours indicate duration of each activities made by teacher. Teacher can do more than one activities or may not do any activities. Teachers who not doing any activity will be set to null.
The objective of queries is to produce a table that consist of summary of teachers activity by branch and group by hours.
In the expected output table, 'Hours' is a fixed value to indicate hours from in ascending order starting from 0 to 12. It will still display value even there are no hours value for A and B. A and B columns are branch. The value indicates total number of teachers who are doing activities. So, for row 0, there are 1 teacher for branch A and 2 teachers for branch B who are not doing activities.
Expected output:
   +-----------+------------+------------+
   |   Hours   |     A      |     B      |
   +-----------+------------+------------+
   |     0     |     1      |     2      |
   |     1     |     1      |     0      |
   |     2     |     0      |     0      |
   |     3     |     1      |     0      |
   |     4     |     0      |     1      |
   +-----------+------------+------------+


texts: How to install Oracle 10g Express and 11g Enterprise on same machine

texts:
I have to continue maintaining an Oracle 8i database that receives information (about 20 Inserts and Updates a day) from a 9i database.  I am upgrading the 9i database to 11g which does not support database links to 8i.

The plan is to link from Oracle 11g to Oracle 10 XE and then to the 8i and hope that one day I won't have to do this.

I have already found articles noting that installing XE after any other version is not recommended.

Has anyone installed XE and another Oracle version side by side?  What order was used and what problems were encountered?

Edit: @George3 some forum posts indicate that you can use JDBC drivers to connect 11g and 8i.  Please add some detail and post it as answer.


texts: How to Backup and Restore Filestream Data

texts:
I am trying to backup and restore my database which includes a filestream filegroup.

I backed up the database without the filestream filegroup and was interested to know what happens when you restore it. Inside the restore dialog nothing appeared in the 'Select backup sets to restore' list. This is not exactly surprising.

Then, I backed up the filestream data into a separate .bak file.

When I tried to restore them both together I got this message,

  The media loaded on 'backup file' is formatted to support 1 media
  families, but 2 media families are expected according to the backup
  device specification.

What does that mean?
How can I backup filestream and database separately?


texts: SQL To Return True False base on condition where it either matches the condition or is completely null

texts:
I have written an SQL query which I am thinking might not be right.
I have Table A(Column A_PK, B, C, D), Table B(Column A_PK, C_PK), Table C(C_PK,..)
Table B is associative entity for A and C.
i,e it has two columns A_PK from Table A and C_PK from table B.

I want to select All As where it has an entry in B and matches my search param for C_PK.

  SELECT * FROM
  A a
  WHERE EXISTS(SELECT 1 FROM B b WHERE A_PK = a.A_PK AND b.C_PK = @mysearch)
  OR NOT EXISTS(SELECT 1 FROM B WHERE A_PK = a.A_PK

Is this fine, OR I should use left join.?

Thanks in advance for looking at it.


texts: MySQL: Should I keep a running total column in one table instead of summing a second table?

texts:
I have 3 tables named users, achievements, and  userAchievements. The users table contains information like username, password, e-mail, etc... and the achievements table contains a list of possible achievements and how many points they are worth, and userAchievements lists all achievements completed by all users.

users                        userAchievements           achievements
--------------------------   ------------------------   ------------
username | email             username | achID           achID | points
johnb     john@aol.com       johnb      ach001          ach001  5
daver     dave@yahoo.com     johnb      ach002          ach002  10
miket     mike@hotmail.com   daver      ach001          ach003  15

In my app, once the user is logged in, some information about them from the users table is displayed along with their total achievement points. I'm doing this using two SELECT queries, one to pull information from users, the other to sum the points of all the achievements they have completed. In order to cut it down to one SELECT query, I'm thinking of adding an "achievementTotal" column to users which gets added to by the point value every time an entry in userAchievements is made.
Is it possible to get everything I need with just one SELECT query without doing that? Or is there an even better way to implement what I am trying to do?


texts: Possible to run two DBCC INDEXDEFRAG commands simultaneously, each on a different table?

texts:
I am currently running a script which performs a DBCC INDEXDEFRAG on every table in a SQL Server 2005 database, one table at a time.  Using DBCC DBREINDEX instead of INDEXDEFRAG is not an option, due to space constraints and uptime requirements.

I have noticed that it takes a long time for certain tables to be defragmented.  For instance if I examine the "sys.dm_exec_requests" dynamic management view, I can see that the following INDEXDEFRAG is currently churning away on the clustered index of a table that has a table_id of 829610394:

DBCC INDEXDEFRAG (0, 829610394,         1)

I know that it will be a long time before the defragmentation process completes.  Leaving aside the fact that the script currently running will eventually defragment all the tables, is there any harm in me manually running another DBCC INDEXDEFRAG on the clustered index of another table while the current command executes?  Will both tables actually be defragmented at the same time if I do this?


texts: How to create a function in MySQL that will return a random bool

texts:
So far I've got this:

CREATE FUNCTION IS_ROLEMEMBER (@Rolename VARCHAR(255), @Username VARCHAR(255))
RETURNS BINARY AS
RETURN (
 SELECT (FLOOR(RAND() * 10) % 2) AS BINARY
);

But that gives me "wrong syntax by 'RETURN ( SELECT (FLOOR(RAND() * 10) % 2) AS BINARY)'"

ignore the paramters and function name, I want this for testing


texts: Would there be a reason not to make primary keys unsigned?

texts:
I am going through a database creation script created by people with more experience than me, and I noticed that the primary keys were set to  signed.

So like this,

CREATE TABLE `exampleTable`
(
`pk` INT (11) NOT NULL AUTO_INCREMENT,
`content` TEXT
) ENGINE = INNODB
ROW_FORMAT = COMPACT;

instead of like this,

CREATE TABLE `exampleTable`
(
`pk` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
`content` TEXT
) ENGINE = INNODB
ROW_FORMAT = COMPACT;

It seems to me that by setting the primary key to be unsigned one has doubled the possible number of values, am I missing something where it would actually be better to use a signed int for the primary key?


texts:
More of rant, than a question, but maybe someone would explain -- why PostgreSQL migration wiki states that

  FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION

when in PostgreSQL DOUBLE PRECISION supports 15 significant digits, whereas in Oracle DOUBLE provides 38 ?!

IMO, this is quite an omission for people planning to migrate their data to PostgreSQL.


texts:
We have about 5-6 different internal webapps (staff use only) that we will be transferring across to a new virtual server, however I need to setup our my.cnf settings first and I am lost (I am web designer - so this is a little out of my league!).

Our internal systems have about 100 tables apiece, using primarily InnoDB but in some cases using Myisam. Also, 1 of our webapps has quite a lot of data (100,000 records+) and the SQLs used to select the data are quite inefficient (few indexes used, inner SELECTs etc). Roughly about 50 users at any one time.

Our new server will be the HS-250 from HeartInternet.

Many thanks for the help,


texts: Backing up Oracle home directory while instances are running

texts:
Can I take a backup of Oracle home directory (with cp -r) before upgrade while the instances using the home are running or is it required to shutdown instances first?

Trace files/alert logs are in different directory structure and all data files, redo log files, control files and spfiles are in ASM.


texts: Do SQL Servers in a cluster need to have the same memory?

texts:
I have two servers in always on failover configuration with windows clustering underneath.

Do both servers need to have the same memory?
One of our DBs that uses a lot of memory is not in the AG, so ideally I don't want to give the secondary server the same memory as the primary because - it will never need to run the big DB.


texts: Advantage of using a database snapshot for reporting purposes

texts:
What is the performance advantage of using a snapshot of a database for reporting purposes?

The way I see it, it would probably downgrade performance since for every write in the original database, another write would have to be done for the snapshot itself.

I can see that you'd use snapshots whenever you want to do reporting of data up to that point, but that doesn't fall into the performance category.

So again, is there a performance advantage?


texts: Moving MySQL to another server without updating connection strings

texts:
Currently, I have a Windows 2008 R2 Server which runs both all my websites and all my MySQL databases.  I am considering moving MySQL to another seperate server which may also be Windows or may be Linux.  My question is, if I do this, and MySQL is then on a different IP address, is there anyway of aliasing the IP address of the new MySQL server from the current server hosting the websites so I do not have to go through and update every single connection string?

Thanks.


texts: MS SQL :Export data from query output to excel which has a column with huge data

texts:
I have a problem where when the data being exported from query output to excel, data is extending to multiple rows in the excel sheet as the number of characters are more, Is there anyway to handle this.

I have tried exporting the data using the export option in SSMS and save as CSV file option as well along with simple copy paste option. All having the same output .Ie single column data per row in SQL will be extended to multiple rows in the excel , instead of single row in same column.
This could be due to the spaces in the SQL data as well,
Any leads will be helpfull


texts: Does AWS RDS support MEMORY storage engine?

texts: Title says it all.  Strangely can't find a result on this.


texts: I create db with Person, Client, Employee, Manager, Developer. I'm interested what is the best way to put the data in tables. In which cases should programmer unite Client in table hierarchy with EmployeeCategory for next using concept of Table join inheritance (because they have common attributes like firstName, lastName, email..) and in which cases programmer should hold Client table separate from Employee hierarchy (despite that Client has common attributes with Person)?


texts: SQL Server 2008 R2 - Error 18456 State 12 - nothing I find helps me

texts:
First off let me state that I'm not a DBA, I don't have access to a DBA, I'm just a programmer who has to get a CMS working. I'm generally pretty good at getting random servers working, but this is doing my head in.

I have a trail of SQL Server 2008 installed, with a database that I imported from a backup. The CMS starts up, connects to the DB but tells me

  [SqlException (0x80131904): Login failed for user 'kentico'.]

Checking the logs shows me

  Error: 18456, Severity: 14, State: 12.

I googled it, and every answer has state 11 and 12 in lumped together, and I think I've done what is mentioned in the posts. but really im just stumbling in the dark. I don't even know what other information I can provide that will help people help me!

I have created a user in in the \security\logins folder, and this login is mapped to a user of the same name in the \databases\<db_name>\security\ folder. What else do I have to do?!

I should say that changing the connection string to the sa account gets me in to the cms, but then I'm only allowed one connection on the sa account so the CMS randomly spits the dummy when the connection is refused.

Any help would be greatly appreciated, and please remember, this is my first time I've even looked at SQL Server, so the assumed knowledge is just not there :(