texts: How long will a temporary MEMORY table persist if I don't drop it (MySQL)

texts:
I'm using a recursive stored procedure in MySQL to generate a temporary table called id_list, but I must use the results of that procedure in a follow up select query, so I can't DROP the temporary table within the procedure...

BEGIN;

/* generates the temporary table of ID's */
CALL fetch_inheritance_groups('abc123',0);

/* uses the results of the stored procedure in the WHERE */
SELECT a.User_ID
FROM usr_relationships r
INNER JOIN usr_accts a ON a.User_ID = r.User_ID
WHERE r.Group_ID = 'abc123' OR r.Group_ID IN (SELECT * FROM id_list)
GROUP BY r.User_ID;

COMMIT;

When calling the procedure, the first value is the top ID of the branch I want, and the second is the tier which the procedure uses during recursions. Prior to the recursive loop it checks if tier = 0 and if it is it runs:

DROP TEMPORARY TABLE IF EXISTS id_list;
CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;

So my question is: If I don't DROP the temporary MEMORY table at the end of the procedure, or within my transaction, how long will that table persist in memory?  Is it automatically dropped once the session ends, or will it remain in memory as long as the connection is open?

**N.B. The obvious answer might be to drop the temp table prior to the commit statement, but lets assume for a moment that I can't do that.*

EDIT: To be a little more precise, what if persistent connections are employed, will the table persist through multiple requests?  So far it seems that it will and that we would need to explicitly remove the temp table to free up that resource.

UPDATE: Based on the advice from the commenters, I've found a way of adjusting my stored procedure so that I can utilize the TEMP MEMORY table, but be able to explicitly DROP it at the end...

Rather than just calling the stored procedure and using the remaining TEMP table to gather the results in the actual query, I've changed the CALL format to use a third OUT variable like so:

CALL fetch_inheritance_groups('abc123','0',@IDS);

... then within the stored procedure, I added a second IF tier = 0 at the very end with the following:

IF tier = 0
    THEN
    SELECT GROUP_CONCAT(DISTINCT iid SEPARATOR ',') FROM id_list INTO inherited_set;
    DROP TEMPORARY TABLE IF EXISTS id_list;
END IF;

So the result of the stored procedure is now a comma separated list of ID's that is compatible with FIND_IN_SET, and so the final query was modified so that:

WHERE r.Group_ID = 'abc123' OR r.Group_ID IN (SELECT * FROM id_list)

... is now ...

WHERE r.Group_ID = 'abc123' OR FIND_IN_SET(r.Group_ID,@IDS)

Voila!  Thanks to the commentors for your input, and for giving me the reason I needed to try a little harder :)


texts:
Many of my databases have fields defined as varchars.  This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem)

After working with databases for about 5 years, I've found I eventually run into problems with the limited nature of the varchar field and I have to modify my fields to store data as nvarchars.  After having to make another update to a table, converting a varchar field to an nvarchar, I just had the thought-- why are we still doing it this way?  I've long since made the mental decision to define all of new my text fields to nvarchar, instead of varchar, which is what I learned to do from my text books when I was in school 10 years ago.

It's 2011 and there was a new release of SQL Server last year.  Why do we continue to support a varchar datatype when we can/should instead be using nvarchar?

I know that it is often argued that nvarchars are "twice as large" as varchars, so storage space usage could be one arguement for maitaining varcars.

However, today's users could define their nvarchars to store the data as UTF-8 instead of the default UTF-16 if they want to save on storage space.  This would allow for 8-bit encoding if that is primarily desirable, while giving assurance that the rare 2-8 byte character that gets inserted into their DB would't break anything.

Am I missing something?  Is there a good reason why this hasn't changed over the past 15-20 years?


texts: Prevent updating table using procedure in different database

texts:
This is my scenario:

Windows User Gabe has db_owner access of database DB1.
Windows User Gabe has db_reader on database DB2.
When the user executes UPDATE DB2.dbo.Tbl1 SET X = 1, they get "The UPDATE permission was denied.." as expected.
when user executes a proc DB1.dbo.uspUpdateTable containing the same UPDATE statement above, it succeeds.

I thought this might be due to database chaining or trustworthy settings but they are not enabled.

Is this the expected behavior for db_owner privilege?? And is there a way to prevent this?

SQL Server 2017


texts:
running select datname FROM pg_database gives me a partial list. I'm missing there the db aa.
How is that be? How can I sort it?
all=# \dt aa.*
                List of relations
 schema |   name     | type  | owner
--------+------------+-------+--------
 aa     | aamember   | table | dkuser
 aa     | aaspmember | table | dkuser
 aa     | aamaster   | table | dkuser
(3 rows)

all=#
all=# select datname FROM pg_database;
   datname
--------------
 all
 dev
 padb_harvest
 template1
 template0
(5 rows)


texts: Multiple PostgreSQL running as primary server on pgpool

texts:
I'm facing a problem with my pgpool setup. Currently, this is my requirement based on the pgpool documentation here :

Server A  : Install with PostgreSQL database. Run as primary server

Server B  : Install with PostgreSQL database. Run as secondary server

Server C  : Install with pgpool and pgpoolAdmin. Run as web server.

The problem starts when I'm accessing the pgpoolAdmin through Apache web server.

Both of the PostgreSQL databases are running as primary servers. It is supposed to run one as standby and the other as primary, for when failover occurs. Maybe I've messed up something when configuring this set up, but I don't know what it is. Can someone help me with this?

EDIT : Below is my pgpool.conf file

master_slave_mode = on
master_slave_sub_mode = 'stream'
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
backend_socket_dir = '/tmp'
backend_hostname0 = '192.168.1.134'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/usr/local/pgsql/data'
backend_flag0= 'ALLOW_TO_FAILOVER'
backend_hostname1 = '192.168.1.136'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/usr/local/pgsql/data'
backend_flag1= 'ALLOW_TO_FAILOVER'


texts: In SQL Server is a FAST_FORWARD cursor DYNAMIC by default?

texts:
I've been reading the DECLARE CURSOR (Transact-SQL) Books Online article.  It states:

  FAST_FORWARD:
  Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled.

and

  FORWARD_ONLY:
  Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor.

Does this mean that a FAST_FORWARD cursor is a DYNAMIC cursor by default, unless I also specify that it's STATIC?


texts: SSIS identical packages deployed two different ways produce different results

texts:
We have two packages as part of one job. We copied these same packages from one SQL Server Integration Services Catalog SSISDB folder to another SQL Server in the same Integration Services Catalog SSISDB folder (same namespace). When running this job on the new server with the packages copied, the job runs but fails and also does not produce an error message (we have any error message logged).

When building and deploying from Visual Studio these same two packages to the same SSISDB folder and then running the job, the job runs as expected.

When deploying via the .ispac file, the script tasks we have error out with with

  ScriptTaskName: There was an exception while loading ScriptTask from
  XML: System.Exception: The Script Task "ST_0001a..." uses version 15.0
  script that is not supported in this release of Integration Services.
  To run the package, use the Script Task to create a new VSTA script.
  In most cases, scripts are converted automatically to use a supported
  version, when you open a SQL Server Integration Services package in
  %SQL_PRODUCT_SHORT_NAME% Integration Services. at
  MicrosoftSqlServer.Dts.Tasks.ScriptTaskScriptTaskLoadFromXML(XmlElement
  elemProj, IDTSInfoEvents events)

How come only the build and deploy way makes the packages work as intended?


texts: How do you set up a multi-node replica set in Mongodb? I don't know where to start. I'm trying to set up a replica set of three members in three virtual machines, i.e. there will be a total of three members, one for each virtual machine.


texts: Database design for surveys and experiments

texts:
I have only a bit of experience in working with databases, but rather theoretically during my studies and not so much practically. I was wondering if someone could help me figuring out how to design a good relational database for this data setup.

The setup is this. I have a set of predefined surveys that are used to evaluate subjects on certain aspects during some experiments. Each of these surveys is composed of a set of items. The items are combined to a set of factors, e.g. items 1, 4 and 8 build factor A, so the survey can be evaluated. The surveys are something like general templates that can be used by experimenters.

In an experiment each subject fills out the survey resulting in a value for each item of the survey. I don’t need to store the results for each subject but rather the mean across subjects for each item. Items are combined to factors in any combination, which is predefined by the survey, and for each factor a value can be calculated.

I want a database that contains all surveys and stores the information for each experiment where a survey is used, that means, the experiment should link to a particular survey and the survey should be linked to the items that compose it with their values, the factors with their values and which items compose which factors. Take into account that each survey can be used in multiple experiments.

The thing is, and this is where it gets complex, for some surveys I won't have the items but only the factors so I should be able to store the information on factors without relying on the items being there. Another thing is that usually the experimenters use the predefined items and factors of a survey. However, sometimes it happens that they use some additional items or factors, or they compose the factors differently. I want to be able to distinguish which items and factors were originally from the survey and which are added extra (or were changed).

So in summary I want to store this information:

Experiment
Survey used on the experiment
Original items of the survey and their respective values (if
available)
Original factors from the survey, their respective values and the
items that compose them
Extra items from the experiment that are not linked to the survey (if
available)
Extra (or changed) factors from the experiment that are not linked to
the survey

Take into account that one factor could be composed with original items and extra items. Any combination could happen. An extra factor could be defined with original and extra items too.

Any ideas what could be a good efficient way to define the structure of this database? I tried around already, but I always have circular references somewhere. And the result seems to be very complex.


texts:
I am having only MYI files is it possible to restore my database with this files.?

If yes means tell me the procedure please


texts: Create junction table where one referenced table has a composite key as PK

texts:
I have a category table which currently has only a title field (PK)

CREATE TABLE `category` (
  `title` varchar(20),
  PRIMARY KEY (`title`)
);

and a product table with a composite key as a primary key

CREATE TABLE `product` (
  `title` varchar(50),
  `vendor_name` varchar(50),
  `price` decimal(10,2) unsigned NOT NULL,
  PRIMARY KEY (`title`, `vendor_name`),
  CONSTRAINT `fk_product_vendor_name` FOREIGN KEY (`vendor_name`) REFERENCES `vendor` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
);

A product can have many categories so I created a junction table. I'm struggling with the composite key from the product table. In the junction table I need to reference the combination of the product title and the vendor name foreign key. I started with this

CREATE TABLE `category_product` (
  ``/* product composite PK */
  `category_title` varchar(20),
  PRIMARY KEY (``/* product composite PK */,`category_title`),
  KEY `fk_category_product_category_title` (`category_title`),
  KEY ``/* product composite PK */
  CONSTRAINT `fk_category_product_category_title` FOREIGN KEY (`category_title`) REFERENCES `category` (`title`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT ``/* product composite PK */ ON DELETE CASCADE ON UPDATE CASCADE
);

How do I handle the composite key from the product table?

Thanks in advance


texts: How To Grant A User Just To Create A Table In Existing Database But Not To See Other Existing Tables In It

texts: We have dealed with an outsource company to install an application for use of our accounting department. Now, they want to access our existing database to install their application but i don't want them to see our current database inside. I mean like tables, stored procedures etc.. How to grant them just to create a table but not to see other structures of our database?


texts: hosting images on own database or 3rd party

texts:
I'm working on an app where the user can upload 1-6 pictures.

I've never handled images&databases before so I wanted to know if there is anything in particular I should be careful of.

Also, I've read that images are data intensive, and was wondering whether or not I should host them on the same database as my other data. Or if I should create an entirely different database for them.

thanks


texts: Updates since the previous commit point Vs updates prior to the commit point

texts:
The quote below is from An Introduction to Database Systems, 8th edition, C J Date.

I am confused since I think "all updates since the previous commit point" is the same as "updates Prior to the commit point". Am I wrong?

  ...all database updates made by the executing program since the previous commit point are committed; that is, they are made permanent, in the sense that they are guaranteed to be recorded in the database. Prior to the commit point , all such updates must be regarded as tentative only-tentative...


texts: How to migrate huge MySQL databases one by one to new server?

texts:
We have several servers with huge databases (on average 100-200GB per DB up to ~500GB for the biggest ones). All of them have a slave that is used for backups.

Now I need to migrate those with minimal or no downtime and preferably one DB at a time. First thing that came to my mind is doing a backup including master-data and setup replication. With the size of these databases dumping and reimporting takes quite a while, so I was looking into using innobackupex. That works and I can filter for one DB. However I still get all directories for all databases (even though they're empty) and an ibdata1 file of close to 2GB.

My main concern is, that this will only work for the first database. I can't do that for a second one and add that to a running slave as this would conflict with already existing data. Am I missing something or is it only possible to setup a slave for ALL databases of an existing MySQL server at once?

The servers are a mix of MySQL 5.1 and 5.5 and the new hosts should start with 5.7. Does the multi-source replication feature help with my approach?


texts: MySQL replication master-slave with 2 or more users in master

texts:
I've just configured MySQL replication master-slave.
I have into master instance 3 user: root (with all privileges and grant), admin(with all privileges) and slaveUser(with replication salve privilege).
I've configured slave server to, and if I modify (with root user) in my master instance some resource this are replicated into slave.
Well, if I modify a resource, in master instance, with admin user this resource isn't modified into slave and I've this error

  Error 'Can't find any matching row in the user table' on query.
  Default database: 'my_db'. Query: 'GRANT ALL PRIVILEGES ON . TO
  'admin'@'%''

I've configured this on the same machine with 2 docker instances.

Any suggestions??

EDIT

This is my users on MASTER

+-----------+-----------+
| user      | host      |
+-----------+-----------+
| admin     | %         |
| root      | %         |
| userSlave | %         |
| mysql.sys | localhost |
+-----------+-----------+

and this is users on SLAVE

+-----------+-----------+
| user      | host      |
+-----------+-----------+
| root      | %         |
| mysql.sys | localhost |
+-----------+-----------+

Which kind of permission does admin need to write replication on slave??

EDIT add SHOW SLAVE STATUS

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.3
                  Master_User: userSlave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 317959980
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 5834
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1133
                   Last_Error: Error 'Can't find any matching row in the user table' on query. Default database: 'mydb'. Query: 'GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 5621
              Relay_Log_Space: 317961680
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1133
               Last_SQL_Error: Error 'Can't find any matching row in the user table' on query. Default database: 'mydb'. Query: 'GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%''
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 100
                  Master_UUID: 8527301e-9765-11e5-a957-0242ac110003
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 151215 20:26:46
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
1 row in set (0.00 sec)

ERROR:
No query specified


texts: Getting unfamiliar error when using terminal command to update database

texts:
I'm a bit in over my head when it comes to using and managing large databases. I run a small startup that uses a PostGreSQL and pgAdmin to administer and query the database, which is housed on Herkou.

Typically, I need to 'pull' and updated version of the database in order for the pgAdmin platform to query. This was all set up and explained to me a few months ago, and since then, things have worked well.

Recently, however, whenever I pull the using the following terminal command:

heroku pg:pull <db name> <local db name> --app <app name>

I get the following error:

heroku-cli: Pulling swimming-****-**** ---> <app name>
createdb: could not connect to database template1: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

I have absolutely no idea how to fix this, and the person that set it up is long gone.


texts:
Creating an index always has trade-offs.  Whenever you insert, update or delete, it has to perform those operations on the index as well.  This is all pretty commonly known.

But with the advent of filtered indexes, I'm wondering if there is a minimum (or maximum) row count that you should consider too much of a performance hit.

For example, if you have 100,000 rows in a table that gets, say, 75% read operations, would it be wise to add a filtered index that only covers 500 rows?  Or 100 rows?  What if the filter covers 85,000 of those rows?

I think the performance implications of adding a filtered index to a read-heavy table are pretty clear.  I'm just wondering if there's a natural limit to this performance.

Another way of looking at this question:  If 90% of your updates aren't fitting within the filter, is the performance degraded less than it would be for a non-filtered index?


texts:
Box is running only mysql and CPU is at 400% constantly. I can't seem to find anything that would be causing it. I've changed a few values in my.cnf based on some research, but had no luck reducing the CPU usage.

my.cnf as follows:

[mysqld]
basedir=/opt/bitnami/mysql
datadir="/opt/bitnami/mysql/data"
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
tmpdir=/opt/bitnami/mysql/tmp
#bind-address=127.0.0.1
skip-name-resolve=1
skip-external-locking
key_buffer_size = 64M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 64M
thread_cache_size = 40
join_buffer_size = 1M
query_cache_limit=0
innodb_lru_scan_depth=100
max_connections = 100
open_files_limit=30000
# Try number of CPU's x 2 for thread_concurrency
thread_concurrency = 16


texts: Index not used with `=any()` but used with `in`

texts:
Table t has two indexes:

create table t (a int, b int);
create type int_pair as (a int, b int);
create index t_row_idx on t (((a,b)::int_pair));
create index t_a_b_idx on t (a,b);

insert into t (a,b)
select i, i
from generate_series(1, 100000) g(i)
;

No index is used with the any operator:

explain analyze
select *
from t
where (a,b) = any(array[(1,1),(1,2)])
;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=1000 width=8) (actual time=0.042..126.789 rows=1 loops=1)
   Filter: (ROW(a, b) = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
   Rows Removed by Filter: 99999
 Planning time: 0.122 ms
 Execution time: 126.836 ms

But one of them is used with the in operator:

explain analyze
select *
from t
where (a,b) in ((1,1),(1,2))
;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Only Scan using t_a_b_idx on t  (cost=0.29..8.32 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=1)
   Index Cond: (a = 1)
   Filter: ((b = 1) OR (b = 2))
   Heap Fetches: 1
 Planning time: 0.161 ms
 Execution time: 0.066 ms

It uses the record index if the record is cast to the correct type:

explain analyze
select *
from t
where (a,b)::int_pair = any(array[row(1,1),row(1,2)])
;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Index Scan using t_row_idx on t  (cost=0.42..12.87 rows=2 width=8) (actual time=0.106..0.126 rows=1 loops=1)
   Index Cond: (ROW(a, b)::int_pair = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
 Planning time: 0.208 ms
 Execution time: 0.203 ms

Why doesn't the planner use the non record index for the any operator as it uses it for the in operator?


texts:
I have a table with 3 columns, participant_id, question, answer. for each participant, there will be five question.

How do fetch a report like against each participant each question as a column header and answer as value. So in report for each participant there will be one entry in report?

expected output:
group using participant_id and each question should be the column header and custtusponding answer will be the column value.


texts:
I am trying to carry forward the previous state of data wherever data is not available. For example, I have following data;

Month
Order
Amount

1
Item1
100

1
Item2
200

1
Item3
300

2
NULL
NULL

3
Item4
100

4
NULL
NULL

5
NULL
NULL

6
Item1
100

So in the above example I want to use 1st month's information to be carry forwarded as it is for 2nd month. Also since 3rd month's information is available then use it for the month 4 and 5. Hence the output will be following:

Month
Order
Amount

1
Item1
100

1
Item2
200

1
Item3
300

2
Item1
100

2
Item2
200

2
Item3
300

3
Item4
100

4
Item4
100

5
Item4
100

6
Item1
100


texts: Trigger for add discount on total price calculated from other table - oracle 11g

texts:
I have the following schema:

CREATE TABLE ORDER(
    OrderId   NUMBER PRIMARY KEY,
    Value     NUMBER);

CREATE TABLE ORDERITEM(
    OrderItemId     NUMBER PRIMARY KEY,
    OrderId         NUMBER,
    ISBN            NUMBER,
    Quantity        NUMBER,
    Price           NUMBER,
    CONSTRAINT FK_OrderID
    FOREIGN KEY (OrderId)
    REFERENCES ORDER (OrderId));

In the value field I store the order total price [sum(quantity*price)]

I need to create a trigger for add 5% discount from the total price and store it in the value field in order table, if the customer buy more than 6 books.

I need the trigger for Insert/Update/Delete.

Edit:
Here is my current trigger for filling the value field:

create or replace trigger val
before insert or update or delete on orderitem
for each row
begin
    if inserting then
        Update order
        set value = ((
                    select sum(price * quantity)
                    from orderitem
                    where orderid = :NEW.orderid
                    group by :New.orderid)
                    + (:New.price * :New.quantity))
        where orderid = :New.orderid;
    elsif updating then
        Update order set value = (value + (:New.price * :New.quantity))
        where orderid = :New.orderid;
        Update order set value = (value - (:old.price * :old.quantity))
        where orderid = :old.orderid;
    elsif deleting then
        Update order set value = (value - (:old.price * :old.quantity))
        where orderid = :old.orderid;
    end if;
end;


texts: Reporting Service 2014 Service Pack 1 Failing to work with error - wrong database version

texts:
Tonight we upgraded our SQL Server 2012 (SP2+CU6) cluster with SQL Server 2014 Service Pack 1.

On that cluster nodes, we have Native mode Reporting Services scale-out configuration.

After in-place upgrade to SQL Server 2014 SP1, we got error from Reporting Services web page:

  The version of the report server database is either in a format that
  is not valid, or it cannot be read. The found version is '163'. The
  expected version is '162'.

So there go question - why we have this error when we have upgraded to the most recent version of SQL Server 2014 and Service Pack 1?


texts: Can't update "CO2" to "CO₂" in table row

texts:
Given this table:

CREATE TABLE test (
    id INT NOT NULL,
    description NVARCHAR(100) COLLATE Modern_Spanish_CI_AS NOT NULL
);
INSERT INTO test (id, description) VALUES (1, 'CO2');

I've realised I can't fix a typographic issue:

SELECT * FROM test WHERE id = 1;
UPDATE test SET description = 'CO₂' WHERE id = 1;
SELECT * FROM test WHERE id = 1;

because the update matches but has no effect:

id          description
----------- -----------
1           CO2

(1 affected rows)

(1 affected rows)

id          description
----------- -----------
1           CO2

(1 affected rows)

It's as if SQL Server determines that, since ₂ is obviously just a tiny 2, the final value won't change so it's not worth changing it.

Could someone shed some light on this and maybe suggest a workaround (other than updating to an intermediary value)?


texts: Why does creating database snapshot fail unless user is db_owner or sysadmin? MSDN suggests it should work

texts:
MSDN suggests that CREATE DATABASE permissions are all that is necessary to create a snapshot.

  Any user who can create a database can create a database
  snapshot; however, to create a snapshot of a mirror database, you must
  be a member of the sysadmin fixed server role.

This seems not to be the case.  When trying to create a snapshot via a user with this suggested minimal permission requirement, I get the following error:

Msg 1817, Level 14, State 1, Line 4
Only the owner of database "example" or the system administrator can create a database snapshot on it.

As I'd like to set up an automated process to create snapshots on a schedule, I obviously would like to avoid giving it db_owner or sysadmin rights.

Is it possible to set up as suggested by MSDN or are their docs wrong?

Many thanks for any suggestions.

/*as sysadmin*/

USE [master];
CREATE DATABASE example;
CREATE LOGIN testuser WITH PASSWORD='xxx';
CREATE USER testuser FOR LOGIN testuser WITH DEFAULT_SCHEMA=dbo;
go

USE [example];
CREATE USER testuser FOR LOGIN testuser WITH DEFAULT_SCHEMA=dbo;
go

USE [master];
GRANT CREATE DATABASE to testuser;
go

/*as testuser*/

USE [master];
create database example_snapshot_01
on (
name = example,
filename = 'C:\...\example_snapshot_01.ss'
)
as snapshot of example;
go

/*
Error:
    Msg 1817, Level 14, State 1, Line 4
    Only the owner of database "example" or the system administrator can create a database snapshot on it.
*/


texts: Find the failing component given an error message

texts:
I have an error message:

"component "Custodian" (121)" failed validation and returned validation status "VS_NEEDSNEWMETADATA"

The language seems clear enough.  A component called Custodian (121) has failed because the metedata is bad.  I'm guessing that a table or view or something has been changed and the component no longer works with it.

If I'm correct in that assumption then that just leaves me with the problem of identifying what the actual component in my IDE is.  There doesn't seem to be anything called Custodian (121).

How to I match up this error with an actual component in the IDE?


texts: MySQL vs. MariaDB Binary Logging Difference

texts:
I've been doing some testing with both MySQL and MariaDB and I've noticed a big difference with regards to the growth of binary logs - I can't find out much about this online.

I have a test database, and I have written a script which simulates transactional activity (selects, updates, inserts, deletes etc) with the aim of doing some simple load testing and later, for testing point in time recovery procedures.

What I've noticed is that on MySQL running my automated script for half an hour causes about 400MB of binary logs.

When I do the same thing on MariaDB, I only get around 2MB of binary logs.

All settings are the same, ie.

Max Binlog Size: 100MB
Buffer Pool: 2GB max

MySQL is running v 5.7 and Mariadb is running 10.3 - both are running in Docker on my local laptop.

I've also noticed that MariaDB is a little bit slicker performance wise, I get more query throughput in half an hour than I do with MySQL.

Obviously the big difference here is with binary log size. The smaller size of MariaDB means I can roll forward on the binary log much quicker for point in time recovery than I can on MySQL.

Is there any documented evidence out there to support this behaviour? Is MariaDB more efficient than MySQL when looking at binary logs and recovery?

Are there some hidden/non-obvious variables I should be checking that may be having a big impact on this testing?

Any help understanding this would be great.


texts:
Is it possible to have more than one count in a SQL statement such as:

SELECT
    student_id,
    (
        COUNT (ATTENDANCE_ID)
        from attendance
        where attendance_id IN ('ABSENT')
    ),
    (
        COUNT (ATTENDANCE_ID)
        from attendance
        where attendance_id IN ('PRESENT')
    )
    FROM attendance;


texts: Tricky selection of grouped rows, selecting based on values of two distinct but related rows

texts:
Say I have a table with the following information (sorted here for display purposes)
taskid   |  reference   |  centreid  |  date_out
---------------------------------------------------
10001    | 'IX#001323'  |         1  | '2012-09-28'
10022    | 'IX#001323'  |         2  | '2012-10-04'
10032    | 'IX#001544'  |         1  | '2012-10-09'
10046    | 'IX#001666'  |         1  | '2012-10-10'
10056    | 'IX#001666'  |         3  | '2012-10-13'
10078    | 'IX#002100'  |         2  | '2012-10-23'
10098    | 'IX#002100'  |         1  | '2012-11-01'

I want to select the groups of rows that have the same reference where the first occurrence belonged to a particular centreid, but where the second occurrence was within a particular date range. For example, I want to find all taskids that have the same reference where the second date falls in October but the first centreid is 1, the results should be:
taskid   |  reference   |  centreid  |  date_out
---------------------------------------------------
10001    | 'IX#001323'  |         1  | '2012-09-28'
10022    | 'IX#001323'  |         2  | '2012-10-04'
10046    | 'IX#001666'  |         1  | '2012-10-10'
10056    | 'IX#001666'  |         3  | '2012-10-13'

It's possible that there are more than two taskids with the same reference, in which case,  I am always interested in the last date and the second to last centreid (when ordered by date_out). Each time I write a query it ends up being very complex and still not returning exactly the rows I want.
I have:
SELECT taskid
FROM tasks t1
WHERE (EXISTS (SELECT 1
               FROM tasks t2
               WHERE t1.taskid <> t2.taskd
                 AND t1.reference = t2.reference))
 AND
    -- STUCK
    -- Unsure how to select amongst remaining rows where max(date) between two dates

Thanks in advance for any help. I'm using PostgreSQL 9.1 if that makes any difference...
Update (2012-11-10)
I have used the answer provided by Erwin, but the self join in CTE d took a long time to execute, so I split it up into two different CTEs. This provided an overwhelming performance improvement. Erwin's original answer executed in around 120 seconds (choking on CTE d) whereas the below executes in just under 2 seconds.
... snip ...
,d AS (
   SELECT reference, grp
   FROM   c
   WHERE  c.rn = 1 AND c.date_out BETWEEN '2012-10-01' AND '2012-10-31'
   )
,e AS (
   SELECT reference, grp
   FROM   c
   WHERE  c.rn = 2 AND centreid = 1
   )
SELECT b.taskid, b.reference, b.centreid, b.date_out
FROM   b
JOIN   d USING (reference, grp)
JOIN   e USING (reference, grp)
ORDER  BY reference, date_out


texts:
I'm trying to create a SQL code in which I want to return JSON similar to this format:

{
  "type": "FeatureCollection",
  "features": [
   {
     "type": "Feature",
      "properties": {
      "id": 1,
      "zone": "A",
      "nb_stats": 136,
      "occupation": 0
   },...]}

So far I manage to code a script that output a certain part of the JSON format, but I'm enable to generate the properties field with the values.

Here my SQL code:

Select json_build_object(
'type', 'FeatureCollection',
'features',json_build_array(
  json_build_object(
  'type','Feature',
'properties', (select row_to_json(properties) from (Select id, zone, nb_stats,occupation from public.zone) properties))))

By executing this code, I'm always getting a error saying that the subQuery return more than line. Error 21000.

Please note that if I only execute the select row_to_json(properties) I am able to output the result all the values of the table:

select row_to_json(properties) from (Select id, zone, nb_stats,occupation from public.zone) properties

"{"id":1,"zone":"A","nb_stats":136,"occupation":0}"


texts: Field Terminator Problem in Format Files

texts:
I have a data file in txt format and I used to load it with Bulk Insert in the following command:

Bulk insert [table1]
FROM 'data.txt'
WITH
  (
     FIELDTERMINATOR ='|',
     ROWTERMINATOR ='0x0a',
  )

The code works well. However, while I was trying to use a format file with the bulk insert to load the file, I encountered some problems specifying the field terminator for the last row.

Here is the specification for the last row in the format file:

21  SQLCHAR  0  20  "0x0a"  21  misc_costs       SQL_Latin1_General_CP1_CI_AS

With "0x0a" as the terminator, I get the following error:

  Bulk load data conversion error (truncation) for row 2, column 1 (var1).

With "\n",  data can be loaded, but "|" is also loaded in the last column. For example, I get "0.5|" instead of "0.5".

"|\n","0x0a","|0x0a",'\r\n' returns the same error as "|"

  Bulk load data conversion error (truncation) for row 2, column 1 (var1).

I'm using sql-server 2016, windows 10 64bit.

Can anyone help with this? Thanks in advance for your help!

Jason


texts: When using XE Profiler in SSMS 17.3, the 'Database Name' column shows as either NULL or an empty string

texts:
I'm just starting to use SSMS 17.3 - and one of the main things I was looking forward to trialing was XE Profiler.

However, when you start a session (either Standard or TSQ) and add the Database Name column, it will either show as blank or NULL.

Adding a Database ID column, will show the correct DB_ID - so it can still be filtered - however, you can't see the name of which database a statement is being executed against.


texts: Set mongodb's `maxIndexBuildMemoryUsageMegabytes` setting in conf file

texts:
In the mongodb documentation for the configuration options it says that I can specify how much RAM index building will consume:

  maxIndexBuildMemoryUsageMegabytes:   Limits the amount of memory
  that simultaneous foreground index builds on one collection may
  consume for the duration of the builds.

This setting is set after starting the server by executing an administrative command called  setParameter.
This is done like this:

mongo --eval "db.adminCommand({setParameter: 1, maxIndexBuildMemoryUsageMegabytes: 700})"

I would like to specify this amount when the server starts up by putting it in the Configuration File Options. The documentation does not seem to include this option.

How can I specify this setting from the mongod.conf file?


texts:
I have a par file that is using JOINS in QUERY clause. But it is not working.

directory=expdp
DUMPFILE=Test_SIT_Query_1table_%U.dmp
encryption_password=oracle
parallel=8
logfile=TEST_SIT_Query_table.log
compression=all
cluster=N
METRICS=Y
query=IAO.VERIFIC:"SELECT /*+Parallel(8)*/ distinct A.* FROM IAO.VERIFIC A JOIN IAO.ELIJ B ON A.RACE_ID = B.RACE_ID WHERE B.CREATE_DT > SYSDATE - 30"
tables=IAO.VERIFIC

Below is the error I am getting.

ORA-31693: Table data object "IAO"."VERIFIC" failed to load/unload and is being skipped due to error:
ORA-00942: table or view does not exist

Could someone help what could be missing in the above par file.


texts: SQL Server Job Cannot Run SSIS Package Error 0xC00160AE Cluster Environment

texts:
I have a 2 machine SQL Cluster and a separate SSIS/SSRS Server. I'm running SQL Server 2014 and Integration Services 12. I'm getting Error 0xC00160AE when trying to run a job from my SQL cluster that executes a package from SSIS on the separate server.

I followed the directions on the below link and gave permissions to NNU\srv_sql to run the SSIS service but I am continuing to get this error. I'm at a loss of what steps I need to take next.

Supporting Link: SISS error 0xc00160ae access is denied from SO

Error Message:

  Executed as user: NNU\srv_sql.  Microsoft (R) SQL Server Execute
  Package Utility  Version 12.0.4100.1 for 64-bit  Copyright (C)
  Microsoft Corporation. All rights reserved.

  Could not load package "\File System\ImpData" because of error
  0xC00160AE.

  Description: Connecting to the Integration Services
  service on the computer "ACSLCRSP010" failed with the following error:
  "Access is denied."

  By default <c/> only administrators have access
  to the Integration Services service. On Windows Vista and later <c/>
  the process must be running with administrative privileges in order to
  connect to the Integration Services service. See the help topic for
  information on how to configure access to the service.

  Source:Started:  2:40:08 PM  Finished: 2:40:08 PM  Elapsed:  0.047 seconds.

  The package could not be loaded.  The step failed.


texts: sp_Blitz: Poison wait detected and too much free memory

texts:
We have a website for SolarWinds Orion which utilizes SQL and we've had many reports of slowness issues with the website. I ran sp_Blitz on the SQL box and I'm seeing these errors:

Poison Wait Detected: RESOURCE_SEMAPHORE
98:07:56:03 of this wait have been recorded. This wait often indicates killer performance problems.

Poison Wait Detected: RESOURCE_SEMAPHORE_QUERY_COMPILE
33:06:03:08 of this wait have been recorded. This wait often indicates killer performance problems.

Too Much Free Memory
7GB of free memory inside SQL Server's buffer pool, which is 12GB. You would think lots of free memory would be good, but check out the URL for more information.

The server has been provisioned 16GB of RAM and SQL server is configured to use 13GB of maximum server memory, 1024KB per query. Is this server over provisioned? I feel like its a memory misconfiguration but any assistance or insight would be appreciated.


texts:
I have homework where I need to create simple web interface and a DB normalized to 3NF. I've chosen ISP client selfcare as my database purpose. Web interface is no problem, the normalized DB is. After a really hard time I've come up with the model below.

I'd like to know if it's in 3NF?! If no, I'd be glad if you explained why.


texts: How long will an already started online Backup with RMAN take?

texts:
I need to query know how long an online backup which is already started with RMAN will still take.

Any recommendation for a query?


texts:
I was reading this article that is about Google BigQuery:

Exploring a powerful SQL pattern: ARRAY_AGG, STRUCT and UNNEST

They use a couple of functions that I am trying to figure out what the Oracle equivalents are, if they even exist.
I am digging through the Oracle documentation and I found something similar to ARRAY_AGG in Oracle called LISTAGG.
What I have not been able to find are the idiomatic equivalents of STRUCT and UNNEST.
Are there things that do the same thing as STRUCT and UNNEST or will I have to write my own?


texts: FlushCache messages appearing in log at specific times

texts:
We've been having a lot of database performance issues lately, and I've been trying to see if I can figure out why. We don't have a DBA (I'm a software developer) so I'm sort of just winging it, and much of what I find online reads like a foreign language to me.

We've been restarted SQL Server every morning because that's the only way it's operational during the working day. I noticed that every morning around 5 am we are started to get this message every two minutes in the log:

  FlushCache: cleaned up 11848 bufs with 7432 writes in 97168 ms
  (avoided 8139 new dirty bufs) for db 9:0

  last target outstanding: 4, avgWriteLatency 32

  average throughput: 0.72 MB/sec, I/O saturation:
  11635, context switches 18849

The numbers differ each time of course, but it's the same message over and over in that pattern until I restart the server. I'm not sure how to interpret this, I've been trying to Google about it and all I've gathered is that it means there might be something wrong with I/O and that something is taking longer than it's supposed to. We recently switched to using SSD so I didn't think it should be a write problem.

Could anyone shed some light on this?


texts: ERROR 2013 (HY000): Lost connection to MySQL server during query when running query on table

texts:
My system crashed last night. At first, I couldn't get mysql up and running. To resolve the issue, I did the following:

mv /var/lib/mysql/ib_logfile* ~

I could then start mysql. However, when I run the following simple query on a particular table, I get an error:

mysql> select * from posts;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Queries on other tables work fine without an error. This is an innoDB table and database. There is nothing particularly large about this table with about 14,000 rows. The error happens instantly upon executing it.


texts: MS Access: error 3045 while linking to back-end

texts:
In our environment multiple users each work with their own copied front-end, with tables linked to a single networked back-end (both files are .mdb).

Each user has permissions to read and write to the location of BE, and both FE and BE are set to "shared access".

Recently a single user started getting error: 3045 "Could Not Use Database; File Already in Use" while trying to open forms using linked data.

The same error appears while trying to re-link the tables using the manager. All other users can still normally work.

Tried restarting his workstation, removing local files and re-copying the FE and it didn't work.

There is no .ldb file left after all users turn off the FE, compacting after that and restarting the FE of the unlucky user didn't help either.

Said user is working on AC2010, but some others are working on AC2007 and AC2003.

Please help!

edit: BE is stored on Windows Server 2003.


texts: User-shared queries: Dynamic SQL vs. SQLCMD

texts:
I have to refactor and document a number of foo.sql queries which will be shared by a team of DB tech support (for customer configurations and things like that). There are types of tickets that come regularly where each customer have their own servers and databases, but otherwise the schema are the same across the board.
Stored procedures are not an option at the current time. I'm debating whether to use dynamic or SQLCMD, I have not used much of either as I am a bit new at SQL Server.
SQLCMD scripting I feel definitely "looks" cleaner to me, and easier to read and make small changes to the queries as needed, but also forces the user to enable SQLCMD mode. Dynamic is more difficult as the syntax highlighting is loss due to query being written using string manipulation.
These are being edited and ran using Management Studio 2012, SQL version 2008R2. What are some of the pros/cons of either method, or some of the SQL Server "best practices" on one method or the other? Is one of them "safer" than the other?
Dynamic example:
declare @ServerName varchar(50) = 'REDACTED';
declare @DatabaseName varchar(50) = 'REDACTED';
declare @OrderIdsSeparatedByCommas varchar(max) = '597336, 595764, 594594';

declare @sql_OrderCheckQuery varchar(max) = ('
use {@DatabaseName};
select
    -- stuff
from
    {@ServerName}.{@DatabaseName}.[dbo].[client_orders]
        as "Order"
    inner join {@ServerName}.{@DatabaseName}.[dbo].[vendor_client_orders]
        as "VendOrder" on "Order".o_id = "VendOrder".vco_oid
where "VendOrder".vco_oid in ({@OrderIdsSeparatedByCommas});
');
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@ServerName}',   quotename(@ServerName)   );
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@DatabaseName}', quotename(@DatabaseName) );
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@OrderIdsSeparatedByCommas}', @OrderIdsSeparatedByCommas );
print   (@sql_OrderCheckQuery); -- For debugging purposes.
execute (@sql_OrderCheckQuery);
SQLCMD example:
:setvar ServerName "[REDACTED]";
:setvar DatabaseName "[REDACTED]";
:setvar OrderIdsSeparatedByCommas "597336, 595764, 594594"

use $(DatabaseName)
select
    --stuff
from
    $(ServerName).$(DatabaseName).[dbo].[client_orders]
        as "Order"
    inner join $(ServerName).$(DatabaseName).[dbo].[vendor_client_orders]
        as "VendOrder" on "Order".o_id = "VendOrder".vco_oid
where "VendOrder".vco_oid in ($(OrderIdsSeparatedByCommas));


texts: What does it mean? ORA-06502: PL/SQL: numeric or value error: character string buffer too small

texts:
Q 1:

Whenever an Oracle SP wants to return error code/message, it fails returning below exeption:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "DB.GET_COST", line 244
ORA-01403: no data found
ORA-06512: at line 1".

I am calling it from MS SQL via a linked server as follows:

declare
     @id1   int
    ,@id2   varchar(8)
    ,@code  varchar(10)
    ,@curr  varchar(4)
    ,@ed    datetime
    ,@return_status int
    ,@error_code    varchar(10)
    ,@error_msg     varchar
    ,@landed_cost   numeric(14,5)

begin try
    exec ('BEGIN db.get_cost(?,?,?,?,?,?,?,?,?); END;'
         ,@code
         ,@id1
         ,@id2
         ,@curr
         ,@ed
         ,@return_status    output
         ,@error_code       output
         ,@error_msg        output
         ,@cost             output
    ) AT ORASRV
end try
begin catch
    select
          @return_status    as return_status
         ,@error_code       as error_code
         ,@error_msg        as error_msg
end catch

It seems that the input parameters work fine, as their values are such that when the SP is returning a correct result, everything is working. Only the IN parameter values could cause the SP to return an error code, when the error above is thrown.

The declaration on the Oracle side is as follows:

PROCEDURE get_cost (
     code   IN  VARCHAR2(10 BYTE),
     id1    IN  NUMBER(7,0),
     id2    IN  VARCHAR2(8 BYTE),
     curr   IN  VARCHAR2(4 BYTE),
     ed     IN  DATE,
     return_status  OUT NUMBER
     error_code     OUT VARCHAR2(10 BYTE)
     error_msg      OUT VARCHAR2,
     cost   OUT NUMBER(14,5)
)

Q 2:

To add insult to injury, after throwing the above exception 3 times, and working fine once, the script then dies with the following MS SQL error message:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

It dies even if I exclude the 1st 3 calls that cause the original error and just call the SP successfully once.


texts: Add parent_id dynamically based on another field

texts:
I have a version table using a field to track order:

id, owner_id, order, version
5, 1, 1, 1.1
6, 1, 4, 1.2
7, 1, 7, 1.2.1
8, 1, 10, 1.3

I want to ALTER TABLE version ADD COLUMN parent_id and have each record reference their parent and then drop the order column. The parent_id is the next lowest in order (which may not be sequential) resulting in:

id, owner_id, parent_id, version
5, 1, NULL, 1.1
6, 1, 5, 1.2
7, 1, 6, 1.2.1
8, 1, 7, 1.3

Can I write a query that will update the parent_id for me? Or must it be done programmatically?


texts: move old data to another table when try to insert data


texts: How can I import a database dump (.sql) using pgAdmin without the PSQL Console plugin?

texts:
I am trying to import a database dump (.sql) using pgAdmin. I want to do it using only the GUI, not the PSQL Console (where I can import the sql dump using \i /path/to/yourfile.sql on Unix or \i C:\\yourfile.sql on Windows). Is that possible? If so, how?

The new import tool seems to be able to only import data in a table:

The restore function doesn't seem to accept .sql:

Lastly, I cannot load the .sql I'm trying to import in the query builder, as it is too large (25 GB).


texts: Looking for a query that eliminates near-repeats

texts:
I have a database with four rows: county_a, county_b, flow_a_to_b, and flow_b_to_a. Essentially, the data is repeating in other rows, but the values are switched around. Here's an example:

Entry 1: Baltimore County, Baltimore City, 10, 1

Entry 2: Baltimore City, Baltimore County, 1, 10

These entries are technically different but they give me the same information. What kind of query could I write to eliminate the second value while keeping the first?


texts: Index not being used on array elements in JSONB column

texts:
I have a table testuser like this:
CREATE TABLE testuser (id int, data jsonb);

where the following snippet shows the format of the data column:
INSERT INTO testuser (id, data) VALUES (1, '{
  "name": "peter",
  "permissions": [
    {
      "type": "ARTICLE",
      "aggregateReference": "ae8b9fed-f99b-498e-b9ab-b87b1eec94a7"
    }
  ]
}'::jsonb);

Now, I've created a gin index on the permissions field like this:
CREATE INDEX test_user_permission_ix ON testuser USING gin((data -> 'permissions'));

but when I try to generate some additional rows, and run a query, the index is not being used:
SET enable_seqscan=false;
INSERT INTO testuser SELECT * FROM generate_series(1,100);
EXPLAIN ANALYZE SELECT * FROM testuser WHERE data @> '{"permissions" : [{"type":"ARTICLE"}]}';
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Seq Scan on testuser  (cost=10000000000.00..10000000170.01 rows=10 width=141) (actual time=26.040..26.453 rows=1 loops=1)
   Filter: (data @> '{"permissions": [{"type": "ARTICLE"}]}'::jsonb)
   Rows Removed by Filter: 10000
 Planning Time: 0.051 ms
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 0.327 ms, Inlining 3.541 ms, Optimization 16.740 ms, Emission 5.626 ms, Total 26.233 ms
 Execution Time: 26.861 ms
(9 rows)

I can see in the pg_indexes table that the type of the permissions field seem to be ::text, but I'm not sure if this affects anything:
 schemaname | tablename |        indexname        | tablespace |                                              indexdef
------------+-----------+-------------------------+------------+-----------------------------------------------------------------------------------------------------
 public     | testuser  | test_user_permission_ix |            | CREATE INDEX test_user_permission_ix ON public.testuser USING gin (((data -> 'permissions'::text)))

Any suggestions on why the index is not being used would be very appreciated!


texts: How does Checkpoint know which point to recover from in PostgreSQL?

texts:
I'm trying to better understand Checkpoint internals, specifically in PostgreSQL.

My current understanding is that Crash Recovery will always start from the latest REDO location that is marked when
a Checkpoint starts. Data in all WAL records before the REDO location are guaranteed to be on disk (in data files), and those WAL records are no longer needed.

I'm having a hard time understanding the following:

1) How is the REDO location determined exactly. When a Checkpoint starts, does it literally say "REDO location is the current WAL record + 1"?

2) When we do determine the REDO location, how is it that we are guaranteed that all WAL records before this are no longer needed?

Specifically, how is the following scenario handled:

Checkpoint 1 runs and writes out dirty page A to disk.
Dirty page A then has new rows inserted
Checkpoint 1 finishes
Checkpoint 2 starts and determines REDO location.

How can Checkpoint 2 be sure that all previous data is written to disk if pages can be dirtied after Checkpoint 1 flushes them, is the gist of the question.


texts: how to Import MySql Dump file in MySQLWorkBench

texts:
I have a MySQL Dump file for few objects of a database and I have to import only one table from that file in MYSQL Work Bench.

My question is
How can I import a dump file in MySQL Work Bench for specified table or for all tables?


texts:
We just inherited a legacy Tempdb database with Multiple MDF files. Tempdb1 is the primary.

Example:

Tempdb1.MDF
Tempdb2.MDF
Tempdb3.MDF

(1) Was tempdb2.mdf and tempdb3.MDF being ignored the whole time?

(2) Or Is is true naming extensions don't matter?  I can call 2 and 3, .mydat, or .datafile, etc.  And they are still inferred as secondary?

(3) However, for best practice should I label 2/3 with .ndf?


texts: What joins are available in Informix 9.2?

texts:
I'm testing some queries with an old version of Informix (9.2.1) and can't seem to execute even the most basic queries containing RIGHT OUTER JOIN or FULL OUTER JOIN. Tried several variations (RIGHT JOIN, OUTER JOIN or just OUTER), but without any success. INNER JOIN and LEFT JOIN work fine.

Does anyone know the correct syntax for outer/right joins in older versions of Informix, or are these joins just not possible?


texts:
I will be getting a SAN with a battery backed write cache for a new Postgres data warehouse and am not sure what settings to apply to make proper use of it. If the cache is enabled in the SAN is that enough or do I need to set wal_sync_method, synchronous_commit or others?

The warehouse will be handling 5-10 million row inserts a day once the current data sources are turned on. This will most likely grow in the future.

We will be using Postrges 9.3 and most likely Centos 6.4.

Thanks in advance.


texts: Changing a materialized view column's length after a change in the base table?

texts:
We changed the underlying table column size (length). So the materialized view which is built on that table throws an error now since the MV column still has the old size.

This is an Oracle 11gR2 database.

How can I change the MV column's length to match the table's new column size without dropping the MV and recreating it?


texts:
In my production environment I have two database Servers know as DBN1 and DBN2. I have made a cluster between two and DBN1 is my primary database server. Now I used Always on availability feature on both servers and databases were synchronized.

Today I am encountering an issue. There is a small question mark icon coming on the DBN1 which is shown by DBN2 always on availability properties. Moreover it is also not showing that it is a primary database. Why this is happening ?

This is my primary server output & follow the arrow

This is my secondary server output & follow the arrow


texts: Query system specs (cpu, memory, data disks, free space) using T-SQL

texts:
Is there an ability to query below system specifications using T-SQL?

cpu (show number of processors / cores / frequency)
memory (show total amount of RAM on a machine; show allocated RAM; show really used RAM)
data disks (show total number of disks, their volume, free space available)
show Windows edition and version


texts: Online resource for writing Access SQL statements

texts: I am looking for an online resource for writing Access SQL statements. I write queries using SQL because the UI puts limitations on what can be queried. I have been using w3schools for reference, however MS-Access sometimes has slightly different syntax, so when I run the query I get an error. Then I end up wasting time trying to figure out what I did wrong only to find out that Access does it differently.


texts: Migrating from MongoDB Atlas to self managed Mongo (EC2) server

texts:
There is an abundance of info about migrating TO Atlas but not too much I could find specifically about going the other direction.

I am migrating a collection which is 250GB in size.
I have tried using mongodump from the remote EC2 server connecting to the Mongo Atlas instance however presumably due to the large collection size this keeps failing.
I have also read that mongodump is not great for DBs of this size but I could be wrong.

I was able to download a backup .tar.gz file from Atlas and move this to the the remove server. The file contents are below.

Is it possible to restore these in to a fresh mongo setup on the EC2, or will these only work for the environment they were taken from?

Here are the files available in the backup:


texts: Unable to revoke permissions for 'NT AUTHORITY\SYSTEM' on SQL Server 2016

texts:
I have tried everything I know to do and spent several hours Googling without success. I want to revoke the SHUTDOWN and CONTROL SERVER permissions for the login NT AUTHORITY\SYSTEM.

When I run this

EXECUTE AS LOGIN = 'NT AUTHORITY\SYSTEM'
SELECT * FROM fn_my_permissions(NULL,NULL)
REVERT

...the output is:

Things I have tried:

Deleting the Login entirely,
Executing the following (with the different permissions listed):

USE master;
REVOKE SHUTDOWN FROM "NT AUTHORITY\SYSTEM";
GO

Even after I delete the login, and execute the fn_my_permissions SP, it still lists the permissions from the image above.

Any help I could get on this would be great.


texts:
We have an SSRS 2014 already in production and we want now use a secure site by linking it to a certificate (URL like "https://..."). So now the URL is fully qualified domain name(FQDN).

We can access correctly to the report manager with this https URL. We have to register to access with the Windows account.

The bug we have is that when we navigate, sometimes, we pass to an http page. Each time that we pass from an https to an http page, the browser (IE) ask to register again.

For example, here we are on a https page. When I click right on a report -> manage:

I arrive on this page:

If I clic on a tab on the left pane (for example in Security), SSRS ask me to reconnect and jump to an http page. This is a real problem for us.

Any suggestions?


texts: Application with "configurable" columns : how to model this

texts:
I'd like to do a status list. I have 1..n projects. Each project contains 0..n tasks, for which I'd like to report a status, next phase, priority, etc...
For each project, I'd like to setup which kind of column I want to use, for instance :

Project A : task ID, name, long description, next phase (date), ontime (boolean)
Project B : task ID, name, next phase (date), priority (limited set of values)

I also want to be able to configure the use of a field for a given project. In my example, I want to display next phase (a date) using a different format for instance. Some fields may be computed values (eg : late = true if today() > next phase()

I though of the following model :

one table for projects (with general metadata about the project)
one table with available column types
one link table between the project and the column types, with attributes on the configuration of the column (in my example, the date pattern)...(I'm no so happy about this part, as I would need to extend the table every time I add a new time with new configuration possibilities)
one task table, with a foreign key on projects, a foreign key on column types, and one column for the value

eg : the task on project B (12, "Write specifications", 12.12.12, "High") would be represented with 4 lines :
B, 1 (assume it's PK for ID column type), 12
B, 2 (assume it's PK for description column type), "Write specifications"
B, 3 (assume it's PK for date column type), 12.12.12
B, 4 (assume it's PK for priority column type), High

I'm not so convinced...do you have any suggestion ?
Maybe I should not model the column types in the DB ?

my biggest issue is to model the fact I have an undetermined number of attribute for a task in the project, and that 2 projects may have a different table size


texts:
Hello i want to make unique key as a foreign key but i got an error  i already checked this question

mY member Table

 CREATE TABLE IF NOT EXISTS `member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `contact` varchar(100) NOT NULL,
  `facebook` varchar(100) NOT NULL,
  `cnic` varchar(100) NOT NULL,
  `expertise` varchar(100) NOT NULL,
  `com_code` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB

and my profile image tabl

create table profileimage(
imageid int primary key not null,
username varchar(100) foreign key references member(username)
ON DELETE CASCADE
 ON UPDATE CASCADE,
imagepath varchar(255) not null
);

and error i faced
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'foreign key references member(username) ON DELETE CASCADE ON UPDATE CASCADE,' at line 6


texts:
Below are my two tables :

I am trying to write a query that joins these 2 tables AND returns the Source_Title and Destination_Title for the given Source ID of Result Map table.

I have tried written the query, but I can only solve one part of the puzzle not the whole thing.

SELECT Title as SourceTitle
FROM Result Map AS RM
INNER JOIN Table_Result TR ON RM.SourceId = TR.Id

The above query gives me the title for the source, but not the Destination title.


texts:
I have created an extended event as below. I want to know how to query the historical data using T-SQL to see the issues at particular timestamps. The trace is running as of now

CREATE EVENT SESSION [PerQuerymonitor] ON SERVER
ADD EVENT sqlos.wait_info(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlos.wait_info_external(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.disk_log_read(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_statement_starting(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text))
ADD TARGET package0.event_file
(SET filename=N'C:\SQLDatabase\PerQuerymonitor.xel',
max_file_size=(204800))
WITH (MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Appreciate your kind help.


texts: Guarding against CTE infinite loops in JOIN condition considered bad practice?

texts:
In docs, and pretty much everywhere else on the net, to guard against loops in recursive CTEs people use cycle column. The problem with this solution (as opposed to doing the ANY(path) check in join condition directly) is that the loop is detected in the iteration after which it's first encountered.

Is there any reason to favor the first approach as opposed to the second? Is it because you explicitly want that duplicate child row, for some reason, or is there some hidden side effect of the second approach I'm not seeing?

CREATE TEMPORARY TABLE folders (id INT, parent INT) ON COMMIT DROP;
INSERT INTO folders
    (id, parent)
VALUES
    (1, 1);

cycle guard as a column in parent

WITH RECURSIVE tree (id, path, cycle) AS (
    SELECT
        id,
        ARRAY[id],
        FALSE
    FROM folders
    WHERE id = 1

    UNION ALL

    SELECT
        c.id,
        tree.path || c.id,
        c.id = ANY(tree.path)
    FROM folders c
    INNER JOIN tree
        ON tree.id = c.parent
    WHERE
        NOT cycle
)
SELECT * FROM tree

results in duplicate row (unless we explicitly deduplicate):

1    "{1}"    false
1    "{1,1}"  true

or as part of the join condition directly

WITH RECURSIVE tree (id, path) AS (
    SELECT
        id,
        ARRAY[id]
    FROM folders
    WHERE id = 1

    UNION ALL

    SELECT
        c.id,
        tree.path || c.id
    FROM folders c
    INNER JOIN tree
        ON
            tree.id = c.parent AND
            NOT c.id = ANY(tree.path)
)
SELECT * FROM tree

which does not result in duplicate rows (+ one less iteration):

1    "{1}"

Edit: one possible benefit of solution (1) I'm seeing is when you need to know that there was a loop in the query.


texts: Oracle 12c - Slow query to tables ALL_CONS_COLUMNS and ALL_CONSTRAINTS

texts:
We have successfully upgraded Oracle 11g to Oracle 12c. So far it's been going well. But We noticed one problem, queries made for Oracle's constraint tables (ALL_CONS_COLUMNS and ALL_CONSTRAINTS) are really slow compared to 11g.

When We used 11g, there was no problem at all. The query finished in less than one second but in 12c it is at least 4 times slower. It is affecting all applications We have and even though there's a workaround (application side), ideally, You'd expect the same performance between 11g and 12c.

We have also tried to measure the query cost using Toad. On 12c, the cost is much smaller than 11g. Could this be affecting the query time? Is there anything I can try to solve this problem?

PS : Any other queries made for another tables are okay. Performance wise, there's little to no difference between 11g and 12c.

Any feedback is welcomed. Thank you very much.


texts: mysql query builder inefficiently builds a query

texts:
I have a track and a waypoint tables. The track table is also connected to a bunch more tables that are irrelevent. I wish to get all tracks, where at least one of their waypoints is in specified bounds. The tracks should have all their waypoints, even ones outside the bounds. My query looks like this:

select <all columns>
from track inner join waypoint point1 on point1.track_id = track.id
      left outer join other table on track.other_table_id = other_table.id
           <a few more tables outer joined>
where track_id in(
   select distinct track_id
   from waypoint point2
   where MBRContains(<POlYGON HERE>, point2)
)

the efficient way to do this query would be first build a table of all track_ids from the subquery, then connect them to table entries, then left outer join other variables, and then inner join the waypoints from point1 table.

Instead, MySQL first gets a table of all tracks, then outer joins, then does a lookup by both track_id and mbrcontains function, and then joins the inner waypoints.

How can I tell mysql to optimize the query?


texts: Can you create a clustered DB in Azure using Windows 2003 and SQL Server 2005?

texts:
We need to replicate an environment in Azure, running a clustered DB. The current physical environment is running on Windows 2003 and MS SQL Server 2005 R2.

We know that there's no OS/DB support but that it's possible to install these versions are run there.

However, how do you do the clustering side? There's no 'shared disk' as such. I believe in newer versions you use something called SIOS datakeeper that manages replication for you (for example, in a SAP set up: https://docs.microsoft.com/en-us/azure/virtual-machines/workloads/sap/high-availability-guide). Can you use this on Windows 2003, with SQL 2005, in Azure?

Thanks
Ross


texts:
I've got Docker Compose cluster and one of the containers is Oracle 12c. There is schema.sql file to initialize the db. I would like my application to wait until the db executes all the sql. How can I do it in an automatic fashion with bash?

Thank you very much for any suggestions!


texts: sql server management studio 2008 sucks up with memory yielding .net runtime failure

texts:
PROBLEM
sql server management studio 2008 sucks up with memory yielding a .net runtime error. It crashes with an empty message in an error-dialog but there can be found a little eventlog entry, reading:

<EventData>
  <Data>.NET Runtime version 2.0.50727.5446 - Fatal error in the execution engine (7269AECA) (0).</Data>
</EventData>

Original Eventlog Entry (German):

<EventData>
  <Data>.NET Runtime version 2.0.50727.5446 - Schwerwiegender Fehler im Ausführungsmodul (7269AECA) (0).</Data>
</EventData>

RECREATING THE ERROR

After about every 10 Million-th Insert-Statement Management Studio 2008 executed in a loop against a remote instance of SQL Server 2005, the 4GB of memory on my dev-workstation are up which leads to the stated error above. Why the hell i'm executing insert-statements in a loop? - Since SQL Server doesn't allow one to set the actual TIMESTAMP of a DB manually, dummy-data-manipulation-statements are needed to help one out (the TIMESTAMP-manipulation-lock is no bad idea but on a dev-environment sometimes needed).

CREATE TABLE __DUMMY (I INT, TS TIMESTAMP)
DECLARE @FROM INT
DECLARE @TO INT
SET @FROM = 666
SET @TO = 666666666
WHILE (@FROM < @TO)
BEGIN
    INSERT INTO __DUMMY (I) VALUES(@FROM)
    SET @FROM = @FROM +1
END

On another session to SQL Server I truncate the __DUMMY-table from time to time:

SELECT @@DBTS, CONVERT(INT, @@DBTS), 'SERVER'
UNION
SELECT CONVERT(TIMESTAMP, 666666666), 666666666, 'CLIENT'

EXEC sp_spaceused '__DUMMY'
TRUNCATE TABLE __DUMMY

--writes    ~10.000 Tuple/Second
--allocates ~100.000 Byte/10.000 Tuple
--      ==> ~10 Byte/Tuple (are "roughly" made up of: INT = 4 BYTE, TIMESTAMP = 8 BYTE)
--insert/truncate is ~100.000 times more performant than doing a insert/delete in the loop or a permanent update on one tuple (haven't tried indices yet)

See http://www.flickr.com/photos/74806161@N03/6732847259/in/photostream for image showing sucker-process-descriptions
and http://www.flickr.com/photos/74806161@N03/6732846981/in/photostream for image showing the memoryconsumption of the sucker-process

Anyone any Ideas?


texts: unixODBC - [IM002][unixODBC][Driver Manager] Data source name not found and no default driver specified

texts:
Folks,

We installed unixODBC-2.3.7 on Aix 7.1 to connect to Oracle 11g. After setup odbc.ini and odbcinst.ini files and tried to connect with isql, we had the following error:

# isql -v ORACLE
[IM002][unixODBC][Driver Manager]Data source name not found and no default driver specified
[ISQL]ERROR: Could not SQLConnect

Before execute the isql command, we ran the following export commands:

export ODBCSYSINI=/usr/local/etc
export PATH=$PATH:/usr/local/bin
export TNS_ADMIN=/u01/app/oracle/product/11.2.0.4/client_1/network/admin
export ODBCINI=/usr/local/etc/odbc.ini
export LIBPATH=/u01/app/oracle/product/11.2.0.4/client_1/lib:/usr/local/lib:/u01/app/oracle/product/11.2.0.4/client_1
export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/client_1/lib:/usr/local/lib:/u01/app/oracle/product/11.2.0.4/client_1

And odbcinst -j command too:

unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /usr/local/etc/odbc.ini
SQLULEN Size.......: 4
SQLLEN Size........: 4
SQLSETPOSIROW Size.: 2
Illegal instruction(coredump)

Our /usr/local/etc/odbc.ini file is:

[ODBC Data Sources]
ORACLE =  Oracle ODBC Test

[ORACLE]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle11gdriver
DSN = ORACLE
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = ORAODBC.customer.com
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID = user
Password = pass
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F

And our /usr/local/etc/odbcinst.ini is

[Oracle11gdriver]
Description     = Oracle ODBC driver for Oracle 11g
Driver          = /u01/app/oracle/product/11.2.0.4/client_1/lib/libsqora.so

Finally, our tnsnames.ora is:

ORAODBC =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = customer.com.br)(PORT = 1521))
   )
   (CONNECT_DATA =
      (SERVICE_NAME = ORAODBC.customer.com)
   )
 )

Any idea what is going on?


texts: How to verify data dumps between two MS SQL and MySQL

texts:
I'm trying to verify data exported from MSSQL and imported into MySQL is 100% accurate using MD5 hash. On Linux, I'm using md5sum and WinMD5 on Windows. The encoding is the same: UTF8. I tried both UNICODE and ANSI, but the results were the same.

When I visually compare the data in the export files or the hashes of individual cell values, it's 100% accurate. However, comparing the two files results in different hashes.  Anyone know why the hashes would be different?

Your comments are much appreciated.

UPDATE:
The data is exported from MSSQL using BCP into a text (.txt) file. MySQL data is exported using: SELECT * INTO OUTFILE.... into a text file. Both use "%^&" as the field delimiter,"!@#$" as the row delimter, and escape using "\".


texts: what is difference between RDBMS and NoSql on basis of how information is store in them?

texts:
I am searching web to understand the underlying concept of how actually information is stored in RDBMS and Nosql databases and found this:

  In the world of database technology, there are two main types of
  databases: SQL and NoSQL—or, relational databases and non-relational
  databases. The difference speaks to how they’re built, the type of
  information they store, and how they store it. Relational databases
  are structured, like phone books that store phone numbers and
  addresses. Non-relational databases are document-oriented and
  distributed, like file folders that hold everything from a person’s
  address and phone number to their Facebook likes and online shopping
  preferences.

Here i can understand that in SQL based databases data is stored in form address and data and in NoSql everything is stored in one document.

Please correct me if I am wrong.

Can someone explain this with help of example.


texts:
We are setting up a PostgreSQL 9.1 database at work. There are no classified data in the database, but we want to know who is doing what. We are using LDAP authentification on the network, so we would like to use that for the database as well, but according to the documentation, all users anyhow need to be defined as database users.

We could of course do a "dump" of users to the database, but we would prefer if it could be possible to define users in the database as soon as they have authenticated in LDAP. This would not be too difficult using a wrapper script, but is it somehow possible to do this directly? (Most users log in using psql.)


texts: Return ResultSet with column names from functions

texts:
I have the following postgresql Function:

CREATE OR REPLACE FUNCTION readMessage(messageFor INT, qid INT = NULL, ctxt INT = NULL, messageFrom INT = NULL, byTimeStamp BOOLEAN = FALSE)
RETURNS SETOF message
AS $$
DECLARE
    sql varchar;
BEGIN
    sql := 'SELECT * FROM message WHERE (receiver_id IS NULL OR receiver_id = $1)';
    IF qid IS NOT NULL THEN
        sql := sql || ' AND queue_id = $2';
    END IF;
    IF ctxt IS NOT NULL THEN
        sql := sql || ' AND context = $3';
    ELSE
        sql := sql || ' AND context IS NULL';
    END IF;

    IF $4 IS NOT NULL THEN
        sql := sql || ' AND sender_id = $4';
    END IF;

    sql := sql || ' ORDER BY';
    IF byTimeStamp THEN
        sql := sql || ' arrive_time ASC';
    ELSE
        sql := sql || ' priority DESC';
    END IF;

    sql := sql || ' LIMIT 1';
    RETURN QUERY EXECUTE sql
    USING messageFor, qid, ctxt, messageFrom;
END;
$$
LANGUAGE plpgsql;

Now, the above function works fine but the resultSet does not contain column names (for example: a simple SELECT statement returns results with column names). I need to be able to access the result of the above function in Java code as:

Connection con;
PreparedStatement ps = con.prepareStatement("select readMessage(1, 1, 1, 1, true))";
ResultSet rs = ps.executeQuery();
rs.getInt(<column-name>)

instead of

rs.getInt(<column-index>)

for more readable code. How can I modify my postgresql FUNCTION to achieve this?


texts: Approaches for handling nvarchar sources to data warehouse

texts:
Situation is this...our data warehouse doesn't need nvarchar and is built using all non-unicode datatypes.  But all new source systems are all coming in with unicode (utf8 Oracle or nvarchar in sqlserver).

If we leave the source nvarchar to our staging then we get implicit datatype conversions if we have to join or compare to our EDW during loads. Plus it just takes up excess storage that is unnecessary.  Sounds great to stay true the source and not risk losing characters in conversion but the reality is we need to optimize storage and our load processes and that means not using unicode anytime we can avoid it.

Thoughts?


texts: One Complex Stored Procedure Causes Estimated Cost Exception

texts:
In SQL Server, I'm getting the following error "The query has been canceled because the estimated cost of this query (5822) exceeds the configured threshold of 300. Contact the system administrator."

This is the result of an execution of a stored procedure, which is pretty complex.  I haven't run into this for other stored procedures, only this one.  Is it possible to change the query cost for this one procedure somehow?  Can I do that in the stored procedure itself upon execution?  Or do I have to define this on the server only?

I'm using ADO.NET command to execute the stored procedure.

Thanks.


texts: Can two transactions lock rows in the same table in parallel?

texts:
I am trying to grasp the concept of MySQL locks and came across this section in the docs: https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html:

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.

That leads me to the conclusion that two transactions can never lock rows in the same table in parallel. Let me explain it on the following example:
Assume I have the following table:
CREATE TABLE t1 (id INT, PRIMARY KEY (id)) ENGINE = InnoDB;

Further, lets assume I have 7 rows inserted. This is the balanced tree of the primary key:

Now assume we lock row with id 3:
SELECT * FROM t1 WHERE id=3 FOR UPDATE

From the above paragrph

A locking read [..] set record locks on every index record that is scanned in the processing of the SQL statement.

I would guess that there is an exclusive lock on row 3,5,10. Is that right?
If that would be the case, then
SELECT * FROM t1 WHERE id=100 FOR UPDATE

would try to set an exclusive lock on row 10, because it scanned in the process to find 100. But that means, it has to wait for the other transaction to be finished.
This implies, any transaction that locks a row in table t1 will always first lock row with id 10. Thus, its not possible that two transactions lock two different rows in parallel.
My questions are:

Is that actually true?
How can I actually see the balanced index tree, so I can do some local testing?


texts: Capturing SQL calls sent to a remote server from application

texts:
First of all, I'm not well versed in SQL anything at all. Closest I've ever needed to get was storing and retrieving data from a local SQLite db.
In essence I think I have a simple problem but it's hard to orient yourself when everything is new.
My main tool at work is an ERP software, which is basically a front end to an SQL db.
Problem I have with it is that it's very clumsy and doesn't allow automation of even the most basic tasks.
What I want to do, is bypass the front-end completely and interact directly with the db to automate most of my tasks with python.
I can connect to the database just fine from python environment, but the schema is gigantic, there's no way I'll be able to find whatever it is I might be looking for.
So I need to capture the call front-end sends when I click a button (telling it to display specific set of data) to use that call as a guide.
Basically, how can I, an SQL noob, capture calls that a desktop application sends to a remote server?
Edit 1: My job is mostly analytical, so all of my automation will be for retrieval, analysis and visualization. I'm not very likely to mess anything up.
Edit 2: Tried running a Profiler and got the message:
"In order to run a trace against SQL Server you must be a member of sysadmin fixed server role or have the ALTER TRACE permission."
I'm not a sysadmin, don't have an alter trace permission and reeeally don't feel like asking for it :D


texts: How can I setup some kind of buffering database with mariadb

texts:
I have a primary database on a linux box run mariadb, but it uses >100 watts of power which reduces the UPS runtime.  Also I need to periodically reboot the server for updates and maintenance.
I want to have an intermediate database on the raspberry Pi 4 also mariadb.
So if the main server is off, it logs it locally(raspberry pi) on disk so if the power is lost the data is not.
I would prefer to have it attempt to transfer the data as soon as it comes in, but 15 minutes intervals is ok. After successful transfer the records should be deleted from the Pi. It needs to verify the records transferred correctly and not hope for the best. If the primary database fails, oh well I guess I will try again in 15 minutes and hope for the best. It will keep trying every 15 minutes until it succeeds. Even if it takes a week.
So I can setup a local mariadb easy enough, but how can I have it automatically transfer the data to the primary database and delete the records from itself.
Also the table has an auto incrementing field that needs to be ignored on the raspberry Pi as the primary server has it own auto incrementing field and that is the master.
I thought of some kind of data replication, but I don't want 2 copies of the data.


texts: Is there any impact to changing IP address of MySQL Server?

texts:
We are changing the network and the IP addresses will be changing.
Will there be any impact to MySQL running on the server?

I don't use the IP address to reference the server.  Instead I use the server name.

For example:

Server          IP
MYPROD01        10.1.3.30 -> becomes -> 192.1.30.90


texts: How to use msdb.dbo.backupset and dbatools to gather all of the historical backup size information for all instances in an environment

texts:
I have too many servers to manage to want to do this manually on each server so I'd like to somehow automate this using the dbatools module and the below query for gathering the backupset information.
  WITH BackupsSize AS(
  SELECT TOP 1000
         rn = ROW_NUMBER() OVER (ORDER BY DATEPART(year,[backup_start_date]) ASC, DATEPART(month,[backup_start_date]) ASC)
    , [Year]  = DATEPART(year,[backup_start_date])
    , [Month] = DATEPART(month,[backup_start_date])
    , [Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([backup_size]/1024/1024/1024),4))
    , [Compressed Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([compressed_backup_size]/1024/1024/1024),4))
FROM
    msdb.dbo.backupset
WHERE
    [database_name] = N'A1000370D1'
AND [type] = 'D'
AND backup_start_date BETWEEN DATEADD(mm, - 13, GETDATE()) AND GETDATE()
GROUP BY
    [database_name]
    , DATEPART(yyyy,[backup_start_date])
    , DATEPART(mm, [backup_start_date])
ORDER BY [Year],[Month])
--SECTION 1 END

--SECTION 2 BEGIN
SELECT
   b.Year,
   b.Month,
   b.[Backup Size GB],
   0 AS deltaNormal,
   b.[Compressed Backup Size GB],
   0 AS deltaCompressed
FROM BackupsSize b
WHERE b.rn = 1
UNION
SELECT
   b.Year,
   b.Month,
   b.[Backup Size GB],
   b.[Backup Size GB] - d.[Backup Size GB] AS deltaNormal,
   b.[Compressed Backup Size GB],
   b.[Compressed Backup Size GB] - d.[Compressed Backup Size GB] AS deltaCompressed
FROM BackupsSize b
CROSS APPLY (
   SELECT bs.[Backup Size GB],bs.[Compressed Backup Size GB]
   FROM BackupsSize bs
   WHERE bs.rn = b.rn - 1
) AS d

I have a list of servers sitting in table on a db and plan to load a $sqlinstance variable with my list of instances using invoke-dbaquery.
What I'm not sure about and need help with is how to use the above SQL script to gather the historical backup data for all databases on all servers in one go. How can I loop through each database while also looping through my list of instances?


texts: SSMS - Can not bulk load because the file could not be opened

texts:
We have a user who get this error message every time bulk load using network location it throws error below.

"Cannot bulk load because the file could not be opened. Operating system error code 3(failed to retrieve text for this error. Reason: 15105)."

If users logs on the remote server and runs same query it works fine
FQDN is specified for the location of the file ( \servername\sharename\filename.txt)
When file is copied on the server ( C:\ drive) , query runs fine via SSMS
We are using SQL server 2008 R2

I have checked all NTFS permissions for the shared folder.
I have gone through numerous posts suggesting solution but no luck.


texts: Is it possible to restore database only with .trn files?

texts:
How do I restore database only with .trn (transaction log backup) file?

I did below two steps Microsoft SQL Server Management studio shows "Restoring".

1)

RESTORE DATABASE [database_name]
  FROM DISK = N'path\file_name.bak'
  WITH MOVE N'logical_filename' TO N'path\filename.mdf',
       MOVE N'logical_filename_log to N'path\filename.ldf' REPLACE,
       NORECOVERY;
GO

2)

RESTORE LOG [database_name]
  FROM DISK = N'path\filename.trn'
  WITH NORECOVERY;
GO


texts: How can I handle a huge amount of logging generated from frequent table rebuilds?

texts:
We have several tables which we "refresh" frequently by rebuilding them in a staging-table, then performing a metadata-switch to the production-table using the ALTER TABLE <source> SWITCH INTO <target> statement.

These operations are causing a huge amount of logging, and we're not sure how to handle it. For now we just moved these tables and operations to a new database using the SIMPLE recovery model, but I would love to hear alternative solutions to this problem.

How do you suggest we handle the massive log generation as a result of frequent table rebuilds?


texts: Why is the ROW keyword needed when constructing a row with one element, but not two?

texts:
In PostgreSQL 11, the following expression
UPDATE test_table SET ("column_a","column_b") = ('value-a','value-b') WHERE "column_a" = 'value-c'

is accepted and performs the update.
But for a single column / value, like
UPDATE test_table SET ("column_a") = ('value-a') WHERE "column_a" = 'value-c'

it gives an error. ROW must be supplied, like
UPDATE test_table SET ("column_a") = ROW ('value-a') WHERE "column_a" = 'value-c'

Is there an explanation why this behavior was chosen? Is there an ambiguity if ROW is missing?


texts:
I have read lot of articles on net but still confused why  Mongo  CP, Cassandra AP,  RDBMS CA ?
Will explain my understanding and query along with that .
Mongo
Consider a scenario where I have one master  ans two slaves.  consider

Write request arrives and goes to master.
It gets committed on master only but master goes off(crashed) before it is written to slaves
Till master is re-elected, write requests need to wait and system is not available
Once the previous node(node crashed in step 2) comes back, writes pending from that node are written back to slaves. This is called
eventual consistency.

Per my understanding because of step 3 and 4, Mongo is said to be CP where C stands for eventual consistent. Correct ?
Cassandra
Here  there is no master/slave model and every node takes its share write and read request based on shard key.

Write request arrives to any node(called coordination node).
Coordination node redirects to one of the node based on shard key
It gets committed  but node goes off(crashed) before it is written to other replication node.
Again write request with same shard key, now coordinated node redirect it immediately to replica node(replica of crashed node)
Once the previous node(node crashed in step 3) comes back, writes pending from that node are written back to replica node. So cassandra seems
to be eventual consistent too ?

Step 4 explains why cassandra is highly available but step 5 also depicts its eventual consistent. So Per my undsertanding , cassandra provides
provides both eventual consistency ans availability. Then why it is said it does not provide Consitency ?


texts: inserting data from one table to another after casting the values

texts:
I have a #temp table containing all the data that needs to be inserted into a table named Customer.
The column names of the two tables are exactly the same, but the data types are different. So I need to cast from one type to another before inserting the data into the Customer table.

The solution has to be implemented for a dynamic condition. i.e, I don't know the column name or data type.


texts: How many disk seeks does it take to write one row on SQLite?

texts: As far as I understand, there can be one disk-seek per rotation of a harddisk, therefore I am interested to know how many disk seeks are needed for writing a row to an SQLite table, including locking etc. Assume that there are no indexes on the table (not even a primary key). I assume that there is one disk seek to write a line to a file? As you understand, I want to compare performance with writing the same data to a flat file.


texts: How to make unique check without creating heavy index in postgres

texts:
I need to implement unique check for INSERT and UPDATE operations, but I would prefer to avoid creating heavy unique index (which is approx 12Gb now) on my table. Now I have unique partial index and it work great, except one thing - it need a lot of SSD space. I don't need SELECT operations using this index, I need just check for the uniqueness of the data.

I read this and this discussion and I understood that it's no real difference between unique constraint and unique index, except partial condition, which is able only for index.

Is there way to do this?

UPDATE:

My example is the schema of many-to-many relation table with history option. This option is implemented by 2 additional fields time_from and time_to. They are storing the time of entering and leaving relation. For data consistency I created 4 additional partial unique indexes (see below).

Now this table contains 1 162 010 000 rows. And overall space of table with each indexes:

vkontakte_wall_post_likes_users - 57 GB
vkontakte_wall_post_like_users_post_id - 24 GB
vkontakte_wall_post_like_users_time_to_2col_uniq - 24 GB
vkontakte_wall_post_like_users_pkey - 24 GB
vkontakte_wall_post_like_users_user_id - 24 GB
vkontakte_wall_post_like_users_time_to_3col_uniq - 846 MB
vkontakte_wall_post_like_users_time_from_2col_uniq - 8192 bytes
vkontakte_wall_post_like_users_id_seq - 8192 bytes

The schema:

CREATE TABLE vkontakte_wall_post_likes_users
(
  id integer NOT NULL DEFAULT nextval('vkontakte_wall_post_like_users_id_seq'::regclass),
  post_id integer NOT NULL,
  user_id integer NOT NULL,
  time_from timestamp with time zone,
  time_to timestamp with time zone,
  CONSTRAINT vkontakte_wall_post_like_users_pkey PRIMARY KEY (id),
  CONSTRAINT post_id_refs_id_3979681bdf0b31a3 FOREIGN KEY (post_id)
      REFERENCES vkontakte_wall_post (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT user_id_refs_id_73bdbaad8e08aee5 FOREIGN KEY (user_id)
      REFERENCES vkontakte_users_user (remote_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
  OIDS=FALSE
);
ALTER TABLE vkontakte_wall_post_likes_users
  OWNER TO manufacture;

CREATE INDEX vkontakte_wall_post_like_users_post_id
  ON vkontakte_wall_post_likes_users
  USING btree
  (post_id);

CREATE UNIQUE INDEX vkontakte_wall_post_like_users_time_from_2col_uniq
  ON vkontakte_wall_post_likes_users
  USING btree
  (post_id, user_id)
  WHERE time_from IS NULL;

CREATE UNIQUE INDEX vkontakte_wall_post_likes_users_time_from_3col_uniq
  ON vkontakte_wall_post_likes_users
  USING btree
  (post_id, user_id, time_from)
  WHERE time_from IS NOT NULL;

CREATE UNIQUE INDEX vkontakte_wall_post_like_users_time_to_2col_uniq
  ON vkontakte_wall_post_likes_users
  USING btree
  (post_id, user_id)
  WHERE time_to IS NULL;

CREATE UNIQUE INDEX vkontakte_wall_post_like_users_time_to_3col_uniq
  ON vkontakte_wall_post_likes_users
  USING btree
  (post_id, user_id, time_to)
  WHERE time_to IS NOT NULL;

CREATE INDEX vkontakte_wall_post_like_users_user_id
  ON vkontakte_wall_post_likes_users
  USING btree
  (user_id);


texts: Why Oracle AWR report's I/O stats are different from the I/O monitoring data captured on operating system?

texts:
I am using Oracle 11.2 on Redhat 6.8, and working on some I/O issue.
I got a AWR report of Oracle DB for a certain day, and I found that "Read per Second" in "IO Profile" section is 848.2.

I also checked my Zabbix monitoring data, which is called "Disk read rate", of the disk that Oracle DB installed for the same date, I found that there is not that many requests.You can see, the max number is about 116.

"Disk read rate" means, "r/s. The number (after merges) of read requests completed per second for the device".

And I tried using iostat tool to capture some real-time I/O stats on that disk. It also showed that there wasn't so many read requests happened.

So the question is,why I am getting such differences between Oracle AWR report and the OS monitoring data? Or have I misunderstood anything?