texts: Question Regarding Proper Failover to Asynch Server in Availability Group

texts:
We are in the process of moving datacenters so we want to properly failover our Primary server to an Asynchronous Server in our new datacenter so we can drive the existing gear out there. Here is my setup

Datacenter Old
SQL01 (Primary) (Current Vote 1)
SQL02 (Secondary) (Current Vote 1)

Datacenter New
SQL03 (Secondary Asynchronous) (Current Vote 1)

I also have a File Share Witness configured on a domain controller in our Office that both networks can see.  So this also has a vote.

On our cut over day we are going to failover our Primary to SQL03 so we can run our system in the New Datacenter while we drive the existing gear out.

Once I failover I need to shut down SQL01 and SQL02, drive them out to new location, change their IP's and bring them online.

Questions:

Should I remove SQL01 and SQL02 from the availability group first then re-add them once I bring them online at the new location?

Secondly How will this work with my voting seeing I currently have 4 vote and will be going down to 2?

Will this effect the majority vote?


texts: delete tables on import of dump created by mysqldump

texts:
There are 2 copies of the same database on 2 different storages which need to be in sync.

For example, database named test_db contains tables T1,T2,T3 tables.
T2,T3 are deleted.
Dump of the 1st copy is created using:

mysqldump --login-path=lp --add-drop-table test_db > dump.sql

and imported on 2nd storage using:

mysql --login-path=kp test_db < dump.sql

But deletion of tables T2,T3 on the 1st storage is not propagated this way to the 2nd storage.

How to propagate deletion of tables from the 1st storage to 2nd storage using mysqldump on the 1st and import of the dump on the 2nd?


texts: SQL query to display names of customers who have purchased all the DVD

texts:
These are the tables I have created and inserted the values accordingly:

CREATE TABLE Customer
(Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 Customer_Name VARCHAR(30) NOT NULL
)

CREATE TABLE DVD
(DVD_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_Name VARCHAR(30)
)

CREATE TABLE DVD_Purchase
(DVD_Purchase_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_No INTEGER NOT NULL,
 Customer_No INTEGER NOT NULL
 )

INSERT INTO Customer (Customer_Name)
VALUES('Daman')
INSERT INTO Customer (Customer_Name)
VALUES('Saif')
INSERT INTO Customer (Customer_Name)
VALUES('Gurung')
INSERT INTO Customer (Customer_Name)
VALUES('Upendra')

INSERT INTO DVD (DVD_Name)
VALUES('Bleach')
INSERT INTO DVD (DVD_Name)
VALUES('Gintama')
INSERT INTO DVD (DVD_Name)
VALUES('Tokyo Ghoul')
INSERT INTO DVD (DVD_Name)
VALUES('Death Note')

INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (3,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,4)

In order to display names of customers who have purchased all the DVD, this is the query that I have tried out:

SELECT Customer_Name
FROM Customer
WHERE
EXISTS (SELECT Customer_Name,DVD_Name
FROM Customer,DVD,DVD_Purchase
WHERE Customer.Customer_No = DVD_Purchase.Customer_No AND DVD.DVD_No = DVD_Purchase.DVD_No)

But unfortunately, after executing this query it is displaying all the customer names. But I want to display a specific customer name who have purchased all the DVD.

Can someone provide me the correct query ?


texts: Is it possible with SQL Server 2008 to have a Partioned Tables with an Identity Column?

texts:
I'm not thinking of using it for the partition function.
I just want to know, if it is supported or not.

Don't tell me why it is not a good idea to use such columns on partitioned tables.


texts: Display all fields with null values in a particular row in SQL Server

texts:
I have a requirement where I want to fetch and display all the fields with NULL value for a particular row in the table.

Some thing like:

select 'all columns with NULL'
   from table_xyz
   where primary_key='pk_row2';

I use SQL Server 2012. Please help achieve this.


texts: Stack Depth Limit Exceeded (After delete trigger)

texts:
CREATE TABLE parent (
   parent_id VARCHAR(30) PRIMARY KEY
);

CREATE TABLE child (
   parent_id VARCHAR(30) REFERENCES parent ON DELETE CASCADE,
   child_id VARCHAR(30) PRIMARY KEY
);

CREATE OR REPLACE FUNCTION delete_parent()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM parent WHERE parent_id = OLD.parent_id;
    RETURN NULL;
END; $$ LANGUAGE 'plpgsql';

CREATE TRIGGER delete_parent AFTER DELETE
ON child
FOR EACH ROW
EXECUTE PROCEDURE delete_parent();

Error:

stack depth limit exceeded
hint: 'Increase the configuration parameter "max_stack_depth" (currently 6144kB), after ensuring the platform\'s stack depth limit is adequate.'

Background:

A parent can have many children
The schema is designed to so that if a parent is deleted, all of its children records are also removed.
If a child is deleted, the trigger deletes the parent, and then cascade deletes all the other siblings

This has worked for months and today suddenly we started getting this error.

I can't find while there might be an infinite recursion and I am considering doubling the stack depth limit just to see what happens.

Note: The actual schema is more complex than this and has a few more related tables that have CASCADE delete constraints. But this is the only trigger.

UPDATE: So I doubled the max_stack_depth limit and now it is fine. I don't think this is a good solution and I am still unsure how I can for example prevent this from happening in the future.


texts: MySQL: display only the dates of the days that fall on a Sat. or Sun. between two given dates?

texts:
How can I get all the dates of the Saturdays and Sundays that fall between two given dates using MySQL?

Startdate = 2020-04-08
Enddate = 2020-04-21

The answer must be:

2020-04-08
2020-04-14
2020-04-15
2020-04-21

I'm a newbie to MySQL andI tried some queries that I got from the internet that show all dates between two dates now I wish to narrow those dates down to only Sat. and Sun.

select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2020-04-08' and '2020-04-21


texts: How can I change the font in the "edit data" window in pgAdmin III?

texts: I wonder whether I can change the font in the "edit data" window in pgAdmin III.  I use  Windows 7 and OS X 10.10.


texts:
I have a question on amount of time it takes for an in-memory OLTP SQL Server 2019 database (no Accelerated DB recovery configured) to come online.
I have 2 tables each with 40GB in size (in-memory) part of the database. My question is, does SQL Server have to load the complete 2 tables into memory first before bringing the DB online?
Let's say each table takes 20seconds to load from disk to memory depending on the disk throughput, can we say the DB will not be available in online state for at least 40 seconds (2 *20 seconds for each table) + regular recovery process of analysis,redo and undo phases for DB recovery?
Is my understanding correct? Can I get some pointers for the above concepts please?


texts:
I have a slow stored procedure like

CREATE PROCEDURE [dbo].[MyProc]
(
    @Param1 nvarchar(20),
    @Param2 nvarchar(20)
)
AS
BEGIN
    set nocount on

    ;with cte (col1, col2, col3)
    as
    (
        select col1, col2, col3
        from    someTable
    --And the rest of it

I am trying to optimise it because it was taking too long.

When I call it as exec MyProc @Param1=N'xxx', @Param2=N'yyy' it takes 20 seconds or so. To test what I could do I ran it like this:

DECLARE @Param1=N'xxx',
@Param2=N'yyy'
set nocount on

;with cte (col1, col2, col3)
as
(
    select col1, col2, col3
    from    someTable
--And the rest of it

and that takes about a second. Execution plans are completely different. Plan that is used when I call 'exec ...' is different to the plan that is used when I run the code directly. I re-indexed the tables used, I called sp_recompile on the table and the stored procedure itself, I found the plans that are used for the SP and called DBCC FREEPROCCACHE ( on the plan handles, but every time the plan is re-generated, they are different.

Any idea what's going on? Why would the same piece of code use different plans, one that takes 1 sec and the other taking 20 secs?


texts: Why would the user matter when running a query?

texts: I have a web page with a database lookup that contains multiple optional parameters.  When I select a couple of parameters and run the query, it returns in less than two seconds.  However, when another person logs in, and runs the same query with the same parameters on my machine, it takes 30+  seconds to come back.  Web browser doesn't matter, it behaves the exact same way in multiple different browsers for each user.  Both users have all the needed permissions on the database to run the query as they go through the same database connection using tcp/ip.  At this point, the only difference is who is actively logged in, which doesn't make any sense to me.  Does anyone have any ideas as to why the person would make a difference when everything else appears to be the exact same?  Thanks


texts: Can we use wildcard characters on the PostgreSQL hba_conf file for database names?

texts:
e.g.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    ffc*            fatfc             127.0.0.1/32            md5
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident


texts: Inserting data into a second table with some time lag

texts:
I am new to SQL Server. I am using Management studio 2012 Express edition.

What I want to do is that suppose I have table1 and table2 and suppose someone enters some data into table1, then after some time lag the data must be entered automatically into table2.

I remembered reading about scheduled events, but realized that the express edition does not support Server Agent.

Any suggestions as to how I could tackle this problem?

Thank you :)


texts: pt-table-checksum not finding differences

texts:
I have a very simple setup: 1 master with 2 slaves. I stopped replication on one of the slaves (with the slave mysqld still running) and changed a row on the master. I have also kept all slaves and the master running and just changed data on the slave.

I ran pt-table-checksum --databases=db_main --user=user --pass='pass' but do not get any differences.

I read the documentation a few times, but can't seem to find what I might be missing.

Extra info: the storage engine is InnoDB, the master is on MySQL and slaves on MariaDB. Full replication with slaves set as read-only. Permissions are correct.

I want to make sure the tool is working as expected. We had a slave stop running and I ran pt-table-checksum. It told me there was no difference in data (which was obviously false). I want to make sure pt-table-checksum is working as expected, but I have not been able to create a test where pt-table-checksum actually detects a difference.

Just now I changed data on the running slave (so data is different in slave and master). When I run pt-table-checksum on that database, I don't see any "diffs". Because it is a mature tool, I assume I am doing something wrong, but can't figure out what it is.


texts: I need to restore an Oracle 11.2.0.4 database from Netbackup on to a different host. Is it possible to duplicate the DB with RMAN into an Auxiliary DB but without a recovery catalog? If it’s not possible directly can I restore backup pieces to disk first and then duplicate? What would be the procedure? Many thanks.


texts: Slow Query Gets Even Slower After Indexing

texts:
I have a MySQL database with just one table containing 8 million records. I need to query the table to find a single record that matches a specific number:

select * from my_table
where
  (from_field <= 1533177608)
and
  (to_field   >= 1533177608)

Without an index, the query runs in 5 seconds. But surprisingly, when I create an index on two queried columns, the query takes 35 seconds to complete:

alter table my_table
add index test (from_field, to_field);

It is very important for me to speed up the query to reach a fraction of a second. What can I do to improve it?


texts: DB2 window function - running total of X for preceding N minutes worth of rows

texts:
Very new to window functions but think I get the idea.
I'm trying to get a rolling sum of one column in 10 minute intervals (determined from a separate timestamp column)
A working sample might would look like this:
EDIT: fiddle link

Filtered for T2 highlights it better:

There's plenty about the topic like this or this and so on but I'm having trouble finding a solution specific to my requirements.
If I didn't need it by 10 minute intervals it'd be very simple, by 10 rows (current + 9 preceding);
SELECT  ID, TIME, P/L,
    SUM(P/L) OVER (PARTITION BY ID
                     ORDER BY TIME
                     ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as ROLLING
FROM TABLE

Maybe adding the 10 min constraint is not possible with the window function?
############## EDIT TWO ##############
Ok, so I've figure out the issue and managed to reproduce it in a fiddle
In the real table there are valid duplicate transactions (same ID and TIME), I thought I could pull back microseconds and solve this but unfortunately these aren't stored.
We end up with a weird backwards sum for the dupe's which may not be a huge issue but would rather fix if possible:

########################################


texts: Is it possible to execute queries in random order?

texts:
I have a list of INSERTs, UPDATEs and DELETEs queries.
My problem is that I always receive them in the wrong order (random), and I cannot know their original order.

Are there techniques to group/execute them in a particular order, so that I always end up with the roughly the same end-result as the source, no matter in which order I originally received them?


texts: How can I resolve the `package mysql-server is not installed` issue?

texts:
I have just upgraded MySQL from version 5.5.55 to version 5.6, via SSH.  I have managed to successfully start the MySQL, however I am experiencing the 500 Internal Server Error when trying to access the website on the server as well as generating the Apache Test page when trying to access the Webmail.

I then ran the command rpm -q mysql-server, where I received the following response:

package mysql-server is not installed

From the above's output, I assume that MySQL has not been installed.  However, when I enter mysql --version, the following is outputted:

mysql  Ver 14.14 Distrib 5.6.44, for Linux (x86_64) using  EditLine wrapper

Furthermore, when I run the command yum install mysql-server, I also receive the output of:

Package mysql-community-server-5.6.44-2.el6.x86_64 already installed and latest version
Nothing to do

As such, I am confused and not sure whether MySQL is actually installed or not.

In order to resolve this issue, and allow access back to the website and Mail Domain, what would be the next step I would need to perform?


texts:
So, given MS SQL 2008R2 or better:

I want to not only store data in a table but be able for my application to automatically verify it was not modified.

Solution seems to be to use SignByCert to make a digital signature of every cell and insert each result into a matching table full of signatures so I can call VerifySignedByCert later when I query it to see if someone changed the plaintext data.

https://docs.microsoft.com/en-us/sql/t-sql/functions/signbycert-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/functions/verifysignedbycert-transact-sql

My question is, signbycert wants an optional password to decrypt the private key it is signing with. I don't want to store private key passpharses in my stored procedures that call SignByCert. It doesn't seem to make sense to just leave the passphrases in the clear.

Alternatively, is there a secure (good) way to reference the passphrase or is there a safe way to store or NOT encrypt the private key in MS SQL so MS SQL hides the passphrase and knows it internally so I don't have to put it in the SQL statement?


texts: count total matching id if a single column contain multiple value separated by comma

texts:
I have problem in my school management software.

I have to count how many classes a teacher has in all week, for this the admin may assign more than one teacher to a class and that selected teacher is inserted in database by their ids. If there are multiple teachers they will be inserted in single column separated by commas.

I have to count the total number of classes for a teacher.

How could I create such a query

mysql_num_rows(mysql_query("select * from table_teacher where employee_id in (1))


texts:
i've two database, Database1 and Database2! i'd like to synchronize data from the specific table of Database1 to Database2 whenever Database1 updates. I've used triggers to synchronise data and it works but it duplicates everything say number, name, amount yet i want it to update, say if name jovia with a number 014567 and her previous amount(balance) is 200 in Database2. And if payment is made in Database1, with the same name and number and with 300 as the amount, i'd like the trigger to synchronize the other table by adding up the previous amount and the new amount, it does that but it duplicate the rows.

I've added a LIMIT 1 to avoid duplicate rows but it's the same issue

Database2 cc_phonenumber_table

-------------------------
name  | number | amount
-------------------------
jovia | 014567 | 200
maria | 098765 | 500
jovia | 014567 | 500
muche | 987653 | 245

how can avoid this duplication in the cc_phonenumber_table? please help me below is my trigger i've tried to use;

delimiter |

CREATE TRIGGER database1 BEFORE INSERT ON pesapi_payment
  FOR EACH ROW
  BEGIN
    UPDATE database2.cc_phonenumber SET amount=amount+new.amount where id=new.amount
    INSERT INTO database2.cc_phonenumber(number, name, amount) values (new.phonenumber, new.name, new.amount);
       END;
    |

delimiter ;


texts: Windows ODBC Driver versions for Netezza

texts:
I am looking for information regarding the Netezza windows ODBC driver versions.

What are the supported/preferred windows ODBC driver versions for Netezza 7.2, Netezza 6.1 and Netezza 6.0.

Any information is very much appreciated.


texts: Need Clarification of SQL Server Blocking-Process lastbatchstarted

texts:
My understanding is that a SQL Server batch consists of 1 or more statements that will be executed sequentially. The last batch time should be the time when the batch was started. Also, multiple batches can be submitted over the same connection.

The blocking-process event has a wait time and a lastbatchstarted. The confusion I'm having is that the wait time seems to be based on the batch start time but the lastbatchstarted seems to be mislabeled and appears to actually be based on the last statement started.

In my testing environments I am seeing that lastbatchstarted resets after different statements from the same batch are executed but wait time doesn't.

I am unable to find information confirming this online. Can someone let me know if lastbatchstarted is actually last statement started?

Also, as an aside, BOL recommends never using a blocked process threshold lower than 5. There isn't really any detail other than the fact that the blocked process monitor may run continuously. Is there any additional information of the impact of this? Will it negatively impact performance or break something if the threshold is set to a number lower than 5?


texts: How does the QUEUE_DELAY option for Server Audits work

texts:
I am working through how SQL Server Audits work and would like some clarity on how the QUEUE_DELAY options works.  BOL defines the options as:

  Determines the time, in milliseconds, that can elapse before audit
  actions are forced to be processed. A value of 0 indicates synchronous
  delivery. The minimum settable query delay value is 1000 (1 second),
  which is the default. The maximum is 2,147,483,647 (2,147,483.647
  seconds or 24 days, 20 hours, 31 minutes, 23.647 seconds). Specifying
  an invalid number will raise the error MSG_INVALID_QUEUE_DELAY.

Where are these actions queued tempdb, a system table, etc?  Can I query that location to see how many items are in the queue?  If the ON_FAILURE option is set to shut down the server, will these items be logged to the audit once the server is restarted and the location can be accessed again?


texts:
I am practising in writing a simple forum and just can't decide on table structure.
Currently I have this:

        id  int(11)     AUTO_INCREMENT
        title   varchar(56)
        group_id    int(11)
        group_parent    tinyint(1)
        access_from int(11)
        alliance_forum  tinyint(1)
        alliance_id int(11)
        alliance_role_access    int(11)

Group parent is a 0 or 1 column which states wherever a forum is parent of forums, and group id is for group forums whch reffers to same table to record with group parent set to 1.
Alliance forum is same as group parent, as the alliance id,and access column are for access control.
Which would be the best and perfomant way of restructuring this? Should I mak 3 different tables for alliance forums, forum_groups and group_forums or keep it this way?


texts:
Let me start by saying our SQL servers are hosted by a third party. Every once in a while our monitoring software will show that cpu usage on one of our servers goes way out of line with normal activity, eg normal usage may be around 20% - 30 % utilization, jumping to 80% to 100% when the issue occur.

The other day I had this event occur for about an hour.

I confirmed it was sqlserv.exe hogging the cpu. No backups where occuring, no index maintenance jobs.

I set about running sp_whoisactive to see any problematic queries but there was nothing that seemed to burning a lot of cpu, nor did there appear to be lots of smaller queries - i just saw the usual amount of queries running then finishing.

Could it be possible that every now and again our cpu gets accidentally 'shared' out with other clients and that is what causes the high usage. Is this something that happens in the hosted world ?

To save my sanity, I run high cpu workloads on my test system, see the high cpu, capture the sql queires and plans using sp_whoisactive and am able to put 2 and 2 together.

Other times on our production systems I can see some problematic queries and am able to either stop them or fix them. Its just every now and again something seems to happen whereby I cannot see what is causing the high cpu - so i started to think about how our systems are hosted.

What questions should I be asking our vendor....if any at all ?


texts: How can I configure a table such that a user can modify a row "owned" by that user, but not modify rows "owned" by other users?


texts:
I would like to know which Disk a database device is being stored.  When I use "format" command in SCO Unix command line, it shows that the system has two disks (Disk 0 and Disk 1).  They are:

0. c0t0d0 <IBM-DNES-309170-SA30 cyl 11195 alt 2 hd 5 sec 320>
   /iommu@f,e0000000/sbus@f,e0001000/espdma@f,400000/esp@f,800000/sd@0,0
1. c0t3d0 <SEAGATE-ST34520N-1206 cyl 9004 alt 2 hd 4 sec 246>
   /iommu@f,e0000000/sbus@f,e0001000/espdma@f,400000/esp@f,800000/sd@3,0

When I check my database using sp_helpdb, I find that its data is in "sybdbs" and its transaction log is in "syblogs".  And then when I use sp_helpdevice to check, I find these:

device_name physical_name
----------- ------------------
sybdbs      /dev/rdsk/c0t0d0s1
syblogs     /dev/rdsk/c0t3d0s4

I have used "df /dev/rdsk" command and "df -F ufs" command and confirm that they are in local disks, not in network shared folders.

I would like to know which Disk that /dev/rdsk/c0t0d0s1 is on, and which Disk that /dev/rdsk/c0t3d0s4 is on.  How do I do this?


texts:
Below query is executing in approx 5 min:

     SELECT COUNT(distinct B.CALL_ID), C.IVR_APPLN_NAME
      FROM PTDT.TDT_CALL_DATA AS A, PTDT.TDT_IVR_CALL_APPLICATIONS AS B,
           PTDT.TDT_IVR_APPLN_REF AS C
      WHERE A.CALL_ID = B.CALL_ID AND B.IVR_APPLN_ID = C.IVR_APPLN_ID
      GROUP BY C.IVR_APPLN_NAME;

By adding additional where clause (IVR_APPLN_NAME) it's executing in 120 min.

    SELECT COUNT(distinct B.CALL_ID), C.IVR_APPLN_NAME
            FROM PTDT.TDT_CALL_DATA AS A, PTDT.TDT_IVR_CALL_APPLICATIONS AS B,
            PTDT.TDT_IVR_APPLN_REF AS C
            WHERE A.CALL_ID = B.CALL_ID AND B.IVR_APPLN_ID = C.IVR_APPLN_ID
        AND C.IVR_APPLN_NAME in ('DSC','EMS','FAO','RET','YAS')
      GROUP BY C.IVR_APPLN_NAME;

So I tried to run it like below:

       select * from (
                SELECT COUNT(distinct B.CALL_ID), C.IVR_APPLN_NAME
                   FROM PTDT.TDT_CALL_DATA AS A, PTDT.TDT_IVR_CALL_APPLICATIONS AS B,
                   PTDT.TDT_IVR_APPLN_REF AS C
                WHERE A.CALL_ID = B.CALL_ID AND B.IVR_APPLN_ID = C.IVR_APPLN_ID
                GROUP BY C.IVR_APPLN_NAME) as D
       where D.IVR_APPLN_NAME in ('DSC','EMS','FAO','RET','YAS')

I assumed that internal query will run first (like before it was running in 5 min) and then I will filter results out of that (from outer filter D.IVR_APPLN_NAME in ('DSC','EMS','FAO','RET','YAS')).
But this was not the case, DB2 manipulated in such way that it applied outer filter (IVR_APPLN_NAME) to internal query and overall query executed again in 120 min.
  Other option could be to store internal result in temporary table but temp table use in DB2 itself is not as simple as in SQL.

Any suggestions?


texts:
I have used workbench before to make databases code wise and I just started looking into its EERD functions. I created a small database for a test project and found some odd things about how MySQL workbench did things. I am trying to make sure this program is working correctly before I use it for larger projects.

I created an EERD that looks like this:

(User)||---o<(Permissions)>o---||(Rooms)||---o<(Posts)||---o<(Target)

I forward engineered it to my database and it went through fine, everything seems to look ok (don't have data to test it with). I then reverse engineered that database and it switched all of the optional manys into mandatory(1) manys.

Is this a cosmetic issue with workbench or can I not forward engineer optional manys?


texts: Change default value for Job Activity Monitor

texts:
Is there a way for me to programmatically (some configuration setting in a table / registry entry etc, I'm hoping) change the default value in the Auto Refresh setting to 5 seconds instead of 60, and to set the Auto Refresh to ON?

I need a way to make this a permanent setting - hopefully so that it applies to all users of the server.


texts: How can I implement full-text search on a single value in a text[] column?

texts:
I am using Postgresql 9.3 on an Ubuntu 12.4 machine.

I've only recently started studying database and database design, and I am to create a database that includes text that will later be searched through. I've read up on tsvectors and stuff better than WHERE column LIKE 'query', but I don't have much experience with it. I am thinking of building a table where one column is of type text[], and another one will be the tsvector build from the text. I will use and array of text because some of the text will be displayed to the user with different formatting etc. I have never worked with array columns before. My questions are:

Is using a text array a good idea at all in this case?
Will the text array work well with the tsvectors?
Will using text[] compromise my performance?
Are there any pitfalls I should stay alert for?

Thanks for the attention, and this is my first post ever here, so if I messed up on this question do tell me. P.S.: I've searched this topic here beforehand, I hope I am not creating a redundant question. P.P.S.: Not a native speaker, sorry for the weird language.

EDIT: Thanks for the answers! The texts in the array won't be of fixed size. They will range from 6 ~ 20 lines, but they need to be editable, and some of the texts inside an array will be formatted in a different manner when displayed to the user. I'm not using multiple text columns because some of the entries in the table will have a single, two, or more elements inside the array. I know how each element will be formatted because every row will also have a "type" column, which will define it's formatting.


texts: rsExecutionNotFound when directly accessing an SSRS report

texts:
I have an SSRS report that can I access from the Web Portal but once I try to access it directly through the URL, I get the following error: Execution 'xxx' cannot be found (rsExecutionNotFound).

I looked at the logs on the Report Server and the error there says  userName: mydomain\myusername not found in the database.

But, of course, it is in the database - I can access the report from the Web Portal.

Any ideas?

UPDATE The same link now works, two days later.


texts: SELECT all rows with sequential id after a specific id

texts:
Is it possible to query all rows that their ids are sequential after a specific id? (id is an AUTO_INCREMENT column.)
For example, suppose my specific id is 5. Then I want all rows with these ids:
6, 7, 8, 9, ... until this sequence exists. E.g. if there is no row with id=12 then the sequence must be stopped at 11. So desired result would be rows with these ids:
6, 7, 8, 9, 10, 11.
I use MySQL.

EDIT:
An example:
This is my table:
mysql> SELECT * FROM Inheritance ORDER BY id ASC;
+-------------+-------------------+------------+-------------+-------+
| classNS     | class             | super      | superNS     | id    |
+-------------+-------------------+------------+-------------+-------+
|             | Data              | Object     |             | 00002 |
|             | Thing             | Object     |             | 00003 |
| BUILD       | DATA              | Data       |             | 00004 |
|             | Dialable          | Data       |             | 00005 |
|             | Phone             | Data       |             | 00006 |
|             | Email             | Data       |             | 00007 |
|             | Place             | Data       |             | 00008 |
|             | Website           | Data       |             | 00009 |
|             | SocialNetworkLink | Data       |             | 00011 |
|             | Password          | Data       |             | 00012 |
|             | User              | Thing      |             | 00013 |
|             | Asset             | Thing      |             | 00014 |
|             | Collection        | Asset      |             | 00015 |
|             | Product           | Asset      |             | 00016 |
| collections | School            | Collection |             | 00017 |
| collections | LangSchool        | Collection |             | 00018 |
| BUILD       | OWNER             | DATA       | BUILD       | 00022 |
| BUILD       | OWNER_OF_PRODUCT  | DATA       | BUILD       | 00023 |
| BUILD       | OWNER_OF_EMAIL    | DATA       | BUILD       | 00024 |
| BUILD       | COUNTRY           | DATA       | BUILD       | 00025 |
| BUILD       | SOCIAL_NETWORK    | DATA       | BUILD       | 00026 |
| BUILD       | PROVINCE_IR       | DATA       | BUILD       | 00027 |
| collections | SchoolPlus        | School     | collections | 00028 |
+-------------+-------------------+------------+-------------+-------+
23 rows in set (0.00 sec)

Then I want this result (when my specific id is 5):
+---------+----------+-------+---------+-------+
| classNS | class    | super | superNS | id    |
+---------+----------+-------+---------+-------+
|         | Phone    | Data  |         | 00006 |
|         | Email    | Data  |         | 00007 |
|         | Place    | Data  |         | 00008 |
|         | Website  | Data  |         | 00009 |
+---------+----------+-------+---------+-------+

Notice that the row with id=10 is absent in the source table.


texts: How to troubleshoot, find and fix query Percona 5.6 with error "SQLSTATE[HY000]: General error: 1785 When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1"

texts:
i'm running Magento CMS, and underneath there are 2 physical servers both with Percona

  Server version: 5.6.23-72.1-log Percona Server (GPL), Release 72.1,
  Revision 0503478

in Master/Slave with GTID config.

Here it is the config, specific for GTID (it's the same also on salve)

  server-id                 =1
  binlog_format               =MIXED
  log_bin                   =/var/log/mysql/mysql-bin.log
  expire_logs_days            =3
  max_binlog_size           =50M
  log_slave_updates           =1
  gtid-mode                   =on
  enforce_gtid_consistency master-info-repository       =TABLE
  relay-log-info-repository =TABLE sync-master-info         =1
  binlog-checksum           =CRC32
  master-verify-checksum      =1
  report-host               =BCVMaster

My client randomly during the day, receives this error:

  SQLSTATE[HY000]: General error: 1785 When
  @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional
  tables can only be done in either autocommitted statements or
  single-statement transactions, and never in the same statement as
  updates to transactional tables.

I'm willing to resolv this problem, me from a server stand point of view and i have 2 magento developers ready to help me, but i need help on how to trace, identify and visualize offending query so either me or developer can resolve the problem.

What i have done so far

1)converted as many possible tables from MyISAM to InnoDB

2)changed binlog format from row --> mixed

3)rebuild twice the slave with innobackupex

4) tried to read from binary logs:
mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000179 > /root/mysqlbin179.txt
but seems i'm not looking in the right direction.

Can i please have some help on this?

thank you


texts:
I am in the process of scripting existing SQL Server 2008R2 logins for migration to a new server instance.

I see that the existing logins contain the below...

Windows Groups:

[BUILTIN\Administrators]

[BUILTIN\Users]

[BUILTIN\Backup Operators]

Windows Users:

[NT SERVICE\MSSQLSERVER]

[NT SERVICE\SQLSERVERAGENT]

Should these logins be used at all? And do they get created by default on installation?


texts: Fetch record from 3 tables which are related to each other through ID


texts: Database table data with different set of values

texts:
I am trying to create a database table for my project but I stuck in this type of data.

The purpose is the admin can add, modify or insert types of questions and choices

example:

Do you like pokemon?

Yes
No

Pick the pokemons you like

[] charmander

[] pikachu

[] squirtle

[] bulbasaur

Pick your starting pokemon:

charmander
pikachu
squirtle
bulbasaur

Why pikachu is so cute?

Explanation here

Here is my table

Questions table

  id  |  question   |  type

Choices table

  id  |    choice   |  question_id

UserAnswers  table

  id  |    choice_id  | question_id  |  answer  |   userid

my problem is where to put the explanation of the user since it would be a large set of text? I am still very new in database designing and don't know what to do with this type of scenario. Please any recommendation for a good way to design my table.


texts: Rich text: VARCHAR, TEXT or BLOB in MySQL

texts:
Question: In my web application (php/MySQL) I will be saving rich text. This may be anything from a couple words to several pages length essay. I’m sure I could get away with using VARCHAR, TEXT or BLOB to store markdown or HTML from a rich text editor like TinyMCE. Which should I use?

My findings so far: In all my searching this week I’ve not found anything that tried to be a definitive source as to which would be best in general. I think VARCHAR is a standard and TEXT is not. BLOB is for binary data and so is overkill. TEXT may be deprecated in some DBMS. One major concern I have revolves around my ignorance as to how VARCHAR is saved to the database. I.e., if I max it out (or leave it undefined) will it waste storage or memory if there is only a few words in it? Or as a modern DB does MySQL not store blank data as reserved space? My research has indicated that the performance difference is very minor in modern systems between VARCHAR and TEXT queries. Also, I do not need to do many text searches in the content. I’m not planning on any, however, the ability to do so would be a benefit, long term.

Tentative Conclusion: I think to be best and standards compliant (low priority) I should make a VARCHAR type with no limit. Then just insert the markdown, HTML or textile data in there. I assume not specifying a limit would max out the data capacity on the server.

Would VARCHAR be the best standard to employ? Or have I gone completely off the rails in my research?


texts: We need to change the SQL backup location from one network share to another network share. The new share name is different from the old share name. I am thinking to update the "command" column and "output_file_name" column in "msdb..sysjobsteps" table. Is it safe?


texts:
I need to order a query by the value of a field. If it is empty or not

I need to cast the value of the column order_by to a boolean and the column type is varchar

All rows where order_by is empty or null should be ordered last in the query

SELECT *
FROM table
ORDER BY CAST(order_by, 'boolean')


texts: Will SQL Server check FK restrictions between row deletes in a single DELETE statement?

texts:
I have a table that is self-referencing:
CREATE TABLE [dbo].[TestTable] (
    [id] [bigint] IDENTITY(100000,1) NOT NULL,
    [referenced_id] [bigint] NULL,
    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED (
        [id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestTable] WITH NOCHECK ADD CONSTRAINT [FK_ReferencedId] FOREIGN KEY ([referenced_id])
    REFERENCES [dbo].[TestTable] ([id])
GO

ALTER TABLE [dbo].[TestTable] CHECK CONSTRAINT [FK_ReferencedId]
GO

Let's say I have several rows that reference each other:
id     | referenced_id
-------|--------------
100023 | 100024
100024 | 100023
100025 | 100026
100026 | 100023

If I try and DELETE the row WHERE [id] = 100023, the FK will be violated because 100024 and 100026 reference that row and the DELETE will fail.  However if I just DELETE FROM [dbo].[TestTable], it seems to work and successfully delete all rows.  Therefore, SQL Server only seems to be checking the FK constraint after all rows to be deleted in a single DELETE statement have been deleted, rather than in between each row deletion.
Can I rely on this behaviour, or might such a DELETE sometimes fail?


texts: Good video tutorials/series for learning Oracle?

texts:
I am a better learner with visual content. Therefore I actively seek video tutorials when I start learning a subject. For oracle below links are my finds. I would like some additions to these.

Oresoft youtube Oracle Server Concept
http://www.youtube.com/user/TheOresoft#grid/user/8A9FE158DD9759AD
Oracle DBA Justin
http://www.youtube.com/user/jbleistein11#p/u
Oresoft Oracle SQL
http://www.youtube.com/user/TheOresoft#grid/user/EB058E78A206806C
Oresoft Oracle PLSQL
http://www.youtube.com/user/TheOresoft#grid/user/3245012E0631F7AE
Oracle VTC
http://www.vtc.com/products/oracle-10g-tutorials.htm


texts: group by clause without aggregate function

texts:
I've always assumed GROUP BY was designed specifically for aggregate functions and in all other circumstances you should use ORDER BY. For example, we have three tables: orders, shippers, and employees:

OrderID     CustomerID  EmployeeID  OrderDate   ShipperID
10248   90  5   1996-07-04  3
10249   81  6   1996-07-05  1
10250   34  4   1996-07-08  2

ShipperID   ShipperName     Phone
1   Speedy Express  (503) 555-9831
2   United Package  (503) 555-3199
3   Federal Shipping    (503) 555-9931

EmployeeID  LastName    FirstName   BirthDate   Photo   Notes
1   Davolio     Nancy   1968-12-08  EmpID1.pic  Education includes a BA....
2   Fuller  Andrew  1952-02-19  EmpID2.pic  Andrew received his BTS....
3   Leverling   Janet   1963-08-30  EmpID3.pic  Janet has a BS degree...

We can then determine the number of orders sent by a shipper:

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;

Group By helps here because it prevents a duplicate shipper name in the result set. That is, the aggregate function itself without using GROUP BY would return two rows of shipper name if shipper name appears more than once in the shippers table. GROUP BY gives our aggregate without duplicates.

Makes sense. But then I come across this result set from an ORM (ActiveRecord in Rails in this case):

SELECT users.* FROM users
INNER JOIN timesheets ON timesheets.user_id = users.id
WHERE (timesheets.submitted_at <= '2010-07-06 15:27:05.117700')
GROUP BY users.id

There were no aggregate functions in that sql statement. Shouldn't it be using ORDER BY instead?


texts: Why "Revoke Priviliges" Command Doesn't Work in MySQL?

texts:
I am trying to remove all priviliges of a user on a db, I executed the command below but it doesn't change anything. What is the problem?
revoke all on db.* from 'user'@'host';


texts: MongoDB Reconnects to Secondaries Too Fast

texts:
How can MongoDB 4.0.12 be configured to SLOW its rate of re-connecting to its replica members when they are down?

I had MongoDB replica members die. The online replica members get “connection refused” when trying down members, then re-attempt to connect as fast as the CPU and network allow. This crushes the CPU and makes the log files grow huge as Mongo retries it’s connection hundreds of time ps per minute.

I'd rather MongoDB retry to reach replica members only once per minute, rather than immediately after getting a failure. There’s no reason to retry a down server hundreds of times per minute.

Is there a way to configure MongoDB to wait for some seconds before attempting a reconnect when a replica member is down? I can't find any such setting in the server & replica settings, and this seems like it would be an important thing to set.


texts: Indexed varchar column very slow (40M+ rows)

texts:
I've got an events table with over 40 million rows. It has a btree index on type: "index_events_on_type" btree (type).

Any equality look up takes ages! I tried tuning up the work_mem to get rid of the lossy value but it had little to no effect.

dev=# EXPLAIN (ANALYZE, BUFFERS) SELECT "events".* FROM "events" WHERE "events"."type" = 'contact.card.added';
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on events  (cost=56176.20..1633783.13 rows=1614405 width=296) (actual time=252.846..13832.331 rows=1550851 loops=1)
   Recheck Cond: ((type)::text = 'contact.card.added'::text)
   Rows Removed by Index Recheck: 10414231
   Heap Blocks: exact=39335 lossy=758338
   Buffers: shared read=807020
   ->  Bitmap Index Scan on index_events_on_type  (cost=0.00..55772.60 rows=1614405 width=0) (actual time=243.096..243.096 rows=1550851 loops=1)
         Index Cond: ((type)::text = 'contact.card.added'::text)
         Buffers: shared read=9347
 Planning time: 0.100 ms
 Execution time: 13924.612 ms
(10 rows)

...and the table

dev=# \d events
                                            Table "public.events"
      Column       |            Type             | Collation | Nullable |              Default
-------------------+-----------------------------+-----------+----------+------------------------------------
 id                | bigint                      |           | not null | nextval('events_id_seq'::regclass)
 type              | character varying           |           | not null |
 aggregate_root_id | uuid                        |           | not null |
 entity_id         | uuid                        |           |          |
 parent_id         | bigint                      |           |          |
 created_at        | timestamp without time zone |           | not null |
 body              | jsonb                       |           |          | '{}'::jsonb
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)
    "index_events_on_aggregate_root_id" btree (aggregate_root_id)
    "index_events_on_created_at" btree (created_at)
    "index_events_on_entity_id" btree (entity_id)
    "index_events_on_parent_id" btree (parent_id)
    "index_events_on_type" btree (type)
Foreign-key constraints:
    "fk_rails_68f023eb25" FOREIGN KEY (parent_id) REFERENCES events(id)
Referenced by:
    TABLE "events" CONSTRAINT "fk_rails_68f023eb25" FOREIGN KEY (parent_id) REFERENCES events(id)

Any guidance would be appreciated.

Update

Looks like the most time spent is in I/O as the query returns 1.5M records:

dev=# EXPLAIN (ANALYZE, BUFFERS) SELECT "events"."type" FROM "events" WHERE "events"."type" = 'bank_account.payout.added';
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on events  (cost=6324.92..520827.82 rows=182239 width=32) (actual time=283.119..15719.744 rows=1550851 loops=1)
   Recheck Cond: ((type)::text = 'contact.card.added'::text)
   Rows Removed by Index Recheck: 10414212
   Heap Blocks: exact=39336 lossy=758337
   Buffers: shared hit=75 read=806945
   I/O Timings: read=13783.176
   ->  Bitmap Index Scan on index_events_on_type  (cost=0.00..6279.36 rows=182239 width=0) (actual time=272.302..272.302 rows=1550851 loops=1)
         Index Cond: ((type)::text = 'contact.card.added'::text)
         Buffers: shared hit=75 read=9272
         I/O Timings: read=67.302
 Planning time: 0.069 ms
 Execution time: 15807.701 ms
(12 rows)

Rows returned:

dev=# SELECT COUNT(*) FROM "events" WHERE "events"."type" = 'contact.card.added';
  count
---------
 1550851
(1 row)

Total rows:

dev=# SELECT COUNT(*) FROM "events"
dev-# ;
  count
----------
 36447779
(1 row)


texts: SQL Server Licensing & Clustering for Availability Groups

texts:
For SQL Server 2014, reading this MSDN link and Microsoft PDF file about licensing, created a couple of questions.

For Availability Groups do we need WSFC or FCI?
How are WSFC and FCI different?
If we only need 2 AG nodes, could SQL Server Standard Edition work?


texts: Duplicate database leaves orphan archivelog files

texts:
I am cloning the production database to another one, lets say CLONEDB, in a regular basis. I am using rman duplicate database script like:

run {
duplicate target database to CLONEDB
FROM ACTIVE DATABASE
PASSWORD FILE
SPFILE
SET ....
DB_FILE_NAME_CONVERT=....
LOGFILE
  GROUP 1 (
     ....
  ) SIZE 100M,
....
;
}

Before running the script I am doing an archivelog rman backup of the CLONEDB (it deletes backed up archivelogs) and DROP DATABASE CLONEDB.

While running duplicate database, rman copies datafiles and archivelogs from the source database. For example, I can see that such file has been copied:

input archived log thread=1 sequence=666 ...
output file name=+DYSK/archivelog/2015_01_16/thread_1_seq_666
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Then rman recovers database (via set until scn 666666; recover clone database delete archivelog;), so the archivelog above is applied to the database:

starting media recovery
archived log for thread 1 with sequence 666 is already on disk as file +DYSK/archivelog/2015_01_16/thread_1_seq_666
archived log file name=+DYSK/archivelog/2015_01_16/thread_1_seq_666 thread=1 sequence=666
media recovery complete, elapsed time: 00:00:02
Finished recover at 2015.01.16 16:21:57
Oracle instance started

Everything is OK, database starts up, users are happy. But the archivelog file (in this example thread_1_seq_666) stays on the disk. And because rman changes the database incarnation after duplicate process, this file is never deleted during the backup process, as it belogns to the previous incarnation.

So from time to time I must do the housekeeping and delete old, orphaned archivelogs directly from the disk. Of course I would like to do it automatically, with the duplication script. I could add rm command from asmcmd for the old files, but maybe there is a way to convince rman to do it for me during the duplicate database?


texts: Installing SQL Server version 10.50.4000 is failing

texts:
I am attempting to install SQL Server 2008 R2 version 10.50.4000 on a virtual machine running Microsoft Windows Server 2008 R2.

When I try to install SQL Server, I get an error that, when I look it up, says I need to install and configure .NET Framework 3.5 first.

When I try to install via add features, it fails without telling my why, and I have to restart the server.

When I try to install manually, it says I have to use the installer.

When I try to install via PowerShell (Import-Module ServerManager and Add-WindowsFeature as-net-framework), I get the following error:

  Add-WindowsFeature : Installation of [.NET Framework 3.5.1 Features]
  .NET Framework 3.5.1 failed. The server needs to b e restarted to undo
  the changes. At line:1 char:19
  + Add-WindowsFeature <<<<  as-net-framework
      + CategoryInfo          : OperationStopped: (:) [Add-WindowsFeature], Exception
      + FullyQualifiedErrorId : FeatureMessageDetail,Microsoft.Windows.ServerManager.Commands.AddWindowsFeatureCommand

There has been more failed attempts, but I can't remember all that I have done that has failed, I have had people breathing down my neck all day, so I skipped the recording what I have tried step in the name of speed... Any help would be appreciated.


texts: Postgres Major version upgrade 9.4 -> 9.6. PITR compatibility across versions?

texts:
Just dug into postgres documentation and couldn't get a clear answer on this. With Debian Jessie hitting EOL for Debian LTS in June, we are upgrading our database servers along with our other servers to Stretch. This brings postgres-9.6 into the limelight. We've successfully upgraded test server 9.4 clusters via pg_upgrade which the Debian apt package automatically triggers notifications about when it notices more than one postgres version during the big 'ol dist-upgrade. We employ continuous archiving and PITR via a cron job and the following method: https://www.postgresql.org/docs/9.6/continuous-archiving.html

My question is, would a PITR restore with a WAL segment made with the 9.4 cluster be restore-able into the 9.6 cluster that results from the upgrade? I'm a lowly software dev with a DevOps focus and I don't want to make our DBA's uncomfortable with a from-the-hip-upgrade that might break backup compatibility. What's everyone's experience?


texts:
I am new to MySQL TRIGGER. I want to know is it possible to check before insert using last record found?

Table AccountChecking

 ====================================================
 || ID ||  ACID  || Debit  ||    Credit   ||  Saldo ||
 ====================================================
 || 1  ||   10    || 0      ||      0     ||  100   ||
 || 2  ||   20    || 20     ||      0     ||  120   ||
 || 3  ||   30    || 0      ||      20    ||  100   ||
 ====================================================

So, what I want trying to do in Mysql is : check the inserted last Saldo with ( Debit or Credit and Saldo ) addition to Table AccountChecking , if not match the MySQL itself reject the inserted Query.

This is my Query :

DELIMITER $$

CREATE
    TRIGGER `DB`.`balanceCheck` BEFORE INSERT
    ON `DB`.`AccountChecking`
    FOR EACH ROW BEGIN
        IF(NEW.Saldo != (SELECT (SALDO+NEW.Debit-NEW.Credit) FROM AccountChecking WHERE ACID='10' ORDER BY ID DESC))
        THEN
            signal SQLSTATE '00000' SET message_text = "no no no DONT INSERT !";
        END IF;
    END$$

DELIMITER ;


texts: Which terms are used to refer a row in database?

texts:
I am creating a Java application, in which I want to create a class that will relate to a row in database.

I decided to use Entity while naming the class with a prefix/suffix. But,
This page says:

  An entity is a lightweight persistence domain object. Typically, an entity represents a table in a relational database, and each entity instance corresponds to a row in that table.

I am too conscious & put much time while naming classes, packages etc.

So, what are the other terms, a row in a database can be referred with?


texts: Best practices to segment people and attributes for advertising database

texts:
we have a database where we store people some attributes per person in the following tables:

people (id, name, email, phone)
people_attributes (id, person_id, attribute_name, attribute_value)
people_location (id, person_id, location_id) (links to locations table)
people_devices (id, person_id, device_id)
people_metrics (id, person_id, metric_type, value) -- very very large table

I need to be able to run queries like this one:

Get all people that  (
    live in Moscow
    OR
    live in Athens
    OR
    live in Istanbul
    OR
    live in San Francisco )
AND
have an iPhone
AND ( EITHER
          have at least one metric of type X
          OR
          have at least one metric of type Y )

How would you go about achieving that?

Currently using an SQL Server 2012 which takes minutes for the above query. Posting the query itself here is irrelevant as I am looking for best practices to achieve the above which may mean a re-architecture / re-engineering

There is budget to upgrade to SQL Server 2014 or switch to other databases or cloud offerings.

Thanks


texts: Are 2 INSERT statements guaranteed to complete sequentially?

texts:
We've got a system that receives event from clients and stores them in a database.

Each event is saved as an atomic INSERT, and has an id_event which is an auto-incremented primary key.

Our clients can execute a specific action that causes 2 events to get fired in rapid succession:

1) name: 'board-added', boardId: 12344
2) name: 'board-switched', boardId: 12344

Both events arrive at our server in the correct order.

However, for some reason we keep finding some board-switched events having an id_event that precedes it's corresponding board-added event.

As you can see in the below result set, board-added has a higher id_event than board-switched which is wrong.

{name: "board-switched", boardId: 313994, id_event: 48041136}
{name: "board-added", boardId: 313994, id_event: 48041137}

My question is:

Is it possible that 2 INSERT events, executed sequentially will cause the 2nd INSERT to complete before the 1st INSERT?


texts:
I have a MySQL table of articles. It's growing rapidly, and over 50% of the size on disk is accounted for by the article body field, as you might expect. There are no indexes on the body, but 8/16 fields on the table are indexed.

My question is: going forward, will it be more efficient if I move the article body into a separate table? Will I gain more than I lose by forcing extra joins? Average body length is 3.3 kB and maximum length is 414 kB.

CREATE TABLE `articles` (
    `id` CHAR(32) NOT NULL DEFAULT '',
    `site_id` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
    `trend_id` CHAR(32) NULL DEFAULT NULL,
    `date_added` DATETIME NOT NULL,
    `date_scraped` DATETIME NULL DEFAULT NULL,
    `date_published` DATETIME NULL DEFAULT NULL,
    `date_shared` DATETIME NULL DEFAULT NULL,
    `language` VARCHAR(5) NULL DEFAULT NULL,
    `reach` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
    `title` TINYTEXT NULL,
    `summary` TEXT NULL,
    `keywords` TEXT NULL,
    `thumbnail` TEXT NULL,
    `body` MEDIUMTEXT NULL,
    `url` TEXT NOT NULL,
    `status` ENUM('published','pending','disabled') NOT NULL DEFAULT 'published',
    PRIMARY KEY (`id`),
    INDEX `site_id` (`site_id`),
    INDEX `date_published` (`date_published`),
    INDEX `language` (`language`),
    INDEX `reach` (`reach`),
    INDEX `trend_id` (`trend_id`),
    INDEX `date_added` (`date_added`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;


texts: Versioning a dataset in an RDBMS using initials and deltas (xpost from SO)

texts:
I'm working on a system that mirrors remote datasets using initials and deltas. When an initial comes in, it mass deletes anything preexisting and mass inserts the fresh data. When a delta comes in, the system does a bunch of work to translate it into updates, inserts, and deletes. Initials and deltas are processed inside long transactions to maintain data integrity.

Unfortunately the current solution isn't scaling very well. The transactions are so large and long running that our RDBMS bogs down with various contention problems. Also, there isn't a good audit trail for how the deltas are applied, making it difficult to troubleshoot issues causing the local and remote versions of the dataset to get out of sync.

One idea is to not run the initials and deltas in transactions at all, and instead to attach a version number to each record indicating which delta or initial it came from. Once an initial or delta is successfully loaded, the application can be alerted that a new version of the dataset is available.

This just leaves the issue of how exactly to compose a view of a dataset up to a given version from the initial and deltas. (Apple's TimeMachine does something similar, using hard links on the file system to create "view" of a certain point in time.)

Does anyone have experience solving this kind of problem or implementing this particular solution?

Thanks!


texts: Why GRANT UPDATE on SEQUENCE in PostgreSQL

texts:
I have seen postgresql allowing users to Grant update on a sequence. But never seen any update query on the sequence.
Sequence values are usually changed using ALTER SEQUENCE command. I understand the need for GRANT USAGE and GRANT SELECT on sequences. But what exactly does a GRANT UPDATE on a sequence lets you do in PostgreSQL.


texts: How to re-simulate a long query stuck in mysql queue?

texts:
BACKGROUND

Occasionally, in my live web app, I'll get a query that locks up the database for 1-2 minutes. Sometimes it is hard to predict which queries will do this until the load gets to a certain point, or a particular user with a particular set of data runs through that query.

The current solution I have for handling this (and being aware of it), is a crude crontab script which checks "Threads_running" in "show global status" of the mysql server. If the number is over 30, then I log out all the queries with "show full processlist"

Im finding that i'll get a long query stuck query about ~ once a week.

THE QUESTION

Once I find this query, sometimes EXPLAIN will help me figure out why its slow. But sometimes, I just want to re-simulate it happening. When I run the query again, it is fast, (because of query cache?). Then I run RESET QUERY CACHE and FLUSH QUERY CACHE, and expect to be able to simulate that slow query, but it is fast still. Why?

ALSO, side note: I'm curious how other people monitor long queries?


texts: How do I select items from a table where a single column must contain two (or more) values?

texts:
I have a MySQL database table that references different words and their locations in documents. I want to return the IDs of documents that contain all of the words.

Here is an example table.

docid     wordid
1         4
2         4
1         2
1         5

Ok, now say that someone queried the database for the words that had WORDIDs 4, 2, and 5.

My erroneous SQL SELECT statement would be something like:

Select docid from table where wordid = 4 and wordid = 2 and wordid = 5

This is giving me 0 results.

I have seen in other places where the where in clause has been suggested:

If I understand correctly, this is another way to write an OR clause. I have tried this:

select docid from table where wordid in (4,2,5)

But, this is giving me all the results. It should exclude docid 2 as that does not contain the other words. I'm expecting to just get docid 1.

However, I could be using the where in clause incorrectly as I have very little db experience.

How can I return docids that contain all of the words?

Please note as well, my where clause will be dynamically generated in a FOR loop.  The query could be as simple as one or two words, or it could be 10 or 12 words. I'm looking for a query structure that takes speed into consideration. Please let me know if you need anymore information.

For reference, I am trying to convert this code into PHP / MYSQL, but I don't understand the sql statement here or its equivalent in MYSQL:

http://my.safaribooksonline.com/book/web-development/9780596529321/4dot-searching-and-ranking/querying


texts:
Hellow Good Afternoon, I have created a visual representation of my 2 tables and here they are.

Before I ask my question I want to explain this both tables. The table tbl_AdMastefile is the Masterfile of my system which will be the basis of comparison from my tbl_GMFormat.

My tbl_GMFormat has 3 important columns and they are Count Date,Gondola Type and No.Of Units but all of them will be used.

Little story about this tbl_GMFormat : Everytime I will import an Excel file in my program and I will fetch those data.

How can I achieve this ?

Let me explain the Image above, I want to compare those data that is same on the following columns Outlet Name and Gondola Type If the data has no match then leave it blank or else compare them.

I hope someone helps me.


texts:
Good morning.
Mysql 5.7.25:

insert into mytable values('2019-03-31 02:06:29')

return error "#1292 Incorrect datetime value"

but:

insert into mytable values('2019-03-31 03:06:29')

return no errors.
(the type of the field is timestamp)

Where am I wrong ?

Thanks

Chico


texts:
Can you use mirroring to upgrade SSRS?

For instance, I've upgraded from SQL 2005 to SQL 2014 with mirroring.

SSRS is running on the same box as my database server.  I'm going from SQL 2012 (11.0.5343) to 2014 SP1.

I was hoping to get info from the community to upgrade in the easiest way with any possible caveats.


texts: Can one database affect the performance of another in MongoDB?

texts:
We have a fairly beefy server (15 GB RAM, 2 cores) running a reasonably large MongoDB instance. The Mongo instance hosts two separate databases that communicate via application code. The larger database (let's call it DB1) contains on the order of 100 million records with more being added all the time.

The second DB (let's call it DB2) contains about 10 million records and grows much more slowly (there are a lot of writes, but most are updates).

Recently, we noticed that reads on DB2 have become ridiculously slow, taking up to 2-3 minutes to simply loop through about a 1000 records. On profiling the query, it looks like the right indexes are set and are being used, but that the performance for some as-yet unknown reason has completely fallen off a cliff.

Considering that DB2 doesn't grow much, could this be because DB1 is affecting the performance (maybe by competing for RAM)? Is there a more direct mechanism for two databases to interfere with each other?

I'm trying to identify the bottleneck here, so any pointers in the right direction would be much appreciated.

I should also note that while DB1's performance has also degraded a bit, it hasn't completely fallen off a cliff like DB2.


texts:
Does anyone have a Theil-Sen regression function written in T-SQL?

I found one written in Perl, but I'm not able to recode it into SQL.


texts: Importing external data with specific time format into MS Access

texts:
I need to import data from a flat file to MS Access.  Part of the data is time information in the format HH:MM:SS.SSSS (i.e. 24-hour time, fractional seconds).  I have tried all of the standard time formats, and have monkeyed around with input masking, but Access keeps complaining that I am violating formats and won't import my data.  Anybody know how to do this?  Thanks.

(BTW, the "date-format" tag was the closest I could find to something like "time format".)


texts: Restore subscriber from subscriber's backup

texts:
We have currently pull transaction replication with 1 publisher, 1 distributor and several subscribers. The replication is for several tables only.
I want to create new subscriber and this time restore it from backup. All the process and configuration is pretty simple, but I wanted to check if the following will work:

backup the publisher
backup one of the subscriber (right after no.1).
copy subscribers backup (which is much lighter) to the future subscriber and restore
define the new subscriber.

From my tests it is picking data and works ok, but I didn't test it heavily yet.
Is this practical?

Thanks in advance


texts:
If you only look at the database everything is fine. You have transactions and if somethings goes wrong everything gets rolled back. That's nice - I like this.

BUT: I want to send mails. Now I am in trouble because I can't rollback.

example:

transaction starts
Mail gets send
Other stuff gets done (inside DB)
Something goes wrong.
Rollback.

How to solve this is a different question, not this.

This question how to call this in general. In this example is about sending mails. But the same problem as soon as you do modify something in systems which are outside the transaction boundary.

Is there a name for this problem?

Roughly the same problem arises if you want to import files from a directory. If you delete the file inside the transaction, then the transaction might fail and the file was deleted but never imported. Or you delete the file after the transaction. Then the delete of the file might fail and the file gets imported a second time.

I don't want to reinvent a solution for this. That's why I need the matching term for this problem. Then I can read some papers and learn what's "state of the art" in the year 2018.


texts: sys.database_files and sys.master_files show different physical_name values

texts:
I received the following alert:

  The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x0000000012c000 in file 'C:\VeeamFLR\UK->SB->SQL11_4406bb77\Volume3\DATA\MyDB10_STAGE.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and...

The following script shows sys.database_files and sys.master_files have different physical_name values:

declare @dbname varchar(30)

set @dbname = (select name from sys.master_files where physical_name like 'C:\VeeamFLR\%.mdf')

select name as 'MasterFiles', physical_name as 'FullPath' from sys.master_files where physical_name like 'C:\VeeamFLR\%.%df'order by physical_name desc
exec('USE '+ @dbName + '; select name as ''DatabaseFiles'', physical_name as ''FullPath'' from sys.database_files')

Results:

MasterFiles              FullPath

------------------------ ------------------------------------------------------------------------

MyDB10_STAGE        C:\VeeamFLR\UK-SB-SQL11_4406bb77\Volume3\DATA\MyDB10_STAGE.mdf

MyDB10_STAGE_log    C:\VeeamFLR\UK-SB-SQL11_4406bb77\Volume2\Logs\MyDB10_STAGE_log.ldf

(2 row(s) affected

DatabaseFiles            FullPath

------------------------ ------------------------------------------------------------------------

MyDB10_STAGE        E:\DATA\MyDB10_STAGE.mdf

MyDB10_STAGE_log    D:\Logs\MyDB10_STAGE_log.ldf

(2 row(s) affected)

The sys.database_files value is correct. (Sys.master_files refers to a previous Veeam restore mount point. Not sure why). I've tried a detach and re-attach with the correct path specified:

-- detach:
ALTER DATABASE MyDB10_STAGE SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'MyDB10_STAGE'
GO

-- No need to move physical files as actually already in correct location

USE [master]
GO
CREATE DATABASE MyDB10_STAGE ON
( FILENAME = N'E:\DATA\MyDB10_STAGE.mdf' ),
( FILENAME = N'D:\LOGS\MyDB10_STAGE_log.ldf' )
FOR ATTACH
GO

--Put database in multi user mode again:

ALTER DATABASE MyDB10_STAGE SET MULTI_USER
GO

But this has not updated physical_name in sys.master_files. Can anyone recommend how to update physical_name in sys.master_files to the correct value please?


texts: Performance issue with sp_executesql and VARCHAR parameter

texts:
Table Segments has an index by DEPARTMENT (VARCHAR(10)) and BDPID(VARCHAR(10)).

Execution time of the first query is 34 seconds

SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
FROM Customers c
LEFT JOIN Segments seg
     ON   seg.DEPARTMENT  = 'DEP345'
     AND  seg.BDPID       = c.BDPID

When I moved the parameter for DEPARTMENT to a variable, execution time became 1 second. Execution plan #2 (fast)

DECLARE @dd VARCHAR(10)
SET @dd = 'DEP345'
SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
FROM Customers c
LEFT JOIN Segments seg
     ON   seg.DEPARTMENT  = @dd
     AND  seg.BDPID       = c.BDPID

But I have to use dynamic sql. And when I moved query to sp_execitesql the execution time again became 34 seconds. Execution plan #3 (slow)

EXECUTE sp_executesql
     'SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT
     FROM Customers c
     LEFT JOIN Segments seg
          ON   seg.DEPARTMENT  = @dd
          AND  seg.BDPID     = c.BDPID',
     '@dd VARCHAR(10)',
     @dd = 'DEP345'

How can I get performance of the second query using dynamic sql?


texts: mysqld startup/initialize permission demied - can't connect

texts:
The MariaDB server (5.5.35?) which is preinstalled on my linux system came broken, and instead of trying to fix it (#1 it's out of date, #2 I might break the system in the process since it's pre-installed), I downloaded MySQL 5.7.16 (the .tar.gz version, see #2 for reason). Then I changed the data folder, logs folder, etc. to get away from MariaDB and put them in my own my.cnf file. Also made a symbolic link called '/usr/local/mysql' to my actual MySQL installation in my home folder.

I'm having trouble configuring/running mysqld as mysql user (which came with MariaDB), but it can run well as root (yes, I know the security risks but the server is only for testing).

It seems to have trouble creating some files for writing.
The following is from running mysqld with mysql user & personal my.conf:

    2016-12-05T17:22:26.420468Z 0 [ERROR] InnoDB: Cannot open '/home/me/Downloads/mysql-5.7.16-linux-glibc2.5-x86_64/data//ib_buffer_pool' for reading: Permission denied

    miscellaneous failures/server shutdown as a result

    ...

    2016-12-05T17:22:26.729869Z 0 [ERROR] InnoDB: Cannot open '/home/me/Downloads/mysql-5.7.16-linux-glibc2.5-x86_64/data//ib_buffer_pool.incomplete' for writing: Permission denied

Initializing the database gives the same error  but woth'data/mysql/db.MYI' instead of data/ib_pool.

Here are the file ownerships for mysql (I didn't provide file permissions in case that's irrelevant):

All folders except data and mysql-files: My home user
data and mysql-files: mysql

Groups are the same as the owners.

Update: changing /usr/local/mysql permissions let me initialize/start the server, but I still can't connect to it on any user (even root@localhost gets Access Denied), when  I removed /usr/local/mysql link and redirected all paths to my home dir, problem came back again.

Can someone help me use another user besides root?


texts: MSSQL Hashbytes produces different output, string vs column value

texts:
The following SQL

SELECT CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', 'test'), 2) as 'From String'

produces the following hash:

EE26B0DD4AF7E749AA1A8EE3C10AE9923F618980772E473F8819A5D4940E0DB27AC185F8A0E1D5F84F88BC887FD67B143732C304CC5FA9AD8E6F57F50028A8FF

If I then replace 'test' with the column value from [Key] instead like this:

SELECT CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', [Key]), 2) as 'From Column' FROM MyTable
WHERE [Key] = 'test'

The following hash is produced:

9F7D8627E02F97CC5A52DCB2BA96038FE12F2A34B0FAC50E041359AE13D5EDE8A8A50562DA58BA7916DA378E7343EF91E85EFBD6A0A70AB237ADA4C2274DF13D

Right now we have a couple of rows in our database that I would like to hash, so I want to run the following code:

UPDATE MyTable SET [Key] = CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', [Key]), 2)

But the problem is that the hash produced is not correct, it's only correct if I replace [Key] with the actual string value.

I found this question: Using HASHBYTES() yields different results for nvarchar and a variable and there they "solve it" by prefixing the string with N. But I want to do it the other way around, I want the output to be
EE26B0DD4AF7E749AA1A8EE3C10AE9923F618980772E473F8819A5D4940E0DB27AC185F8A0E1D5F84F88BC887FD67B143732C304CC5FA9AD8E6F57F50028A8FF.

How can I achieve that?

My table looks like this:

CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Key] [nvarchar](128) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


texts: SQL Server's BACKUP DATABASE - how to access the original exception?

texts:
DECLARE @DbName SYSNAME;
SET @DbName = 'MyDatabase';

BACKUP DATABASE @DbName
    TO DISK = 'C:\MyDatabase.bak'
    WITH INIT,
    NAME = 'MyDatabase-FullBackup',
    STATS = 5;

This, sure enough, is a problematic code, given you're running under NETWORK SERVICE privileges. The output I am getting from it (because I am trying to write to c:\ to which NETWORK SERVICE doesn't have write access) is following when running in SSMS:

  Msg 3201, Level 16, State 1, Line 4
  Cannot open backup device 'C:\MyDatabase.bak'. Operating system error 5(Access is denied.).
  Msg 3013, Level 16, State 1, Line 4
  BACKUP DATABASE is terminating abnormally.

Both surface in SSMS's Query Analyzer output as exceptions, but it looks like (maybe I'm talking gibberish here) that the first filesystem-related one is handled internally by the BACKUP statement which then replaces the message with a more generic "backup is terminating abnormally".

If I then try to handle the exceptions in my script like so

BEGIN TRY

  DECLARE @DbName SYSNAME;
  SET @DbName = 'MyDatabase';

  BACKUP DATABASE @DbName
    TO DISK = 'C:\MyDatabase.bak'
    WITH INIT,
    NAME = 'MyDatabase-FullBackup',
    STATS = 5;

END TRY
BEGIN CATCH
  SELECT  ERROR_NUMBER()
        , ERROR_MESSAGE()
END CATCH

then I am only intercepting (well, in this case only showing with SELECT) the 3013 exception, obviously.

Question: is it at all possible to programmatically access the first exception (as it makes far better sense for logging problems during the script execution)?

Thank you!


texts: Database stored procedure with a "preview mode"

texts:
A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this parameter indicates that the results of the action should be returned, but the procedure should not actually perform the updates to the database.

One way to accomplish this is to simply write an if statement for the parameter, and have two complete code blocks; one of which does updates and returns data and the other just returns the data. But this is undesirable because of the code duplication and a relatively low degree of confidence that the preview data is actually an accurate reflection of what would happen with an update.

The following example attempts to leverage transaction savepoints and variables (which are not affected by transactions, in contrast to temp tables which are) to use just a single block of code for the preview mode as the live update mode.

Note: Transaction rollbacks are not an option since this procedure call may itself be nested in a transaction. This is tested on SQL Server 2012.

CREATE TABLE dbo.user_table (a int);
GO

CREATE PROCEDURE [dbo].[PREVIEW_EXAMPLE] (
  @preview char(1) = 'Y'
) AS

CREATE TABLE #dataset_to_return (a int);

BEGIN TRANSACTION; -- preview mode required infrastructure
  DECLARE @output_to_return TABLE (a int);
  SAVE TRANSACTION savepoint;

  -- do stuff here
  INSERT INTO dbo.user_table (a)
    OUTPUT inserted.a INTO @output_to_return (a)
    VALUES (42);

  -- catch preview mode
  IF @preview = 'Y'
    ROLLBACK TRANSACTION savepoint;

  -- save output to temp table if used for return data
  INSERT INTO #dataset_to_return (a)
  SELECT a FROM @output_to_return;
COMMIT TRANSACTION;

SELECT a AS proc_return_data FROM #dataset_to_return;
RETURN 0;
GO

-- Examples
EXEC dbo.PREVIEW_EXAMPLE @preview = 'Y';
SELECT a AS user_table_after_preview_mode FROM user_table;

EXEC dbo.PREVIEW_EXAMPLE @preview = 'N';
SELECT a AS user_table_after_live_mode FROM user_table;

-- Cleanup
DROP TABLE dbo.user_table;
DROP PROCEDURE dbo.PREVIEW_EXAMPLE;
GO

I'm looking for feedback on this code and design pattern, and/or if other solutions to the same problem exist in different formats.


texts: How do I map an IS-A relationship into a database?

texts:
Consider the following:

entity User
{
    autoincrement uid;
    string(20) name;
    int privilegeLevel;
}

entity DirectLoginUser
{
    inherits User;
    string(20) username;
    string(16) passwordHash;
}

entity OpenIdUser
{
    inherits User;
    //Whatever attributes OpenID needs... I don't know; this is hypothetical
}

The different kinds of users (Direct Login users, and OpenID users) display an IS-A relationship; namely, that both types of users are users. Now, there are several ways this can be represented in an RDBMS:

Way One

CREATE TABLE Users
(
    uid INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(20) NOT NULL,
    privlegeLevel INTEGER NOT NULL,
    type ENUM("DirectLogin", "OpenID") NOT NULL,
    username VARCHAR(20) NULL,
    passwordHash VARCHAR(20) NULL,
    //OpenID Attributes
    PRIMARY_KEY(uid)
)

Way Two

CREATE TABLE Users
(
    uid INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(20) NOT NULL,
    privilegeLevel INTEGER NOT NULL,
    type ENUM("DirectLogin", "OpenID") NOT NULL,
    PRIMARY_KEY(uid)
)

CREATE TABLE DirectLogins
(
    uid INTEGER NOT_NULL,
    username VARCHAR(20) NOT NULL,
    passwordHash VARCHAR(20) NOT NULL,
    PRIMARY_KEY(uid),
    FORIGEN_KEY (uid) REFERENCES Users.uid
)

CREATE TABLE OpenIDLogins
(
    uid INTEGER NOT_NULL,
    // ...
    PRIMARY_KEY(uid),
    FORIGEN_KEY (uid) REFERENCES Users.uid
)

Way Three

CREATE TABLE DirectLoginUsers
(
    uid INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(20) NOT NULL,
    privlegeLevel INTEGER NOT NULL,
    username VARCHAR(20) NOT NULL,
    passwordHash VARCHAR(20) NOT NULL,
    PRIMARY_KEY(uid)
)

CREATE TABLE OpenIDUsers
(
    uid INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(20) NOT NULL,
    privlegeLevel INTEGER NOT NULL,
    //OpenID Attributes
    PRIMARY_KEY(uid)
)

I'm almost certain the third way is the wrong way, because it's not possible to do a simple join against users elsewhere in the database.

My real world example is not a users with different logins example though; I'm interested in how to model this relationship in the general case.


texts: Is it possible to restore a SQL Server 2012 Standard backup to a SQL Server 2012 Web Edition instance?

texts: If I take a backup from a SQL Server 2012 Standard edition instance can I restore it to a SQL Server 2012 Web edition instance?


texts: Granting a user a permission to restore a database

texts:
In a database I created a user with the following roles:

Server Roles

public

Database roles

db_backupoperator
db_ddladmin
db_datareader
db_datawriter

The problem is I don't want to change the user's roles, but I do want grant the permission to restore the database.

Is there a workaround to only grant the permission to restore but not to drop nor alter the database?

Background info:

The company I work for created an app and they want the users of that app to have the permission to backup and restore the database, but not to create another database or drop the existing one. And, they don't want to have another user who has the role of db_owner, which would allow the customer to manipulate things as they please.

The price for the app is based on the numbers of employees a client has, so while installing the app on the client side they use a file to create a database where they specify all the details (including the number of employees), but they fear that someone might change the file and add the database multiple times, so they want to secure the instance by not granting any user the right to do anything. It's complicated but it is what they want.


texts: Running several versions of a MySQL database in parallel?

texts:
We have a central read-only MySQL database that contains geo data, etc. It
is used by various projects. As part of a refactoring, I am thinking about
setting up a system for versioning the database. The rough idea so far:

The current database is version 1. It is available at:

1.database.example.com:3306/database_1

When in the future the database structure is changed in an incompatible
way, then the version number is increased to 2, and the new database is
made available at:

2.database.example.com:3306/database_2

That allows updating projects one by one from interfacing with
1.database.example.com to interfacing with 2.database.example.com. Due
to the different domain name, it is possible to use a different
database server for 2.database.example.com, even one not based on
MySQL.

Does that make sense? Is there a standard or recommended way for “versioning” a
read-only database?


texts: Switch user to 'postgres' user on macOS results in "su: Sorry" error

texts:
The Postgres installers kindly provided to the community by EnterpriseDB.com creates a postgres user account on macOS as is common.

In a console session in Terminal.app, I need to switch to that user. My goal is to run a text editor with that user’s privileges so that I can edit the pg_hba.conf file controlling Postgres user-authentication rules.

When I try:

su - postgres

…I am prompted for the user's password, which I enter successfully, yet get the error message:

  su: Sorry

I am sure the password is correct. What can I do to access the postgres user?


texts: Lookup transformation not seeing new records

texts:
I'm trying to load data from one database to another. I am using the lookup transform that looks at the target table to see if the record already exists.

I am getting duplicate data when the package succeeds.

Why doesn't the lookup transform refresh the table it's looking at every time it is being run?

Any ideas on how to create the lookup transformation so that it will refresh its connection to the table?

I want to keep the records I already have, and add any new records but the package should not load any duplicates.

How do I make sure not to get duplicated data by making sure the lookup transformation sees the newly added data immediately?


texts: Error installing SQL Server 2008 - all componets installed successfully except database engine

texts:
Below is the error I copied, please someone help me, I have tried at least 10 times in the last 3 days. It is SQL Server 2008 R2 and I wanted to install it on Windows 7.

Microsoft SQL Server 2008 Setup
The following error has occurred:
Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.0.1600.22&EvtType=0xE53883A0%25400xBE03358B%25401306%254024


texts:
I'm confusing myself a lot here. I did this before however have never got the hang of it. So I really need help, main issue is 2nf I just get the dependencies completely wrong. So I am practising ready for next year really. So yes this would be classed as homework.

Here is the original document I have to preform normalization on.

The form is link no1

My attempts are no2 and no3

So, basically. Where am I going wrong? Why am I going wrong? Can you explain how to get back on track without doing it obviously. I know it's shocking, but I never understood all this :/ fine with sql but this side of things, not a chance.

Here is another attempt, what one is close and why?


texts: I'm scripting out a fairly simple database so I can simply hand off a script to someone else for an application to use.  I'm curious about where best practice/the community stand on this: When should you create your primary/foreign key constraints?  Should you do it while you are creating the tables because of the continuity, or should you do it after the tables are created since it will be easier to script that way since you can create the tables in any order you wish?  Are there any potential fatal flaws down the road with either pattern?


texts: mongoDB design - to embed or not to embed

texts:
Let's say I have a mongoDB database with 3 collections:

widgets
thingomajiggers
locations

The way the application is going to be used that I will always force the user to specify a location first.  and then from there, they can drill down into the type object they are looking to read / create / update - namely either a widget or a thingomajigger.

So in this case, would it be better to have a separate location collection and refer to this collection from the documents in widgets and thingomajiggers, or should I embed location information right inside my widget / thingomajigger documents?

Currently my prototype is small so I can't imagine me really noticing performance differences.  But eventually I will have hundreds of thousands of widgets and other object types.

Thanks.


texts: Prevent a user from making changes to some columns in a MS SQL Server database

texts:
I'd like to prevent a user from making changes to some columns in a MS SQL Server database. I cannot lock the column as READ ONLY as there will be changes made to that column. Let me explain as best as I can. Our ERP allows me to block certain fields/columns from users through their security tabs. Sounds great. However, if a person uses an ODBC or DSN connection they would be able to change data. As an example I don't want the user to be able to change costs or anything financial in a customer order table, but have no issue with him correcting incorrect addresses or shipping information. If I lock the table as READ ONLY then using the ERP would also block that column.
I hope i'm making sense here.
Thank you!


texts: How to design a relationship database table to store friendship relationship?

texts:
I want to design a table to store friendship relationship in my web project
It should satisfy at least the following 4 conditions:

who send the add-friend request   e.g.(if A TO B then this column will be A)
who receive the add-friend request e.g.(if A TO B then this column will be B)
current status e.g.(0 denotes rejected whereas 1 denotes accepted or 2 denotes unprocessed
our friend relationship is bilateral

If any of you are experienced with this , any suggestion is welcomed
my current design (I think bad right now) is like this these are the columns
frienshipId
fromUserId
toUserId
status
requestTime


texts: Choosing database for large set sum and difference

texts:
I am creating an application with one server node and multiple clients.
Each client holds a set of strings, that will be stored in their own local database.
Once a day, all clients will send all their data to the server. The server will calculate the sum of all clients' sets, and then calculate the difference between this total set and each client's.
Small example:
Client1 has {1, 2, 3}
Client2 has {1, 2}
Client3 has {1, 3}
Client4 has {}
They send it to the server, that calculates Client1 + Client2 + Client3 + Client4 = {1, 2, 3} and server persists this result in it's own database.
Then server calculates difference.
Client1 lacks {}
Client2 lacks {3}
Client3 lacks {2}
Client4 lacks {1, 2, 3}
As those set of strings are going to be quite big, I would like to choose a database (SQL or NoSQL), that will be able to optimize those two operations.
Application is written in Spring Boot and will run on Ubuntu. Database needs to be open source. I also need full-text-search. In future there may be multiple server nodes, and they should be at least "eventually consistent".


texts: Combine results of two queries, where the second relies on the first

texts:
I have one query which is returning

name(text), total, created_at

And another query which is returning the same thing

name(text), total, created_at

The second query usually is returning more rows, but I only need the rows where the query1.created_at = query2.created_at

Example:
Query 1 returns:

test, 10, 2015-03-06
test, 12, 2015-03-07

Query 2 returns:

newtest, 14, 2015-03-05
newtest, 15, 2015-03-06
newtest, 9, 2015-03-07

The expected output:

test, 10, 2015-03-06
test, 12, 2015-03-07
newtest, 15, 2015-03-06
newtest, 9, 2015-03-07

QUERY 1:

SELECT
  co.name
  sum(c.total)
  c.created_at
FROM c_report c
INNER JOIN contact co on co.id = c.contact_id
GROUP BY co.name, c.created_at

QUERY 2:

SELECT
  po.name
  sum(p.total)
  p.created_at
FROM p_report p
INNER JOIN person po on po.id = p.person_id
GROUP BY po.name, p.created_at


texts: MySQL Server 5.7.11 working with MEB 4.0.3?

texts:
Is it possible to create a backup with mysqlbackup.exe using the MEB 4.0.3 tool on MySQL 5.7.11?
For me it only worked with the 5.7.17 version. Am I supposed to use an older version of MEB in order to make it work?


texts: Multiple tables for different types of transactions?

texts:
Considering a trading system as an example. Would it make sense to have two different tables for credit transactions and asset transactions?

creditTrans : table
id
userId
transType
status
amount
timestamp

assetTrans : table
id
assetId ** fk to asset table
userId
transType
status
amount
timestamp

There are different types of transaction: Purchase, Sale, Deposit, Withdrawal

**Deposit and withdrawal are for fiat currency like dollar and buying/selling assets

In Purchase & Sale Transactions, both tables will have data but in Deposit & Withdrawal only creditTrans data will be inserted.

I'm actually in doubt for over a month whether to merge this two table into one (and have a nullable foreign key (assetId) for deposit/withdrawal transaction) or keep a separate table for different kinds (avoid nullable - have to insert multiple table in purchase/sale transactions).

Additional details

This is the related database schema that I've come up with so far.


texts: Maximum number of fields/columns in MongoDB

texts: I'm considering learning and using MongoDB for a project, but I'm going to need to be able to store at least 1024 columns for the first part, and as many as 1048577 for the second. Does MongoDB have a limit on the number of columns?


texts: RAC, data guard, physical stand by and impdp

texts:
We're setting up a RAC 2 nodes data guard 11g environment, with a remote physical standby (2 nodes also)

We receive the client's data he exports with expdp. We have imported his dump in the primary database a first time for our tests. The day we go to production we will have to import it again. We will probably have to drop the data before doing so, so will we have to disconnect the data guard environment before we proceed? What advice would you give me in this context?

Thank you,

S.


texts: Enforcing minimum cardinality in One To many design?


texts:
I have a table (players) containing a list of players, I would like to pair up these players in a unique way, so that every player's opponent is unique every round (ie. every time the SELECT query is called).

The player_opponent_log column is an integer[] which contains the player_ids of players who have played with that player in a previous round (and is used to help pick out unique players). This column is populated afterwards using the results of the SELECT query - and is outside the scope of this question.

The table for instance would have the following data;

 player_id | player_opponent_log
-----------+---------------------
         1 | {2,3}
         2 | {1}
         3 | {1}
         4 | {}
         5 | {}
         6 | {}
         7 | {8}
         8 | {7}

What I am trying to achieve would be something along the following lines:

 player_id1 | player_id2
------------+------------
          1 |          4
          2 |          3
          5 |          7
          6 |          8

I have tried countless different approaches, including GROUP BY, DISTINCT ON (), self JOINS, but haven't been able to come to a working solution.

The following result is what I am currently getting and am trying to avoid:

 player_id1 | player_id2
------------+------------
          1 |          4
          2 |          3
          3 |          4
          4 |          1
          5 |          1
          6 |          1
          7 |          1
          8 |          1

Where I'm getting stuck is how to eliminate a single player being allocated for a round more than once per SELECT query.

Any ideas on how to solve this would be highly appreciated.


texts:
I created some objects in MS SQL Server 2008 using SubSonic 3. It created them based on the user specified in connection string like:

test_user.table1

but for some reason I am not able to test_user account. Now I am not able to access the tables within test_user schema. Is there any way to transfer schema from test_user to dbo so that any login can access it?


texts: postgres dyanmic and unlimited where conditions

texts:
How would one properly write a FUNCTION in postgres to return a TABLE based on a query where the query WHERE clause can have both an unknown number of filter conditions, and the conditions themselves are dynamic?

I feel like this should be a solved problem, and maybe it is, but I am unable to find any examples in postgres.

The type of conditions in involved in building one filter condition would be dynamically setting AND, OR, NOT conditions, =, >, <, >=, <=, etc. for a given column's values.

Then multiply this by an unknown n number of possible filter conditions.

In my scenario at the moment, there are nearly 70 different numerical measures which could be used to compose the where clause. Again, each of these measures could have AND, OR, NOT conditions, =, >, <, >=, <=, etc. applied. Moreover, each one of these 70 different numerical measures could be used more than once if different equality operators and low/high limit ranges are applied. At a 30,000 ft view, think of the problem as passing all the conditions you could generate in a tool like this to a postgres query. Essentially you would be translating into a postgres query the results of a web app end-user who is building a cohort from visual tools with 70+ measures at his/her disposal.

What is the best way to go about this in postgres? Pass a json array of objects as a function parameter, convert them to a table, and base the dynamic where in some manner off of that table? Run a LOOP based off the conditions? Other? Appreciate any help getting un-stuck on this challenge.


texts: whats the fastest way to copy data from table to new one?

texts:
Whats the fastest way to copy table "table_1" to new table called "table_2" ?

I'd like the constraints copied as well as the data.


texts: Can i get a SQL Server catch Updates to reflect the responsible objectid with a database trigger?

texts:
I have a table that runs several stored procedures and stores the resulting rows in the target table [TestTable]

[TestTable] is first populated by the stored procedure [spInsertRowToTestTable] and then Updated by [spUpdatesRowInTestTableFirst] and [spUpdatesRowInTestTableLast]

I can capture the responsible procedure of initial insert in the table by using a Default Constatraint to set the @@PROCID. I would like to capture Updates in a similar way, without modifying existing procedures that result in updates to the row.

I have tried, but I have not been able to create a trigger or a similar solution that captures any subsequent Updates. The scope of the calling procedure aludes my atempts to catch it in a trigger and update the row.

Is there a straight forward way to do this?

See example code below:

--DROP SCHEMA test;
--GO
--DROP TABLE test.TestTable;
CREATE SCHEMA test;
GO

CREATE TABLE test.TestTable
    (
      Column1 NVARCHAR(20) NULL ,
      RowInsertedBySP NVARCHAR(30)
        NULL
        CONSTRAINT DF_TestTable_RowInsertedBySP DEFAULT OBJECT_NAME(@@PROCID) ,
      RowUpdatedBY NVARCHAR(20)
    );
GO

CREATE PROCEDURE test.spInsertRowToTestTable
AS
    INSERT  INTO test.TestTable
            ( Column1 )
    VALUES  ( 'TestRow' );
GO

CREATE PROCEDURE test.spUpdatesRowInTestTableFirst
AS
    UPDATE test.TestTable
    SET Column1 = 'Updated ' +[Column1];
GO

EXEC test.spInsertRowToTestTable;

SELECT  *
FROM    test.TestTable AS tt;

Resulting in:

Column1              RowInsertedBySP                RowUpdatedBY
-------------------- ------------------------------ --------------------
TestRow              spInsertRowToTestTable         NULL

(1 row(s) affected)

I would simply like to catch the ids of the procedures that do Updates on the rows in column [RowUpdatedBy].

i.e if i have [spUpdatesRowInTestTableFirst], [spUpdatesRowInTestTableLast]. The column [RowUpdatedBy] should then reflect the updates like this:

Column1              RowInsertedBySP                RowUpdatedBY
-------------------- ------------------------------ --------------------
TestRow              spInsertRowToTestTable        spUpdatesRowInTestTableFirst, spUpdatesRowInTestTableLast

I have seen a example of using DBCC Outputbuffer, but i would rather not go down that road. We use Enterprise edition, so another idea would be to leverage CDC.

I would appreciate any input.