texts:
I am converting a MSSQL schema to PostgreSQL and in that schema most tables have a column called Timestamp that are of MSSQL timestamp datatype which is effectively rowversion.

When inserting records into those tables you do not need to specify values for timestamp columns as MSSQL auto updates that column.  I believe this is the same as xmin in PG.

So now when my app tries to insert into the table PG blows up saying it needs a value for the Timestamp column.

I'm therefore thinking after importing the schema writing something that drops all columns from my tables that are called Timestamp and have timestamp datatype.

I have found I can get column and table info from pgclass and pgattribute however I"m now stuck how to loop and do checks and then drop columns. Could someone point me in the right direction please?

select
    t.relname,
    a.attname,
    d.typname
from
  pg_class t
  INNER JOIN pg_attribute a
  on a.attrelid = t.oid
  INNER JOIN pg_type d
  on d.oid = a.atttypid
  where relkind='r' and attname = 'timestamp' and d.typname = 'timestamp'
ORDER BY t.relname


texts: ORA-24033 : No recipient for message has been detected

texts:
I am using Oracle E-business Suite R12.1, 11G database version.

Error I encountered when trying to create or modify a user or responsibility:

  ORA-24033 : no recipient for message has been detected in
  FND_USER_RESP_GROUPS_API.UPDATE_ASSIGNMENT

My workaround

I have followed Oracle metalink doc (Doc ID 358151.1) to fix the error.  Whenever I try to do the first step (dropping the existing subscriber), it gives this error:

  ERROR at line 1:
  ORA-04020: deadlock detected while trying to lock object
  30x0C2ABE5980x0C798B3F80x0D9CA49D8
  ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7541
  ORA-06512: at "SYS.DBMS_AQADM", line 441
  ORA-06512: at line 5

I have to fix ORA-04020 so I can later recreate the existing subscriber (by dropping the the existing one, then adding a new one).

I tried to bounce the applications and the database.
I compiled the apps schema.
I tried to find out where is the lock and which schema is blocking the other so I can kill that session:

sql> SELECT
 c.owner,
 c.object_name,
 c.object_type,
 b.SID,
 b.serial#,
 b.status,
 b.osuser,
 b.machine

FROM
     v$locked_object a ,
     v$session b,
     dba_objects c
  WHERE
     b.SID = a.session_id
  AND
     a.object_id = c.object_id;

Output:

 OWNER    OBJECT_NAME         OBJECT_TYPE  SID       SERIAL#     STATUS
-----   -------------         ----------   ------    -------    -----------
    APPLSYS  FND_CONCURRENT_QUEUES    TABLE     152       3                INACTIVE
    APPLSYS  FND_CONCURRENT_REQUESTS  TABLE     466       7                INACTIVE

Then tried to find out which session is blocking the other to kill it:

sql>SELECT l1.SID  ||' IS BLOCKING '|| l2.SID
FROM v$lock l1, v$lock l2
WHERE l1.BLOCK =1 AND l2.request > 0
AND l1.id1=l2.id1
AND l1.id2=l2.id2;

But I got no returned rows.

Some of the suggestions on the Internet state that a deadlock occurs when there are invalid objects in the database. So, I recompiled the invalid objects by running $ORACLE_HOME/rdbms/admin/utlrp.sql

That script returned an output. The following is a part of the output:

  ERROR at line 1:
      ORA-04063: package body "SYS.UTL_RECOMP" has errors
      ORA-06508: PL/SQL: could not find program unit being called: "SYS.UTL_RECOMP"
      ORA-06512: at line 4

and this was part of the output also:

OBJECTS WITH ERRORS
-------------------
                  1

Note: I am facing this issue after applying a patch. And it's been over 5 days since the deadlock was detected, even though most of the suggestions regarding to the deadlock says that oracle will manage the release itself. Explicit release not required.

Any idea on how to solve this issue?


texts:
I'm reviewing an SQL Server 2008R2 installation having large performance problems. Currently I'm looking into its indexes and foreign keys and have found out that none of the tables have any foreign keys specified. Either the application takes care of all the business logic by itself, or the installation is wrong.

How much do foreign keys affect performance? Or do they exist only for business logic? Are there any cases when not having foreign keys are an advantage? (e.g. this system supports SQL Server, Oracle and DB2.)


texts: Can I upload database backup made through cPanel into phpmyadmin?

texts:
I have been using mybb forum software. As, I use shared hosting so they had provided me cPanel. Through it I had make a backup which is a .gzip file.

Now, my query here is that I want to upload it to another server to do some experiments with my forum. Can I upload the file through phpmyadmin? As my server has no cPanel installed. Will it work?


texts:
I am running a fresh Oracle 11g installation created on lunix redhat 5, but I forget the web interface port, I have already tried this https:// localhost:5500/em/ but did'nt work.

any help will be appreciated.


texts:
I'm working on a web application and I have some questions about MySQL 5.7 LOCK TABLES WRITE behavior.
Ex: LOCK TABLES posts WRITE;

The posts table will be locked by the current session, so the other sessions can not do anything with this table and all the process related to this table will be go in waiting queue, but the other session have access to another tables.
The current session don't have any access to another tables (exclusive access)

My question is about the second (current session):

How can have access to another tables without locking them as write, because I don't want to prevent the other sessions to have access to the other tables
Considering that the first question is not possible, look at this scenario:

SELECT * FROM posts is OK for the current session but SELECT * FROM posts P not possible, but I'm using a framework which use the aliases when doing the queries.
Bref, my problem generate when I wanted to do a bulk insert (which takes some minutes) to one table but I need to read from some other table and I want to block other users to manipulate just this table not the others and it seems that the only way that I have is

LOCK all the tables as WRITE involving my bulk insert even is just one of them is critical for me.
LOCK all the aliases that the framework using as WRITE.

I'll be so happy if here is a better solution.
Thanks.


texts: possible to reference a jsonb_array_length in a where clause

texts:
I am just starting to use some of the jsonb functionality in Postgres 9.4 (would love to move to 9.6) . I have the following query:

SELECT
    queryable_type,
    jsonb_array_length(as_data->'users_who_like') as js_users_count
FROM
    queryables
WHERE
    queryable_type = 'Item' and jt_users_count > 1;

but I am unable to access jt_users_count in the where phrase but can like this:

SELECT
    queryable_type,
    jsonb_array_length(as_data->'users_who_like') as js_users_count
FROM
    queryables
WHERE
    queryable_type = 'Item' and jsonb_array_length(as_data->'users_who_like') > 1
ORDER BY
    jsonb_array_length(as_data->'users_who_like') desc;

To be honest, this just seems a bit unusual and possibly wasteful. Is there a way for Postgres to only compute this once and use a reference to jt_users_count?


texts: MySQL: Error while reverse engineering database from a sql file

texts:
I'm trying to reverse engineer a DB in MySql with a sql file generated from an OpenOffice database. while trying to import and run the sql file, I get the following error:

    ERROR: Line 1: syntax error, unexpected IDENT_QUOTED, expecting EVENT_SYM or FUNCTION_SYM. Statement skipped.

I tried many a things including removing double quotes and adding delimiter to the statements, yet the issue persists. For your reference, please find the excerpts from the .sql file.

CREATE SCHEMA PUBLIC AUTHORIZATION DBA;

CREATE CACHED TABLE Users(UserID NUMERIC(15) NOT NULL PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
MobileNumber VARCHAR(12) NOT NULL,
EmailAddress VARCHAR(50) NOT NULL,
CompanyName VARCHAR(50),
City VARCHAR(50),
StateOrProvince VARCHAR(50),
CountryOrRegion VARCHAR(50),
PostalCode INTEGER,
PassPhraseID NUMERIC(10) NOT NULL,
Gender CHAR(1) NOT NULL,
Age NUMERIC(3) NOT NULL,
MaritalStatus CHAR(10),
Height NUMERIC(4,1),
Weight NUMERIC(3),
Complexion INTEGER,
PreferredMessageMode VARCHAR(10) NOT NULL,
isPaid BOOLEAN NOT NULL,
FBID VARCHAR(50),
BBM VARCHAR(8),
PreferredVerticals VARCHAR_IGNORECASE(100),
EmergencyNos CHAR(60) NOT NULL,
DOB DATE,
PhotoID INTEGER,
CategoryID NUMERIC(2) NOT NULL,
isActive BOOLEAN NOT NULL,
ActivationDate TIMESTAMP(0));

CREATE CACHED TABLE UserLocation(
UserID NUMERIC(15) NOT NULL PRIMARY KEY,
Location NUMERIC(22) NOT NULL,
Timestamp TIMESTAMP(0) NOT NULL,
CONSTRAINT SYS_FK_101 FOREIGN KEY(UserID) REFERENCES Users(UserID));

I have no clue about this. Please help, Thanks in Advance


texts: MySQL warnings while loading integer data into table (SOLVED)

texts:
For some reason, after loading a large amount of data into the following table (from a single file), all of the values register as 0.

CREATE TABLE secondTable
(
freq int NULL,
w1 int NULL,
w2 int NULL,
w3 int NULL
)

Then loading the data:

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE secondTable

For some reason, all of the data comes up as 0. The data file is 1.0GB (hardly unprecedented). The datafile also has appropriately formatted headers in the same style as other data files that have successfully been loaded into other tables.

EDIT: The problem is clearly tied to some sort of warnings during loading:

Query OK, 18976277 rows affected, 65535 warnings (5 min 49.74 sec)
Records: 18976277  Deleted: 0  Skipped: 0  Warnings: 75905108

Here are the type of warnings I'm getting:

************* 1. row *************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'freq' at row 1
************* 2. row *************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: '' for column 'w1' at row 1
************* 3. row *************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: '' for column 'w2' at row 1
************* 4. row *************
  Level: Warning
   Code: 1261
Message: Row 1 doesn't contain data for all columns
************* 5. row *************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: '' for column 'freq' at row 2
************* 6. row *************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: '' for column 'w1' at row 2
************* 7. row *************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: '' for column 'w2' at row 2
************* 8. row *************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: '' for column 'w3' at row 2
************* 9. row *************

In Python, the lines look like this:  \x001\x00\t\x006\x00\t\x002\x008\x001\x005\x001\x00\t\x002\x007\x004\x003\x002\x004\x00\r\x00\n

UPDATE: UPDATE: I solved the problem by using

file = codecs.open('data.txt','r','utf16')

in Python, then writing to a new file to open in MySQL. Apparently, it was formatted in Little Endian UTF-16.


texts: Remove duplicate values from query result

texts:
I am using Postgres 9.3.9. I have 2 tables :

f_agent :

CREATE TABLE f_agent (
  f_agent__id bigserial NOT NULL,
  f_agent__total_users integer NOT NULL,
  f_agent__dim_time_id integer NOT NULL,
  .... other unnecessary columns
);

dim_time :

CREATE TABLE dim_time (
  dim_time__id bigserial NOT NULL,
  dim_time__date date NOT NULL,
  dim_time__month_start_date date NOT NULL,
  dim_time__week_start_date date NOT NULL,
  dim_time__quarter_start_date date NOT NULL,
  dim_time__year_start_date date NOT NULL,
  dim_time__guid uuid NOT NULL DEFAULT uuid_generate_v4(),
);

Now, f_agent__dim_time_id field in f_agent is FK referencing dim_time__id of dim_time.

My dim_time table has a date and its related week_start_date, month_start_date etc. For example: if dim_time__date is 14-07-2015 then dim_time__week_start_date will be 13-07-2015 (monday is start of week) and start_of_month is 01-07-2015 (always the first).

I have to find  f_agent__total_users from f_agent given a date range and a dimension (week / month / quarter).

What I've tried:

SELECT dim_time__week_start_date, f_agent__total_users
FROM f_agent
JOIN dim_time
    ON f_agent__dim_time_id = dim_time__id
WHERE dim_time__week_start_date IN (
    SELECT generate_series('2015-07-06', '2016-07-03', '7 day'::interval)
);

The output:

 dim_time__week_start_date   f_agent__total_users
"2015-07-13";                      3
"2015-07-13";                     33
"2015-08-10";                     12

But I only need the first value in case 2 week_start_dates are same. The output I am expecting here is:

 dim_time__week_start_date   f_agent__total_users
"2015-07-13";                      3
"2015-08-10";                     12

How can I do this?


texts: lock escalation on a small table (13 rows)

texts:
We have encountered performance issues in the application due to blocking and locking on a table in SQL. The performance team fixed the problem by disabling lock escalation on the said table. Since then, the performance are better.

My issue with this is that said table has only 13 rows. I understand that locks and blocks on this table was the cause of the performance issue, but how could that have been fixed by disabling lock escalation ?

I am missing knowledge. Would it be possible for SQL to escalate fine-grained locks to a table lock for a table with 13 rows ?

Thank you


texts: Joining 4 tables of which one is unrelated

texts:
I have 4 tables as follows.

I want to find a wholesum resultset, limited to 25 records, in the following way:

Users from Table1 based on a condition
And those Users should not be in Table2 [Non restricted users]
Join those records to Table3 on Table1.Group_id = Table3.Group_MEMBER_ID where Table3.GroupName = 'someValue'
Join Table1 with Table4 on Table1.UserName = Table4.UserName

Am currently using two queries and am using UNION. Something like this:

      ( SELECT  Table_4.DeviceName
            from  Table1
            left join  Table2 on Table1.UserName = Table2.UserName
            inner join  Table4 on Table1.UserName = Table4.UserName
            where  Table1.some_column = 'someValue'
              and  Table2.UserName = null
            limit  25
      )
UNION
      ( SELECT  Table4.DeviceName
            from  Table1
            inner join  on Table1.Group_ID = Table3.GROUP_MEMBER_ID
            inner join  Table4 Table1.UserName = Table4.UserName
            where  Table3.GroupName='someValue'
              and  Table1.some_column = 'someValue'
            limit  25
      );

The point is, I cant limit the wholesum resultset to 25 records. I should use LIMIT to each query. Is there a way to merge it into a single query so that I can limit the wholesum resultset to 25?

Note: PL/SQL and CTE not available in my Development System and hence am not able to use it.


texts: Why do I need to cast NULL to column type?

texts:
I've got a helper that's generating some code to do bulk updates for me and generates SQL that looks like this:

(Both the active and core fields are of type boolean)

UPDATE fields as t set "active" = new_values."active","core" = new_values."core"
FROM (values
(true,NULL,3419),
(false,NULL,3420)
) as new_values("active","core","id") WHERE new_values.id = t.id;

However it fails with:

  ERROR:  column "core" is of type boolean but expression is of type text

I can get it to work by adding ::boolean to the nulls, but that just seems odd, why is NULL considered of type TEXT?

Also it's a bit tricky to cast because it would require quite a bit of a rework of the code for it to know what type it should cast NULLs to
(the list of columns and values is currently being autogenerated from a simple array of JSON objects).

Why is this necessary and is there a more elegant solution that doesn't require the generating code to know the type of NULLs?

If it's relevant, I'm using sequelize over Node.JS to do this, but am also getting the same result in the Postgres command line client.


texts:
I have 1 tempdb mdf file, and 7 ndf files.  They all grew to 63 GB, and filled up the 500 GBdrive (the .mdf and .ndf files say 99% free space.).
I ran sp_who2 'Active' and there is no active transaction using tempdb.  Why can't I release space?
I cannot restart this server till Friday, which is our designated maintenance window. What should I do until then?
Update
So I got permission to reboot the server. However it did not free up the space...tempdb files are still big. What do I do?


texts:
In a standard master-slave setup using MariaDB (10.3 at this time) and GTID (Global Transaction ID), it is unclear wether to use current_pos or slave_pos.

Reminder from the documentation:

  Using the value current_pos causes the slave to set its position    based on the gtid_current_pos system variable. The slave takes the position given to it by the master server.
  Using the value slave_pos causes the slave to instead use the gtid_slave_pos system variable. With this method, the slave takes into account transactions present    in its own Binary Log.

I think I understand the difference but what is the recommended option?

To the extent that both exist, one might be better for one use case and the other for another use case but the documentation does not clearly state which one should be used in which case...

Which of these options would you use for a very common case of master-slave replication?

EDIT 2019-01-18: Context

The context that led me to ask myself whether I should use current_pos or slave_pos:

I recently upgraded some MariaDB clusters (master-slave setups) from MariaDB 10.1 or 10.2 to MariaDB 10.3 on Debian Stretch, using APT.

In practice, I always proceed like this:

Upgrade slave(s) to 10.3 (using an apt dist-upgrade)
Run mysql_upgrade
Switchover master (using Signal18 Replication-manager)
Upgrade the old master (that became a slave at the previous step)
Switchover master again to return to the original configuration

However, during recent updates, I encountered some failures at Slave Start  after running a mysql_upgrade command. It did not found binlogs using GTID current position.

This happens, I believe, when there has be No transaction on the master since the last master switchover. In this case, switching GTID mode from current_pos to slave_pos fix the problem...

Then, should I definitely use slave_pos? However, even in this case, Replication-Manager forces GTID mode to current_pos after a switchover...


texts:
I'm following this guide to setting up a master-slave config (https://support.rackspace.com/how-to/set-up-mysql-master-slave-replication/) and i'm confused about the end of the document where they are setting up the SLAVE

WHen setting up the slave, they suggest putting the following into the SLAVE my.cnf file

bind-address = 0.0.0.0
server-id = 2
master-host =  [private-IP-of-db01]
master-user = [replication-username]
master-password = [replication-password]
master-connect-retry = 60

Then later on they suggest logging into mysql and then telling the slave where to start replicating from

# mysql -u root -p
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='[private-IP-of-db01]',
MASTER_USER='[replication-username]',
MASTER_PASSWORD='[replication-password]',
MASTER_LOG_FILE='[file-listed-on-master-status]',
MASTER_LOG_POS=[log-position-listed-on-master-status];
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

My question is specifically with the MASTER LOG FILE and MASTER LOG POS.  Are these things not to be set in my.cnf on the slave?

I ask this because if I have to restart the slave mysql for whatever reason one day, how will the slave know where to pick up from?  Do I have to go through the process of re-impoorting the DB to know the position again?  Or do I have to save the position # before taking the slave DB down?

Thanks!


texts: Isolating access to tenant data in a multi-tenant database

texts:
We have a single SQL Server database for all tenants of a web-application. How can we ensure when a support engineer queries the DB directly via SSMS, they only touch data belonging to one tenant?

The web-application reliably isolates data between tenants as all DB queries have an additional clause added automatically to every query: WHERE tenantID = @currentUserTenantId.

The support team have a need to query the database directory and sometimes perform changes to the database. How can we ensure someone doesn't forget to add WHERE tenantID = X when executing a query via SSMS?

Row Level Security (RLS) may be one such solution which would require the support engineer to set the tenantID via SESSION_CONTEXT and reject running the query if no tenantID was specified. Support engineers each have their own SQL User.

Alternatively is there a way, using RLS or otherwise, to ensure that a query ran by a support engineer can only touch a single tenantID? i.e. if they failed to specify WHERE tenantID = X and a query would return data with multiple values in tenantID, then block the query?

Perhaps an ideal solution would be for support engineers not to be able to run any raw SQL and only run predefined stored procedures maintained by the development team. However we don't have the resources to do this at present.

Similarly, Zendesk/Salesforce have the ability for customers to grant access to their respective support teams. I've also seen other solutions which require the customer to share a code with the support team (say by phone). How might that be implemented to protect queries ran via SSMS?


texts:
here we have a VPS with 3.2GHz quadcore processor with 2GB of RAM and 4GB of SWAP which need to work with a high intensive legacy system (read: bad coded). The main problem is that the CPU (~80 - 100%) and memory usage (about 50%) are going to the sky, even in a not so intensive usage.

Current /etc/my.cnf

[mysqld]
query_cache_type = ON
slow_query_log = OFF
long_query_time = 1
innodb_buffer_pool_size = 1GB
join_buffer_size = 16MB
key_buffer_size = 128MB
table_open_cache = 100000
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
max_user_connections = 800
max_connections = 800
slow_launch_time = 1
thread_cache_size = 4
query_cache_size = 512MB

MySQL version

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.34-cll |
+------------+
1 row in set (0.00 sec)

MyISAM index length

mysql> SELECT SUM(index_length) FROM information_schema.tables WHERE engine = 'MyISAM';
+-------------------+
| SUM(index_length) |
+-------------------+
|          82627584 |
+-------------------+
1 row in set, 2 warnings (4.55 sec)

InnoDB index length

mysql> SELECT SUM(index_length) FROM information_schema.tables WHERE ENGINE = 'InnoDB';

+-------------------+
| SUM(index_length) |
+-------------------+
|         466092032 |
+-------------------+
1 row in set, 2 warnings (2.62 sec)

Any can guide-me on what is the first step to solve or ease this issue?


texts:
A part of my database (PostgreSQL 9.3) relies on extra tables (ex. County, City, Town, ... ). I don't manage these tables, they are updated regularly by a third party. Each time I get a new full dump, but I have a hard time to push the changes back into my DB.

I've played with pg_dump / restore and run into some constraint issues duplicate key value violates unique constraint or cannot drop constraint ... because other objects depend on it even with --disable-triggers or --clean option.

Is there an option I've missed? I've found there are some ways to turn constraint on/off but I've no idea if it's the good way to solve this or just a dirty hack? (I'm not a DBA expert.) To be honest, I'm quite surprised there isn't an easy way to achieve this. Maybe I've missed it! I naively thought I could run pg_restore as a big transaction and check constraints at the end of the script. Is it possible?


texts: I have a mysql cluster setup and working. When connecting to it I specify one of the mysql nodes (mysqld) in my connection string. However, if this node goes down it doesn't automatically fail over to the other mysqld node (obviously). When someone creats an app to connect to the cluster do they simply specify each mysqld node in their connection string? In a MSSQL cluster you have a virtual IP you connect to so that instead of specifying each SQL node.


texts: Importing Archived Records Back to Main Database

texts:
I have 2 databases: Current Database & Archive Database.

I need to import the archive data back into the main database.  There are 174,425,460 records that need to be pushed back to the main database.

Currently, I'm running an INSERT INTO, SELECT WHERE NOT EXISTS query in 10,000 row chunks, moving about 100,000 records an hour.  Inserts are based on the clustered index column.  Indexes aren't fragmented so I figure the page splits are what's taking the majority of the time.

This is going to be a weekend only process as to not affect production customers.  It's going to take months to move the data this way!  Does anyone have recommendations on speeding this process up?


texts:
I have seen the following in a query that was supposed to be ported from Oracle outer join syntax to SQL standard outer join syntax:

SELECT ...
FROM A, B, C, D, E
WHERE A.A_ID = B.A_ID
AND B.B_ID = C.A_ID(+)
AND B.B_KEY = C.B_KEY(+)
AND C.C_ID = D.C_ID(+)
AND B.A_ID = E.A_ID(+)
AND B.B_KEY = E.B_KEY(+)
AND 'CONSTANT' = C.X_ID(+)

Now translating the outer join syntax is normally quite a mechanical process, but that last line had me baffled.  What does it mean?  What effect does it have?


texts: How can I delete rows in an oracle table from xml input parameter?

texts:
> CREATE OR REPLACE PROCEDURE PRC_OPEN_PERIOD
>     ( InputXml in VARCHAR2,Tmode in varchar2,pOUT OUT number )
>     IS
>      v_count NUMBER(38) := 1;
>      v_inner NUMBER(38) := 1;
>      str varchar(20);
>      l_xml xmltype;
>     BEGIN
>      IF Tmode ='DELETE' THEN
       l_xml=xmltype(InputXml);
>      DELETE FROM TBL_MST_OPEN_PERIOD
>       WHERE month = l_xml.extract('//openperiod/month/text()').getStringVal()
>       and
>       village_id in (select village_id from tbl_mst_open_period
>       where
>       village_id in
>         l_xml.extract('//openperiod/village/villageID/text()').getStringVal())
> ;
>      pOUT :=1;
>      COMMIT;    END IF;   END;

STRUCTURE OF THE INPUT XML:

<root>
<openperiod>
<month></month>
<village>
   <villageID></villageID>
   <villageID></villageID>
</village>
</openperiod>
</root>

I have a table named tbl_mst_open_period with columns period_id number, month number, village_id number, created_by varchar(20), is_active varchar(1).

I want to delete rows from the table by extracting the values from the xml input parameter. But the data is not getting deleted. Can anyone help??

P.S. When i replace the extarct function by hardcoded values, the delete is working. Also I tried the same xml in an insert into the table by extracting. It was working then.
Also is the delete statement actually correct??


texts: Why is 'LOAD DATA INFILE' faster than normal INSERT statements?

texts:
I've read an article that mentioned we can achieve 60,000 inserts per second by using the LOAD DATA IN FILE statement, that reads from csv files and inserts the data into a database.

Why should it differ from normal inserts?

EDIT:
I reduced the round-trip by calling just one INSERT statement:

INSERT INTO tblname
VALUES (NULL,2,'some text here0'),(NULL,2,'some text here1')
    ,(NULL,2,'some text here2'),(NULL,2,'some text here3')
    .....,(NULL,2,'some text here3000');

What about this?


texts: mysql - Right join not retriving correct data

texts:
I am storing the user activity in the table by start and end time , now i want to get all records from my subject table and matched records from log table.

This is my subject table

This is my log table

I want the output like this

I tried using some code but the records that only matched with log table is return as record, This is what i tried. Any help is appreciable.

SELECT SUM(TIMESTAMPDIFF(MINUTE, A.start_time, A.end_time)) AS prep_time,
                    B.subject_name,
                    A.subject_id
                FROM prep_learn_log A
                RIGHT JOIN prep_subject B ON A.subject_id = B.subject_id
                    AND B.active = 'Y'
                WHERE A.user_id = '1' GROUP BY A.subject_id


texts: How to group records under certain conditons in mysql

texts:
I would like to join 2 tables and group user records based on user id and return its overall status.

Example. Lets say a user applied for a loan, he uploaded multiple documents to bank website which are required to approved by the bank.

if all docs are approved by bank then the status must verified ,

If at least one document is "verified", the overall status is "verified".

If no document is "verified" but at least one is "in progress", the overall status is "in progress".

If no document is "verified" and "in progress", but at least one is "expired", the overall status is "expired".

If no document is "verified", "in progress" and "expired", but at least one is "rejected", the overall status is "rejected".

If no document is uploaded at all, the status is "no xyz uploaded".
 (for documents without expiration date the status is "expired")

Two tables : customer and customerDocs

user has id, name, phone email

customerDoc has id, customerID(reference key), verificationStatus, description, expDate

SELECT
    cu.id CustomerID,
    CASE WHEN cd.verification_status = 'PAID'
     THEN 'PAID'
     WHEN cd.verification_status = 'PENDING'
     THEN 'PENDING'
     WHEN cd.verification_status <> 'PAID' && cd.verification_status = 'PENDING'
     THEN 'PENDING'
     WHEN cd.verification_status <> 'PAID' && cd.verification_status <> 'PENDING'
          && cd.verification_status = 'CLOSED'
     THEN 'CLOSED'
     WHEN cd.verification_status <> 'PAID' && cd.verification_status <> 'PENDING'
          && cd.verification_status <> 'CLOSED'
     THEN 'REJECTED'
     WHEN cd.expire_date IS NULL
     THEN 'CLOSED'
     ELSE 'No status'  END STATUS
FROM customer cu
    JOIN customer_details cd ON cu.id = cd.customer
GROUP BY cu.id

I tried the above query but not getting expected output. So any help here


texts: Restore DIfferential backup on azure managed instance?

texts:
I am migrating my database from on-premise to Azure managed instance. In order to move the database I'm using the organization weekly full backup, and I am asking them to take a diff after they take the application offline (the databases are very large).

Question: Can I restore a differential backup on azure managed instance?

When I try to restore the full with norecovery than apply the DIFF. I get an error: "restore with norecovery is not a supported option in azure managed instance"


texts:
I'm checking out a query which exhibits strangely inconsistent performance.

Using SolarWinds DPA, I can see that the query sometimes uses a Table Spool, sometimes it doesn't.

When it actually used a Table Spool, the query's performance seemed to tank (wait time increased significantly compared to when Table Spool is not used).

See below figure:

Plan #4681967514 has a Table Spool step
Plan #4038619871 does NOT have a Table Spool step

Can you provide a guide, a hint on why the exact same query produced wildly different execution plans like this case?

Note: I've pastebinned the query in Chop: http://chopapp.com/#mpc57cq7


texts: Update a record whilst creating a link to a parent record

texts:
Someones account was deleted from a system we have. They created a new account for her and moved on. But it turns out she wasnt deleted from SQL - when they delete in the app, the record is marked as 'deleted' in a column in SQL.

So I am able to bring the account back, but there are certain triggers/constraints/something that is making it complicated to consolidate the data from the two accounts.

I cant bring back her 'child' records unless they have a link to the parent table, and I cannot have back both records in the parent table simultaneously because two staff numbers cannot exist at the same time (unless one is marked as deleted).

They deleted her new account, so I was able to bring the old one back. Now I need to add the absences that were put in for her new account to the restored old one, but if i try to do so by updating the ID column of her new absences to match the ID of the old (restored) user, I get the error

  Msg 50000, Level 16, State 1, Procedure trsys_Absence, Line 83 [Batch
  Start Line 209]
  ------------------------------------------No link made with the Parent table.

So I thought perhaps I could update the record by somehow linking to the parent table via a join as such

UPDATE tbl_Absence
SET b.ID = a.ID
FROM tbl_Users a
INNER JOIN tbl_Absence b ON a.ID = b.ID
WHERE b.ID = 9999 AND b.deleted = '2019-11-14 10:00:00'
AND a.ID = 9999;

But in this case

  The multi-part identifier "b.ID" could not be bound.

Not sure if what I am doing will even work, but any suggestions of ways I could do this - and hat I am doing wrong in this particular case - would be great, thanks.


texts: Table size without indexes in sql server

texts:
Using the following query I can get the size of the indexes for a table:
SELECT
    i.name              AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(db_id(), object_id('schema.table'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name

If I execute sp_spaceused 'schema.table' does the size without indexes correspond to data column?
Any way to get the size of the table without indexes in sql server? (SELECT statement would be nice as I'd like to plug this query into a bigger query).
If I execute Get size of all tables in database, the sizes for tables are almost the same as the sizes for indexes (suggesting that something is wrong).


texts: MySQL: Updating or closing existing rows in a table and/or inserting new rows?

texts:
I want to update a specific table but in the following manner. When a record is completely the same (so not only the primary key, but all fields) then I don't want any changes (or a new record inserted, etc). It should ignore the record, because nothing has changed. But, when one of the fields of  in a record IS changed, then I want the existing record to be closed with an end date in the field end_date and an new record with a start date and a new follownumber (eg. 02). The end_date of the old record is the start_date of the new record. How do I get this done within MySQL? By hand (manually) and automatically (trigger?). Both would be nice to know. In the end automatically would be the best thing. But I want to understand what happens and why. I really hope someone can help me or show me the right direction. Thanks.

With 'by hand' I mean when I have a existing table and get a .csv file with possible updates. Put this in a temporary table of it's own and then with a sql statement I insert new records, close existing records (and then insert end_date, etc) that have an update record in the temporary table and then last insert the update records from the temporary table with a new subsequent follownumber. After all is done then I delete the temporary table.

If it's not completely clear, I've created a file of how the results should look in Excel: mysql_test_expert_updating.xlsx

I only use Excel as a presentation tool and use MySQL code to get the data from the table. So the Excel I've added is only to show how the content of the table should look...

Kind regards,

Vincent


texts: Best practise for polymorphic associations in MySQL

texts:
I have a MySQL database with 3 tables holding the main classes of data:

 companies (company_id)
 persons (person_id, company_id)
 loans (loan_id, company_id)

Both a 'loan' and a 'person' belong to a company. A company can have loans and a company can have people (such as directors, employees etc.)

There are several scenarios where other data can belong to either a company, a person or a loan, such as 'notes'. For example a user can add a 'note' specific to either a company, a person or a loan.

There are other examples of this such as 'addresses' and 'phone numbers', which can belong to either a company or a person.

I obviously want to have a single table for data such as 'notes', 'addresses' and 'phone numbers', however I am struggling with the best way to link these to companies, people and loans.

Lets use 'notes' as our example here.

Is it better practice to:

1/ Have 3 extra tables linking 'notes' to companies people and loans:

notes(note_id)
persons_notes(person_id, note_id)
companies_notes(company_id, note_id)
loans_notes (loan_id, note_id)

2/ Have a single table 'entities' which has a primary key entity_id used to link all 3 tables:

entities(entity_id)
persons(person_id, entity_id)
companies(company_id, entity_id)
loans (loan_id, entity_id)
notes (note_id, entity_id)

The latter makes some sense in terms of logic, but does not feel right as persons and companies are a completely different class of data to loans. Somehow it feels dirty.

Notes and addresses will be in a one to many relationship with loans, persons and companies. That is, a company may have many addresses or notes. That is the reason I was thinking of the link tables.

A company can have a registered address, and unlimited trading addresses. A person can have a current address, and previous addresses (if at current address less than 3 years).

In this case, it is the company which owns the loan (business loan). The people belong to the company and are usually the company's directors. So, a company can have many persons and many loans.

A company/person/loan can each have many notes
A company/person can each have many addresses


texts: Why use Ola Hallengren DatabaseBackup method with Idera SQLSafe?

texts:
We are planning to implement Ola Hallengren's Maintenance Solution in our environment.

Our organization on the other end, is planning to purchase Idera SQLSafe (requested a demo first).

I see a parameter @BackupSoftware = 'SQLSAFE' in Ola's solution.

Question:

With Idera SQLSafe tool installed, based on the parameter @BackupSoftware in Ola's solution, I assume even Idera SQLSafe can benefit by Ola's solution. Can I know how does it benefit? - Is it just the compression level?
When we have Ola's solution, is it really necessary for a 3rd party tool?


texts: Proper way to store a password within a SQL Server agent job

texts:
I know this is a stupid idea, but I'm having a hard time finding a better solution right now.  I need to execute several sql stored procedures on a daily basis, then execute a powershell script to trigger an external application to pull some data off the sql server

The second step needs to pass username and password as arguments. These two servers are not a part of the same windows domain, so any user-level trust is not possible, only explicitly sending a username and password in the powershell scripts will work.

The server is SQL Server 2008, and we do not have SSIS available as an option.  The sql server itself is restricted to only 3 users today, so having the password in plain text as a powershell command step within an agent job wouldn't get any of those users privilege they don't already have today, but this still just seems like there should be a better way I'm missing.


texts: INET6_NTOA output is set as null to varchar(39) variable

texts:
The following code snippet was working for me in version 10.2.6. But I saw issues in version 10.2.9, where the variable "ip_full_addr" is returning null instead of expected value 2000::

DECLARE ip_full_addr varchar(39) DEFAULT "";
SELECT INET6_NTOA(UNHEX('20000000000000000000000000000000')) into ip_full_addr;
SELECT ip_full_addr;

Does anyone have any clue on why this is happening?


texts: How to connect to an remote PostgreSQL database on Ubuntu using pgAdmin3?

texts:
I'm trying to setup a PostgreSQL database on an Ubuntu machine. I would like to be able to access it using pgAdmin3 from a remote machine. How do I setup this?

I have installed the PostgreSQL database on Ubuntu using:

sudo apt-get install postgresql

In my /etc/postgresql/9.1/main/pg_hba.conf have I this line:

host    all    all    all    password

so it should accept connections from all IPv4-addresses and passwords should be sent in clear text (this is for development reasons).

If I run this command to see what services is running:

sudo netstat -tulpn

I can see these lines, that is showing that PostgreSQL is accepting connections on the default port:

tcp    0    0    127.0.0.1:5432    0.0.0.0:*    LISTEN
3561/postgres

When I try to connect to this PostgreSQL server from a remote computer on the same local network I get this error message:

  Server doesn't listen

  The server doesn't accept connections: the connection library reports

  could not connect to server: Connection refused Is the server running on host "10.0.1.7" and accepting TCP/IP connections on port 5432?

I used postgres as username and no password. But I have also tried with postgres as password. On the local server I can login using:

sudo -u postgres psql postgres

How can I connect to a PostgreSQL database running on Ubuntu from a remote machine using pgAdmin3?


texts:
I have this query:

SELECT *
FROM (
    SELECT @z := ticketing_ticket.id_ticket_category as id, ticketing_ticket.email,
    CONCAT(
          TIMESTAMPDIFF(day,date_create,date_close) , ' jours '
    ) AS 'temps de traitement '
    FROM ticketing_ticket
    WHERE DATE(date_close) = CURDATE()
) AS X
CROSS JOIN (
    SELECT GROUP_CONCAT(T2.label SEPARATOR ';') AS 'Domaines', @z as id
    FROM (
        SELECT @r AS _id,
             (SELECT @r := id_parent
              FROM ticketing_category
              WHERE id_category = _id) AS parent_id,
              @l := @l + 1 AS lvl
        FROM ( SELECT @r :=@z , @l := 0) vars
        CROSS JOIN ticketing_category m
        WHERE @r <> 0
    ) T1
    JOIN ticketing_category T2
        ON T1._id = T2.id_category
) AS Y;

Output:

+----+------------------+----------+-----------------------------+----+
| ID |       EMAIL      | DURATION |           DOMAINS           | ID |
+----+------------------+----------+-----------------------------+----+
| 35 |  p.p@yopmail.com | 65 jours | Elec_Domain;Help_Sub_Domain | 38 |
+----+------------------+----------+-----------------------------+----+
| 36 | test@yopmail.com | 63 jours | Elec_Domain;Help_Sub_Domain | 38 |
+----+------------------+----------+-----------------------------+----+
| 28 |  admin@admin.com | 29 jours | Elec_Domain;Help_Sub_Domain | 38 |
+----+------------------+----------+-----------------------------+----+
| 38 |   test@test.com  | 21 jours | Elec_Domain;Help_Sub_Domain | 38 |
+----+------------------+----------+-----------------------------+----+
| 38 |   test@test.com  | 21 jours | Elec_Domain;Help_Sub_Domain | 38 |
+----+------------------+----------+-----------------------------+----+

My problem is i want to use the id on my second sub-select but on the output is the same id.
What's wrong on my request ?


texts:
I need to add a sequentially-increasing timestamp to a collection of rows in a fine-grained event scheduling system in MySQL 5.5.

For example, if I have 100,000 rows that I want to assign a scheduled time at a given hourly rate, beginning at 2013-06-10 10:00:00 and running at 10,000 per hour, so these 100,000 records would receive timestamps spread across a 10-hour period. I'm happy to be limited to 1-second resolution, so regular timestamps are fine, and it's ok for multiple rows to get the same value. It needs to be done in an update as the records will already exist.

It's trivial to do this naïvely by setting timestamps on individual records, but in this case it would mean 100,000 queries, and I'm sure we can do better than that!

I've seen similar things done for simple numbers using a table of integers, and it's possible I could do something like that; I'd need up to a couple of million events at any given time, and numbers could be turned into timestamps by multiplying fractional seconds (e.g. 10,000 per hour is 0.36 sec per record) and adding an offset for the start time as a unix timestamp.

At the moment I'm drawing a blank on the best way to build a query to do all this. Any ideas?


texts:
In a trigger, I have:

SELECT TOP 1 t.Id
FROM deleted d
INNER JOIN Table t
ON t.Id = d.Id

SELECT TOP 1 t.Id
FROM Table t
WHERE t.Id = (SELECT TOP 1 d.Id FROM deleted d)

Table is a related table that references the trigger's table. Trigger is after Delete

Why does the first return no result but the second does?


texts:
I have a SQL account with no permissions other than having the public role.  I have run the following command to deny permissions to the system views:

  use master
    DENY VIEW ANY DEFINITION TO public;

However when I query view like sys.databases I still get metadata returned.  My understanding was this should not be the case?


texts:
Have a SQL Server 2014 instance running on Windows 2012 R2 that weekly runs Ola Hallengren's DB integrity scripts and the last two weeks started getting this error:

DESCRIPTION:    The operating system returned error 665(The requested operation could
not be completed due to a file system limitation) to SQL Server during a write at
offset 0x000036ec240000 in file 'E:\DBFiles\XXXXX.mdf_MSSQL_DBCC42'. Additional
messages in the SQL Server error log and system event log may provide more detail. This
is a severe system-level error condition that threatens database integrity and must be
corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This
error can be caused by many factors; for more information, see SQL Server Books Online.

I checked both the event log and the SQL Server error log but not getting a lot of additional info that could help.   I'm rerunning the DBCC check for this db as I did last week and it cleared out the MSSQL_DBCC42 file and didn't find any corruption.

Any other places I could find more info other than event log and SQL Server error log?   Should I ask our storage group to see if something shows up on reports for the SAN or is this purely SQL related due to some contention?

This database is pretty active as its replicating the transactions from our JDE financials and manufacturing environment on an iSeries, and currently is about 300gb.   Each night the database is backed up and restored to a development server.  Would it be better for me to run DBCC check against that copy of the database? This DB is a source for building SSAS multi-dimensional cubes for previous day reporting so I would have the option of a complete rebuild of the data or trying to recover from a backup if it became corrupt etc.

Appreciate any thoughts on what could cause this error or the best option for running DBCC checkdb based on the situation.


texts: Update new column with value from old columns for all the rows

texts:
I've got an odd db design in place that was here when I got on my new work space. We have a column named params which contains some string data which appears that it belongs in more than one column, this is the example value of column params :

"---
:body: "Hello\r\nContacting you that I'm ok with your plan.\r\nPlease send me regular updates\r\nThanks"
:emp_id: 4818
"

So I was planning to separate body and emp_id in their own columns so that this table can behave like normal one so we can query the data like normal. Having the data in this format is very hard to query, yet they have silly queries to get/match values.

Anyways I wrote some of my regexes to extract those values and save them to these colums (body) :

select a[1]
from (
    select regexp_matches(params, ':emp_id: ([0-9]+)', 'g') a
    from exchange where id = 483193
) s

for emp_id

select b[1]
from (
    select regexp_matches(params, 'body: (.+):emp', 'g') b
    from exchange where id = 483193
) s

Where id is an id of one exchange. I want to do this for all rows, I tried something but was spinning in the circles, this looked to be most promising but doesn't work :

update exchange
set emp_id = (
  select a[1]
  from (
   select regexp_matches(parameters, ':emp_id: ([0-9]+)', 'g') a
   from exchange where id in (select id from exchange)
  ) s
) where

How can I update all rows? To fill the new columns emp_id and body from existing values in params?

Update:

I've got a massive performance issue here. My db has 500k+ records. And doing update like this :

UPDATE exchange
SET emp_id=cast(subquery.a[1] as int)
FROM (select regexp_matches(params, ':emp_id: ([0-9]+)', 'g') a
    from exchange) AS subquery

Takes really long time. Can this be optimized in some manner?


texts: Database deployment using QSR (Quest Script Runner) is slow

texts:
As part of the automatic deployment of a project, we deploy various Oracle scripts (packages, tables, data, etc...) using qsr - Quest Script Runner that is part of Toad. The script executes multiple sql files using the following syntax:

qsr -c "user/pass@server" -f "file.sql" -o "log.txt" -e

The problem is that we have around 100 sql files that we need to deploy on 8 environments. And each sql file execution takes approx. 1-2 seconds, even if it's a simple file or a simple check (if table exists, skip, else create it). And because of this sometimes the deployment can take 30 minutes or more.

I checked the only documentation I could find in the Help section of Toad and tried the -i parameter and also removing the -e parameter:

I see some improvement, mostly because of the -i parameter, but I am just worried what removing -e parameter really does. Are the scripts stacked then in QSR? When is QSR closed on the deployment agent (the running machine)?

Also, I am a bit puzzled that I cannot find any documentation online about this tool, so any help on how to speed this deployment up would be appreciate (I have already tried sqlplus as an alternative, but it seems our scripts are not 100% compatible with it - some of them are simply not executed).


texts: SSIS processing only 5000 rows from csv file to sql table

texts:
I am loading data from csv file to my sql table through SSIS. Is there any default limit specified on number of records to be read from csv file ?

While loading the csv file, my data flow component is processing only 5000 records, though it contains 5341 records,as shown in the image below.How can i fix this ?


texts:
Consider Relations R and S where are R is having m tuples and S is having n tuples . m<=n . What would be the minimum and maximum number of tuples in each of the following cases (Assume that nothing is mentioned about key constraints)

R union S
R intersection S
R-S
S-R
R NATURAL JOIN S
R Left OUTER Join S
R/S

My Work

R UNION S

max : n+m ( union we add all the tuples from both relations)

min: 0 ( taking m=n=null )

R INTERSECTION S

max : m ( m < n both relation contains same keys then we may get maximum m keys )

min: 0 ( taking m=n=null if no common keys in both relations)

R - S

max : m ( if they are disjoint then in R-S we will get all tuples of R )

min: 0 ( if all tuples in R is also present in S)

S-R

max : n ( as explained above )

min: 0 ( as explained above )

R natural join S

max : n*m ( if no matching key constraints natural join will produce Cartesian product )

min: m ( m < n  when key constraints are taken into consideration )

R LEFT OUTER JOIN S

max : m ( everything from left table will be output even if no match)

min: 0 ( when m=0 )

R/S

max : m ( when n=0 )

min: Im Not able to make a  conclusion


texts: Ensure only one of the several records for a given item has a column with true value

texts:
I have a table called Computer with columns:

id
NIC_name
is_primary

My primary key is (id, NIC_name) and is_primary is a bit field for a boolean value.

I would like to ensure that for all computers with same id there is always zero or one records whose is_primary is set to true. How do I accomplish this?


texts: Questions about Database Design for a Web Application

texts:
Consider a web application with 100,000 users. Each user can create blog posts.  Assume each user has 100 entries.

Would it be appropriate to store all the users in one table, with another table to store the entries made by each user?

Is this a bad design? What would be an efficient way to store all the information to facilitate quick access of the information?


texts: How can I make this backup scheme more friendly for reporting?

texts:
Currently, we have around 50 remote servers, each with a production database. Each night, all 50 of these servers run a backup job and zips the backup. Each morning, a local server runs a job for each site that copies the zip file over, and restores the database to a 'temp' server. The job then copies individual tables (not all tables are copied) to their corresponding database on a 'reporting' server. The database on the reporting server may contain custom tables to track information, so we can't simply drop the database every night.

I feel this process is somewhat redundant. I don't like having the same data in two places, and I don't like have to create scripts to copy individual tables for each remote server added (roughly 1 a month). However, I can't find a good way to do this and keep the custom tables we use.

One of my ideas was to turn the 'temp' server into a repository for the production database backups, and use the 'reporting' server solely for custom tables, but this leads to needing to use linked server joins.

Does anyone have an idea on how I can make this process better?


texts: MySQL "case as column" question in WHERE clause

texts:
I'm setting up a new column with this case statement:

SELECT * ,
CASE t2.field_max_occupancy_value
WHEN  'one' THEN 1
WHEN  'two' THEN 2
WHEN  'three' THEN 3
WHEN  'four' THEN 4
WHEN  'five' THEN 5
WHEN  'six' THEN 6
WHEN  'seven' THEN 7
WHEN  'eight' THEN 8
WHEN  'nine' THEN 9
WHEN  'ten' THEN 10
ELSE 0
END AS numfield
FROM field_data_field_rate_dates t1
LEFT JOIN field_data_maximum_occupancy t2 on t2.entity_id = t1.entity_id
WHERE t1.field_rate_dates_value <=  '2015-07-11'

When I run that query - it works just fine - and I see numfield in the output.

However - when I append something to the WHERE clause...

AND numfield >= 3

It tells me that numfield is an unknown column.

Any ideas as to why?  Is there a declaration I need to make somewhere?  Thanks!


texts:
One common pattern I’ve noticed over the years is that people tend to keep increasing working memory as their data size increases, so that more complex queries can still take full advantage of memory speed, but for some extreme tasks (e.g., reporting).

It could be advantageous to increase that number drastically only for some outlier queries that are seldom run. For instance, I might want to keep work_mem at eg. 8 MB for normal operations but then run a specific query with a specific client/user having something like 250 MB of working memory so that I can do drastic things entirely in memory during a single session, quickly.

Is this possible? Am I completely off base for thinking of this as a solution?


texts: Running MS Access queries for WAN hosted mySQL database

texts:
I have a front end MS Access running through a backend MySQL database via ODBC, I recently moved MySQL database from a local LAN server to a hosted WAN server on Amazon RDS.  Now all of my queries are running very slow. By searching online I found the best way is to rewrite the queries in pass through mode so stored procedures are not running locally in Access.     I have tried VPN vs ODBC, didn't help.

Problem with this approach is that I have thousands of queries (saved and in code) that run sums, dlookups and other Access procedures, it will take months to rewrite all of them correctly so the queries run on the hosted site.

Is there another quicker fix solution to this problem?


texts: Is the everything-is-arithmetic MySQL specific?

texts: Today I learned that SELECT f1,EXISTS(SELECT ...),f2 is valid in MySQL and, reading from StackOverflow, apparently in PostgreSQL as well and the EXISTS part is 0 when the subquery result is empty and 1 when the result is not. Then I began to think and tested a few queries looking like SELECT * WHERE column1 - column2 and even this worked and it's the same as column1 <> column2. And I even tried WHERE (column1 > 0) - column2 and even this worked. Is this SQL standard or just MySQL is trigger happy about casting booleans and integers with wild abandon? Or perhaps booleans do not even exist in SQL and 0 / 1 represents them?


texts:
I am looking for an open source benchmark tool that can easily be extended to custom databases. The reason is I have created a new NoSQL DBMS (called Borrego) that support both JSON queries like MongoDB and DB2 JSON, and generic SQL statements via a JDBC driver. I would like to run some performance tests that I can compare with other existing databases.
Any pointers would be helpful.

Thanks


texts:
I've inherited a SQL Server 2012 database that uses triggers to copy all changes made to dbo tables into an audit schema, providing functionality similar to system-versioned temporal tables.

One of these triggers has recently been causing deadlocks:

ALTER TRIGGER [dbo].[ClientBusinessUnitBehaviour_Trigger_Update]
   ON  [dbo].[ClientBusinessUnitBehaviour]
   AFTER UPDATE
AS

BEGIN

        SET NOCOUNT ON;

        INSERT INTO adt.[ClientBusinessUnitBehaviour]
        SELECT getdate(),'Update', * FROM inserted

        UPDATE [a]
        SET a.[RowVersion] = CASE WHEN ea.MaxRowVersion IS NULL THEN 0x01 ELSE CONVERT(VARBINARY(1), CONVERT(INT, ea.MaxRowVersion) + 1) END
        FROM adt.[ClientBusinessUnitBehaviour] AS [a]
        LEFT JOIN (SELECT Id, MAX([RowVersion]) AS MaxRowVersion FROM adt.[ClientBusinessUnitBehaviour] WHERE Id IN (SELECT Id FROM inserted) GROUP BY Id) AS [ea] ON ea.Id = a.Id
        WHERE a.[RowVersion] IS NULL

END

I haven't seen actual SQL Server logs, but I can access reports that expose data about deadlocks, and these say that both processes involved in the deadlocks are running the second part of this trigger, the UPDATE statement.

Please could someone explain how this can cause a deadlock? My very limited understanding of deadlocks is that they normally occur when two processes are accessing objects in a different order, so I'm not sure how two processes running the same SQL can end up deadlocking.

The transaction isolation level is READ COMMITTED, and READ_COMMITTED_SNAPSHOT is on.

The audit tables don't have primary keys, so I suspect the problem may be similar to the one described here, but that doesn't seem to explain why there's a deadlock, instead of one process just blocking the other temporarily.

Having spoken to the original developer it's become clear that the UPDATE is not required, but I'd like to understand what the problem was.


texts: Pasting data from Excel table takes very long

texts:
I use SQL Server Management Studio 2012 to import data from Excel sheet to a table on SQL Server. I use the most straightforward way that I just Ctrl+C a table in Excel and Ctrl+V in table edit window in SSMS. I know that many other ways faster exist to import data. But this is most simple without a need to write any code.

But it takes 300 rows with 5 columns about 5 minutes. Is there any way how to speed it up without using completely different way that would require copying CSV on server or writing a script? Why does it take so long?


texts: SQL Server 2012 Restore backup to new DB Name

texts:
I seem to remember that, in 2008, you could restore a backup to a new copy of a DB, by changing the name in the "Destination Database" field from the restore-wizard.  It would create a brand new DB, which is a copy of the original DB restored to the point in time that you wanted.  I have not for the life of me figured out how to make SQL 2012 do this.

Now, I understand (thanks to Aaron Bertrand) that this didn't really change, and that 2012 is actually making it more obvious to me that this strategy was a bad idea in the first place!

So, what I need to do is this: Create a new DB, 'MyDB_Copy', from an existing DB, 'MyDB', by using its backup files.  We have nightly full-backups (.bak) and every-15-minute TLogs (.trn).  I don't want the existing 'MyDB' to be affected/touched at all, because it's "live".

After the MyDB_Copy is created from the main full-backup file, I then need to restore a few dozen TLog backups to get it to a certain point in time.


texts: Does MSSQL Log shipping truncate the transaction log?

texts:
I have setup log shipping on a database in Microsoft SQL Server 2012.

I was having issues with my secondary server and I found that this was probably due to a scheduled backup job messing up the log file consistency.

The solution was to set the job to do a copy_only backup however, now I am curious as to whether the log shipping will take care of the transaction log size, or will it just grow indefinitely?

Question: Will log shipping take care of truncating the log file?


texts: How Compare two columns of Same SQL table to find duplicate records

texts:
I have one table with two columns:

StartDateTime
EndDateTime

What will be best query to find out which rows has same (StartDateTime, EndDateTime)?


texts: Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB

texts:
I need to design database to store information contained in millions of log files generated by devices on trial on network.

I have designed 5 different tables to store log file information. But, as the files are very large in number I may fall short of memory on maximum table size. Does anyone know maximum table size limit. I am using MYSQL database on Windows 7 (NTFS) All tables are of type InnoDB.

Thanks for your time


texts:
We are about move all databases from a SQL server 2008r2 (Standard edition) to a 2016 standard edition server.

Many databases have its compatibility level set at 80, wich is not supported on the 2016 server. Our plan is to change all databases to level 100, run some tests, and only then, move the databases to the new server.

My questions are the following:

Should I expect differences in performance after the
compatibility level change?
What is the best way to measure the performance before and after?
Should I take special care when moving the databases the SQL
server 2016?


texts:
I am practicing a file restore using the AdventureWorks database and I am not getting the restore I am expecting. I am sure there's some embarrassingly simple cause, but I have not found it yet.

Here is what I have done

Created a new file group named USERTABLES_01.
Copied data from HumanResources.Employee table to new a new .HumanResources.Employee_01 in the USERTABLES_01 filegroup.
Generated a Full backup.
Executed DELETE FROM HumanResources.Employee_01 with no where clause
Shazbot, I need to restore the table. OK, I'll restore the filegroup USERTABLES_01.
Executed RESTORE DATABASE [AdventureWorks2012]  FILE N'AdventureWorks2012_UserTables_01' ...
Generated a tail log backup
Restored tail log backup

Everything restores without complaint. But my HumanResources.Employee_01 doesn't have any data.

Reviewed MS Example, maybe I didn't follow it correctly, but I am pretty sure I did.

Reviewed similar question, but not sure it's the answer I am looking for:  Restore .mdf data file for a filegroup in SQL Server.


texts: What schema does this mean 'mysql - schema's default character set: utf8'?

texts:
While upgrading mysql from 5.6 -> 5.7 -> 8.0.23 in step 5.7 -> 8.0.23 I got a warning:
The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support.
More Information:
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html

common_schema - schema's default character set: utf8
mysql - schema's default character set: utf8
common_schema._global_qs_functions.function_arguments - column's default character set: utf8
common_schema._global_qs_variables.value_snapshot - column's default character set: utf8
common_schema._global_script_report_data.info - column's default character set: utf8
...

So what means 'mysql - schema's default character set: utf8'? how can I change it to utf8mb4?


texts:
All websites on my VPS getting very slow, after dedcting the problem found that there are a line take 99.9% of the CPU:

/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql \
--plugin-dir=/usr/lib/mysql/plugin \
--user=mysql \
--log-error=/var/lib/mysql/cd.49.be.static.xlhost.com.err \
--pid-file=/var/lib/mysql/cd.49.be.static.xlhost.com.pid

Opened this file found nothing, everything goes right in this file.

EDIT:

etc/my.cnf

[mysqld]
innodb_file_per_table=1

tried to know more, and closed all the content of my domains, also removed each database, one by one and nothing change still the same problem !

Could you help to know from where this error


texts: Rebuild/Re-organize index and Update statistics in SQL server

texts:
For many of the sql servers we have seen that the SQL agent Maintenance job first runs the re-indexing and later as a second step it update stats with full scan.

I want to figure out if auto update stats seems to be working, is it mandatory to update stats straight away once re-index step completes?

Also, are there any scenarios where update stats can be done first and indexing step as second?

The reason for this being that we are witnessing slowness for app/ server which is getting fixed when we manually update the stats. So i am puzzled, if update stats is running fine, why we are facing this issue.

This maint'ce job runs twice a week. so is there a way we can pro actively figure the out of update stats even after successful job run


texts: How To Create A New bash_profile And Connect New Database To It In Oracle

texts:
OS -> Linux

DB -> Oracle 11gR2

Normally my .bash_profile file looks like this:

TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=abcde.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=DB11G; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

But I need to do that:

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/newdb

After that I have to create a new database in newdb.

I'm a newbie oracler and I couldn't find any clear solution. Should I create a directory as newdb and set a new .bash_profile file? If it's a clear solution, how can I do it and after that will my dbca and netca operations work fine? Or something else like this?

Thank you.


texts: Allow MongoDB Read/Write But Disallow Delete Permission

texts:
I want a user to be able to read and update, but not able to delete in any collection for MongoDB.

The command I used is:

db.createUser(
  {
   user: "user",
    pwd: "user",
    privileges: [
       { resource: { db: "icif_pattern" , collection: "" },
         actions: [ "find", "createCollection", "dbStats", "collStats" ] },

    ],
    roles: []
  }
)

But it throws an error:

Error: couldn't add user: "privileges" is not a valid argument to
createUser

I am following the information found in the following article:

Mongo user roles (MongoDB Docs)


texts: I'm using AWS to host my application, and I'm using mongodb as db. I have installed mongodb on amazon ec2, but now I have no idea about where I should store all of my data. Can I store all of my data on ec2 itself as my db will grow with time?


texts: Mysql: slave read binlog from local storage

texts:
I am running master-slave replication, Can I manually move binlog files from the master server to the slave and make the slave read them locally?

I am willing to do so because I am running out of space on the master storage. and the number of binlog files is huge.


texts: Adding database-specific privs for user gives table 'db' is full, MariaDB, *tiny* toy database

texts:
On Windows 10, running 10.4.14-MariaDB - mariadb.org binary distribution
Using phpmyadmin, logged in as root.
I have a rather small WordPress database named PBALL_WP that I loaded from a mysqldump file as the root user through the mysql command-line client. I created a new user named PBALL, and wish to give that user all privileges on database PBALL_WP. Here are the users:
User accounts overview
User name   Host name   Password    Global privileges   User group  Grant   Action
Any         %           No          USAGE                           No      Edit privileges
PBALL       %           Yes         USAGE                           No      Edit privileges
pma         localhost   No          USAGE                           No      Edit privileges
root        127.0.0.1   No          ALL PRIVILEGES                  Yes     Edit privileges
root        ::1         No          ALL PRIVILEGES                  Yes     Edit privileges
root        localhost   Yes         ALL PRIVILEGES                  Yes     Edit privileges

So for User PBALL I click on "Edit privileges". I don't want to give it global privileges (do I need to?), so I click on the "Database" button on the next screen, which brings up a panel for Database-specific privileges. I select "pball_wp" from the selection box of database names and click "go". That brings up the following screen of privileges for user PBALL on database PBALL_WP, and I check the box for "Check All" and click "Go".
     Edit privileges: User account 'PBALL'@'%' - Databases pball_wp
Database-specific privileges Check all
Note: MySQL privilege names are expressed in English.

 Data       Structure               Administration
 SELECT     CREATE                  GRANT
 INSERT     ALTER                   LOCK TABLES
 UPDATE     INDEX                   REFERENCES
 DELETE     DROP
            CREATE TEMPORARY TABLES
            SHOW VIEW
            CREATE ROUTINE
            ALTER ROUTINE
            EXECUTE
            CREATE VIEW
            EVENT
            TRIGGER

Doing that produces the following:
Error
SQL query:
GRANT ALL PRIVILEGES ON  `pball_wp`.* TO 'PBALL'@'%' WITH GRANT OPTION;
MySQL said:
#1114 - The table 'db' is full

I believe that table 'db' refers to the table in the database named 'mysql', and the documentation says that this table records database privileges. So that seems to make sense.
But the mysql table only has three rows in it! Here it is:

Anything I've been able to find about this error has to do with actually exceeding a limit in disk space or pending transactions or something like that. This is basically a new installation (xampp 7.4.10) with this small pball_wp database the very first database loaded into it. Any ideas what I can do to figure out why this is happening? I did have problems getting phpmyadmin working at all, which seemed to stem mostly from the initial configuration specifying "localhost" instead of "127.0.0.1". And I notice that there appear to be two root accounts, one with no password on 127.0.0.l and the other with a password on localhost. I login with a password to phpmyadmin. I definitely got all screwed up trying to get things running at all, I had to keep stopping and restarting httpd in order to get logged in to phpmyadmin...
I'm really at wit's end with this... Help please!
EDIT: Here is what I could find out about the db table. It shows DATA_FREE as 0, but it's 0 for most all tables. As far as I know, that's just the amount of unused allocated space (overhead), and shouldn't trigger a "table full" error.

EDIT 2: I just tried manually inserting a row into the db table with phpmyadmin, and get the same error. So it's consistent - consistently crazy!!!


texts: "Replication Support Only" - Error on LogReader

texts:
Brief explanation is I am trying to set up a basic publication / single subscription of about 5 tables using a remote distribution database.

Servers involved include (Server A pub | Remote Distribution database Server B | Server C sub). All servers are sql 2012 SP2. All on the same domain.

FOR THE LIFE of me, I am continually running into an error and I am just not able to figure it out.

Actual Error in Relp Monitor

The error I am receiving is on the log reader the second I add the actual subscription. If I don't use "replication support only" and switch it to automatic things work as expected...but that isn't what we are aiming for.

  The process could not execute 'sp_MSadd_replcmds' on <'Distribution Server>'. (Source: MSSQLServer, Error number: 1007)
  Conversion failed when converting the nvarchar value '.' to data type int. (Source: MSSQLServer, Error number: 1007)
  Batches were not committed to the Distributor. (Source: MSSQL_REPL, Error number: MSSQL_REPL22020)
  The last step did not log any message! (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

I have also enabled verbose logging on the log reader command and it yields almost the same information

2017-02-07 14:55:19.422 Publisher: {call sp_repldone ( 0x0009d8a6000290d90008, 0x0009d8a6000290d90008, 0, 0)}
2017-02-07 14:55:19.438 Publisher: {call sp_replcmds (500, 0, 0, , 0, 500000)}
2017-02-07 14:55:34.773 Status: 4, code: 22044, text: 'Approximately 500000 log records have been scanned in pass # 1, 0 of which were marked for replication.'.
2017-02-07 14:55:34.773 Status: 4, code: 22044, text: 'Approximately 1000000 log records have been scanned in pass # 1, 0 of which were marked for replication.'.
2017-02-07 14:55:34.773 Status: 4, code: 22044, text: 'Approximately 1500000 log records have been scanned in pass # 1, 0 of which were marked for replication.'.
...
...
...
2017-02-07 14:55:34.960 Status: 4, code: 22044, text: 'Approximately 19500000 log records have been scanned in pass # 1, 0 of which were marked for replication.'.
2017-02-07 14:55:34.960 Status: 4, code: 22044, text: 'Approximately 20000000 log records have been scanned in pass # 1, 0 of which were marked for replication.'.
2017-02-07 14:55:34.976 Status: 0, code: 1007, text: 'The process could not execute 'sp_MSadd_replcmds' on '</Distribution Server/>'.'.
2017-02-07 14:55:34.976 The process could not execute 'sp_MSadd_replcmds' on '</Distribution Server/>'.
2017-02-07 14:55:34.976 Status: 0, code: 1007, text: 'Conversion failed when converting the nvarchar value '.' to data type int.'.
2017-02-07 14:55:34.991 Status: 4, code: 6001, text: 'Background thread for 'Command Loader' is exiting.'.
2017-02-07 14:55:34.991 Status: 0, code: 22020, text: 'Batches were not committed to the Distributor.'.
2017-02-07 14:55:35.007 Status: 0, code: 22037, text: 'The last step did not log any message!'.
2017-02-07 14:55:35.007 Disconnecting from OLE DB DISTOLE '</Distribution Server/>'
2017-02-07 14:55:43.010 Disconnecting from OLE DB Publisher '</Publisher Server/>'
2017-02-07 14:55:43.010 Disconnecting from OLE DB </Distribution Server/> '</Distribution Server/>'

I have been staring at this all day and going to pause to see if I am missing something obvious.

In another test, if the distribution database is local to the publication (i.e. publisher is also a distributor) this works...

If the distribution db is on a remote server I get the error on the log reader job.

Any advice is appreciated.


texts:
I have a query like this:

with xx as
              (
              SELECT [FilePath], ROW_NUMBER() over (partition by [FilePath]  order by [FilePath]) as RowNumber
              FROM [dbo].[foo] where (a = 'a1' and b = 'b1') or (a = 'a2' and b = 'b2')) delete xx where RowNumber>1

(a and b, together constitute composite non-clustered index.
Other non-clustered index is on a different single column)

Which is executing parallel from different instances, but the where clause will separate the data on which it runs without overlaps. Other than that, inserts are also running on it, which may or may not affect the result of this query.

Inserts are typical, like

insert into [dbo].[foo] values('a1', 'b1', 'c1', 'd1')

Now I've started receiving the following error message:

Transaction (Process ID 199) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

What can be the possible cause of deadlock here?
Is the order of taking the lock on the indexes while insert and while delete the reason?
Thanks in advance.


texts:
I am a newbie in databases. I read around and found out that it's probably not a great idea to use email address as primary key because string comparisons are slower which effects performance in complex joins and if an email changes I'd have to change all the foreign keys which requires a lot of effort.

But if my users table requires every user to have an email address and each of those email address should be unique, will adding a unique index on the email column suffice? Because afaik unique fields allow null values, whereas I require every user to have an email address, not allowing null values. Is there something I'm missing here? Or I'm suppose to make email column unique and make sure during data validation on the server that user does enter an email address so that every user has one?


texts: Recover Instance after Log File disk error

texts:
This is mainly theoretical, but I would like to have a documented list of options if this happens in the future.

Today we had a critical disk error on the SAN, meaning the disk holding the Transaction Log files for one of our production instances crashed and initially looked like it was dead. Obviously, down go the instance, databases, and therefore the applications running on it.

Our data center guys were busy working on the what, why and how's of the disk failure, meanwhile I was quickly coming up with a list of options for database recovery.

Ok, so the Data Center guys recovered the disk. It was a VPLEX error, rather than a physical hardware fault.

But what I found meanwhile was that I didn't have a great deal of options. The instance would not start, given that all log files for both Sys and User databases were out of reach. Would the instance have restarted if the Sys database log files were on a separate disk that was 'Up'

I could access the .mdf files, so I had the option of copying them over to another server, then attaching them with new log files on another volume. Either that or restoring the databases to another server\instance using our fairly resilient backups. Either option meant work for the App guys, because all the applications and associated services would need to re pointed.

I had another option of trashing the instance on the server and reinstalling it with the same instance name, then restoring all the databases from full ad log backups. Theoretically, this meant no work for the App team but had a serious time overhead for me (the only DBA).

Am I missing any options here? I only started this job recently, and its fair to say documentation here is limited. I've been busy this past few months putting together inventories of our SQL Estate, looking at patching/upgrade gaps etc, and getting involved in several projects. I think its fair to say that a documented disaster recovery plan for just this type of scenario is now at the top of our hierarchy's agenda.

Any help appreciated.


texts: Is it possible to run SQL Server as 'NT Service\MSSQLSERVER' and EXECUTE AS a domain account?

texts:
For clarity, I'm asking how to resolve this

without running SQL Server as a domain account and
without making sa the database owner.

In a nutshell
If I install SQL Server 2019 and go with the recommended default user account of NT Service\MSSQLSERVER, and then I connect and run EXECUTE AS LOGIN = [SOMEDOMAIN\someusername];, I get this:

Could not obtain information about Windows NT group/user 'SOMEDOMAIN\someusername', error code 0x5.

The problem goes away if I set the SQL Server service to run using a domain account, but I don't want to do this. I like using the recommended NT Service\MSSQLSERVER virtual account. I'm looking for a way to set this up that stays within the dev machine and doesn't require collaborating by setting up a domain account or by manually configuring AD to recognize each development instance of SQL Server.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#VA_Desc:

Use a MSA [managed service account] or virtual account when possible.

I'd prefer not to use an MSA because it requires setup outside the dev machine. So, is using a virtual account possible when I need to be able to do EXECUTE AS LOGIN = [SOMEDOMAIN\someusername];? What's the configuration I'm missing in order to achieve this? If I'm asking for something impossible, could you explain the conflict?
More details
EXECUTE AS LOGIN = serves as a useful test in SQL Server Management Studio, but here's what's really going on: I'm running software that uses Windows authentication to log into SQL Server and create a new database, including an essential stored procedure that has WITH EXECUTE AS SELF. SELF is the domain account used by Windows authentication when the software connected to SQL Server and created the database. Attempting to execute the stored procedure fails with the same error message that EXECUTE AS LOGIN = does. (If it's useful, I can explain why the software relies on a stored procedure having WITH EXECUTE AS SELF.)
I've seen similar questions asked before and get the answer, "just make the database owner sa instead of a domain account." I don't want to do this. It's a big hassle to have to maintain a password for sa and use it instead of Windows authentication every time I deal with this database.


texts:
How can we uninstall multiple instances of SQL Server 2008 without restarting the O/S?

SQL Server installer is failing with error:

  Server needs to restart after one instance uninstalled.

It is not practical for us to restart the server after each uninstall to uninstall every single instance.


texts: Find rows matching only values from a specific list with SQL

texts:
I have a table like this:

  Id    Status
  --    -----------
  1     Closed
  1     In progress
  2     Rejected
  3     Cancelled
  4     Rejected
  4     Closed
  5     In progress

I want to show rows that have the Status of closed, rejected or cancelled. However, I want to exclude the IDs that have a different status value, even if they also have any of those three.

So, for my example I would like the output to be this:

  Id    Status
  --    ---------
  2     Rejected
  3     Cancelled
  4     Rejected
  4     Closed

What should my query look like?


texts: How to order fields returned by a PIVOT query

texts:
I Have 3 tables names like companystructure,projectallocation,introducermaster

I have tried this

select *
from
(
select projectallocation.proAllocationID as Sno,
       introducermaster.intro_name as IntroLow,
       temp.intro_name as IntroTop,
       projectallocation.introtoplevelEmpid as TopEmp,
       (companystructure.csshortname) as Level,
       projectallocation.introlevelEmpid as EmpID
from projectallocation
inner join dbo.IntroducerMaster
ON dbo.introducermaster.empid=projectallocation.introLevelEmpid
inner join introducermaster as temp
on temp.empiD =projectallocation.introtopLevelEmpid
inner Join companyStructure
ON companyStructure.HLevel = projectallocation.introleveID
where projectallocation.projectID=1
group by IntroducerMaster.Intro_Name,
         temp.intro_name,
         companyStructure.CSShortName,
         projectallocation.proAllocationID,
         projectallocation.introlevelEmpid,
         projectallocation.introtoplevelEmpid
) b
PIVOT
(
    max(introlow) for level in(CH,ED,DIR,GM,AGM,BDM,SMM)
)PVT

Output
Sno         IntroTop                                           TopEmp                                             EmpID                                              CH                                                 ED                                                 DIR                                                GM                                                 AGM                                                BDM                                                SMM
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
177         NANDI RAMA                                         EMP00093                                           EMP00006                                           NULL                                               NULL                                               NULL                                               NULL                                               NULL                                               NULL                                               ALAGAPPAN
178         EDWART                                             EMP00031                                           EMP00012                                           NULL                                               NULL                                               NULL                                               NULL                                               NULL                                               NULL                                               B.MURUGESWARI

i want the empid will became last at the pivot table


texts: change MySQL commands to PostgreSQL in a bash script

texts:
I have a bash script to backup MySQL databases. I have pasted the relevant part below (ignore the variables).

The 2 lines I think I need to change to make this work in PostgreSQL are;

DBS="$(mysql --login-path=dbbkup -Bse 'show databases')"

and

$MYSQLDUMP --login-path=dbbkup --add-drop-database --single-transaction --triggers --routines --events --set-gtid-purged=OFF $db | $GZIP > $FILE

Can anyone advise as to how I can get the same functionality with PostgreSQL?

# get all database listing
DBS="$(mysql --login-path=dbbkup -Bse 'show databases')"

# start to dump database one by one
for db in $DBS
do
        DUMP="yes";
        if [ "$IGNOREDB" != "" ]; then
                for i in $IGNOREDB # Store all value of $IGNOREDB ON i
                do
                        if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then
                                DUMP="NO";         # SET value of DUMP to "no"
                                #echo "$i database is being ignored!";
                        fi
                done
        fi

        if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database
                FILE="$BACKUPDIR/$NOW-$db.sql.gz";
                echo "BACKING UP $db";
        $MYSQLDUMP --login-path=dbbkup --add-drop-database --single-transaction --triggers --routines --events --set-gtid-purged=OFF $db | $GZIP > $FILE
        fi
done


texts: Table Joined To Itself Appears Inefficient

texts:
My ultimate goal is to compare two rows (only two will exist) and for a specific type and determine if its rows values are greater than the other rows type values. The result I came up with seems to be unwieldy which leaves me to wonder if it can be done in a faster or more elegant way.

I have created sql fiddle here which demonstrates the issue.

Say we are shipping cargo and our main route of type 1 needs to be compared to the other route type 2. Each route has a Wave value and a Roll value which need to be compared independently. Note that other route types (except main, it is always uses) can be compared to Main of type value 1, but only two at a time.

CREATE TABLE Shipping
(
[RouteID]         [INT]  NOT NULL,
[WaveValue]       [INT] NOT NULL,
[RollValue]       [INT] NOT NULL

);

INSERT Shipping(RouteID, WaveValue, RollValue)
VALUES (1, 20, 2),  -- This is the main route
       (2, 10, 30); -- The other secondary route, (only 2 items ever in this table)

With the values currently in the table we expect that the MainsWave will be greater than the other's wave and the reverse for the Roll.

Here is the working sql, note that the true end result is only concerned with IsMainWaveGreater and IsMainRollGreater but all columns are shown for debug:

   SELECT Max1.RouteID
        , Max1.WaveValue
        , Max1.RollValue
        , MaxOther.RouteID
        , MaxOther.WaveValue
        , MaxOther.RollValue
  , IIF(Max1.RouteID = 1,
        IIF(Max1.WaveValue > MaxOther.WaveValue, 1, 0),
        IIF(Max1.WaveValue > MaxOther.WaveValue, 0, 1)) AS IsMainWaveGreater
  , IIF(Max1.RouteID = 1,
        IIF(Max1.RollValue > MaxOther.RollValue, 1, 0),
        IIF(Max1.RollValue > MaxOther.RollValue, 0, 1)) AS IsMainRollGreater
   FROM
    Shipping AS Max1
   INNER JOIN Shipping AS MaxOther ON Max1.RouteID > MaxOther.RouteID;

Since the row that could be read first and joined to the next may not be the Main route (remember always value 1 in RouteID for main), the sql has to have an IIF which checks on what type Max1 is and then swap the check/results accordingly.

Can this be optimized or even rewritten?

This is just one operation in a CTE process so other operations can be entertained.


texts:
As per https://devcenter.heroku.com/articles/heroku-postgres-legacy-plans the connection limit is 500

As per https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server "Generally, PostgreSQL on good hardware can support a few hundred connections"

What are the determining factors in this limit? #CPU cores? RAM? OS?


texts: MySQL: is commenting out `bind-address: 127.0.0.1` same as setting `bind-address: 0.0.0.0`?

texts:
in my.cnf file I have the following line which binds to localhost:

bind-address = 127.0.0.1

My question is: Is commenting out bind-address the same thing as bind-address = 0.0.0.0?

# bind-address = 127.0.0.1

same thing?

bind-address = 0.0.0.0


texts: TDE Encryption on database tripled size of backup files

texts: After enabling TDE Encryption my database backup file .bak is 3x larger than before. Is there a way to improve this? Is this due to compression not being as efficient on an encrypted file?


texts: Transaction Log Files Larger Than Data Files

texts:
I'm an accidental DBA and need help determining a course of action regarding transaction log files on several databases. I ran Brent Ozar's sp_blitz to see what areas of concern there might be on an instance of SQL Server 2012 and one of the things I saw was transaction log files that are bigger than the data file.  On one database the data file is at 3.33 GB and the log is 5.19 GB. The others have a ratio that's about the same.

The instructions for this scenario indicates that the log file might need to be shrunk, but only if the database is in Full recovery mode. This database is set to Simple recovery.

Should I shrink the log files or just leave them alone?

Thanks for your time.


texts: Isolate database performance on an instance

texts:
I have SQL Server 2014 Standard Edition installed on a machine with two different users and two catalogs, each of them accessed by a different user (each user is strictly tied to his catalog).

Is it possible that a time consuming operation on a catalog slows down operations on the other? How can I avoid that?

The physical server machine has four cores assigned, so I assume SQL Server can use more than one of them, but I have not configured anything explicitly.


texts: Ambiguous Error Message when running report

texts:
I have a database that is used to track container contents as they are shipped in and out of a facility.  I am attempting to create a report off of the table containing all of the cylinders.  At present, the table only contains 60,000 rows.  The report should pull all of the data and then group by:

RIS, Account, Yard, Row, Position

The details for each group will contain:

Cylinder, Gross Weight, Net Weight, Tare Weight, Unit of Measure, Material Type

There are three parameters on the report:  RIS, Material Type and Unit of Measure.  The first two parameters can contain multiple values while the Unit of Measure can only be one value at a time.  The query for this report is:

SELECT * FROM InventoryData WHERE RIS IN (@RIS) ADN [Material Type] IN (@DMT) AND [Unit of Measure]=@UM

When I preview the report and select pounds for unit of measure (which should pull about 80% of the rows, I get the following error:

  An error occurred during local report processing.  The server did not
  provide a meaningful reply; this might be caused by a contract
  mismatch, a premature session shutdown or an internal server error.

After researching, some sites suggested that parameter sniffing may be the issue so I created a stored procedure that takes table value parameters and changed the query using the process at http://gruffcode.com/2012/06/21/using-table-valued-parameters-with-sql-server-reporting-services/.  This setup still gives the same error.  Can anyone shed some light on this error or what to do about it?

The code that assigns the variables is as follows:

Public const strSingleQuote = "'"
Public Shared Function BuildListInserts(ByVal variableName As String, ByVal paramValues As Object()) As String
  Dim insertStatements As New System.Text.StringBuilder()
  For Each paramValue As Object In paramValues
    paramValue = Trim(paramValue)
    InsertStatements.AppendLine(String.Format("INSERT INTO {0} VALUES ({1}{2}{1})",
    variableName, strSingleQuote, paramValue))
  Next
  Return insertStatements.ToString()
End Function

The return value of the function above is assigned to the parameters @risInserts and @dmtInserts within the query.  The modified query looks like this:

exec('declare @risList stringlist ' + @risInserts +
'declare @dmtList stringlist ' + @dmtInserts +
' EXEC InventoryData @RIS=@risLIst, @DMT=@dmtList, @UnitOfMeasure=''' + @um + '''')

When the query is run while doing an SQL trace, the result on the SQL Server is:

declare @risList stringlist INSERT INTO @risList  VALUES ('GBD')
INSERT INTO @risList VALUES('GBD')
declare @dmtList stringlist  INSERT INTO @dmtList  VALUES ('D')
INSERT INTO @dmtList  VALUES ('E')
INSERT INTO @dmtList  VALUES ('Pu')
INSERT INTO @dmtList  VALUES ('U')
INSERT INTO @dmtList  VALUES ('N')
INSERT INTO @dmtList  VALUES ('Np')
INSERT INTO @dmtList  VALUES ('Th')
INSERT INTO @dmtList  VALUES ('C')
EXEC InventoryData @RIS=@risList, @DMT=@dmtList, @UnitOfMeasure='lb'

Which, when run in SSMS, produces the desired result set in about .75 seconds.

Yes, RIS and DMT can both be multiple values which is why I use "IN" instead of "="


texts: Oracle 11gR2 - Limits on Materialised View replication between databases

texts:
We are about to deploy a 3rd party product running on Oracle 11gR2. The product allows very generic text searches. We'd like to mitigate the performance impact of the searches.

Our current design is to create an OLTP database holding 32 days of data and a archive/reporting one holding up to 7 years. Then, we replicate data between them every 24 hours using Materialized Views without applying deletes. This isolates the impact of searches.

However, our database team has raised concerns that MV's are not the right solution as the 3rd party product creates 4700 tables and we'll be replicating approx. 0.5GB data per day.

Any view on the validity of the concerns?

(we are currently looking at alternatives - no archive, 7 years data in OLTP and partitioning to lessen the impact of search)

Thanks in Advance,
Matt


texts: Grant table privileges to a user connecting from any host

texts:
I have a user account - let's call it 'wordpress' - that I need to allow to access a few catalog tables in another e-commerce database on the same server. I've configured the user with three host masks it's allowed to connect from: 'localhost', the IP address of the web server, and the hostname of the web server. No problems there.

The 'wordpress' user also has full access to its own database, granted via the Schema Privileges section in MySQL Workbench. Here, it shows the host is '%', which is what I want, since I don't want to manage three duplicate sets of privileges for the same user. If I look in mysql.db, I see these privileges, with '%' in the Host column.

So now I want to grant SELECT permission on a handful of tables in another database - let's call it 'store'. So I try this:

GRANT SELECT ON store.catalog TO 'wordpress'@'%';

And I get 'Can't find any matching row in the user table', for the obvious reason that '%' isn't a host mask I've explicitly allowed a connection from for this particular user. So what's the proper syntax to grant a table privilege to a user from any of its allowed host masks? How is MySQL Workbench getting away with it for schema privileges? I don't have to manually insert rows into mysql.tables_priv, do I?

UPDATE: To clarify, here's what the current user/grant tables look like. I've anonymized some names, obviously. Note that the host in the schema privilege table is '%', but there aren't any users with that host. How do I get MySQL to let me do that with schema object grants? Preferably without mucking around directly in mysql.tables_priv, but I'll do it if it comes down to it.

mysql> SELECT user, host FROM mysql.user WHERE user = 'wordpress';
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| wordpress | 10.0.0.22 |
| wordpress | webserver |
| wordpress | localhost |
+-----------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT user, host, db, select_priv FROM mysql.db WHERE User = 'wordpress';
+-----------+------+----------------+-------------+
| user      | host | db             | select_priv |
+-----------+------+----------------+-------------+
| wordpress | %    | wordpress      | Y           |
| wordpress | %    | wordpress_test | Y           |
+-----------+------+----------------+-------------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'wordpress'@'localhost';
+---------------------------------------------------------------------------+
| Grants for wordpress@localhost                                            |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpress'@'localhost' IDENTIFIED BY PASSWORD '--' |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'wordpress'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'wordpress' on host '%'

Is MySQL Workbench doing something horribly undocumented with schema/object privileges? Just for kicks, I granted some table privileges to one of the specific user@host combinations, then updated mysql.tables_priv to change the host to '%'. After running FLUSH PRIVILEGES, it worked perfectly. Weird.