texts: Postgres 11 - Fetching results after a transactiom

texts:
With the recent update of Postgres 11. It now supports procedures. Transactions are finally supported in stored procedures in Postgres. However, I have been trying to perform a transaction and retrieving a refcursor as the result.

CREATE OR REPLACE PROCEDURE testproc(pid text,pname text,INOUT cresults refcursor)
 LANGUAGE plpgsql
AS $procedure$

BEGIN
 cresults:= 'cur';
 begin
    update "testtable" set id=pid , name=pname where id=pid;
  commit;
 end;
    OPEN cresults for select * from oureadata.CMS_CATEGORY limit 1;
end;
$procedure$

I understand that fetching of refcursor have to be in a transaction. This is how i execute the procedure.

BEGIN;
Call oureadata.testproc('1','2','');
fetch all in cur;
commit;

When i try to fetch the cursor, it throws an exception "ERROR: Invalid transaction termination"

But if i remove the commit from the procedure. I can actually execute the procedure and fetch the result of the refcursor. (The above is just an example, as i have other more complex transaction which will return refcursor after)

So my question is, can a procedure return a INOUT refcursor result after a transaction is done?


texts:
I would like to lock a table: INSERT should be allowed, but UPDATE should be disabled.
I am using a current PostgreSQL version.
I read Advisory Locks and explicit locking. Maybe I am blind, but I could not find a suitable solution for my goal.
Here are details to my use case:
There is a table which is roughly like logging: Several processes add new rows to the table at the same time.
Once in a hour a script processes the data which was added.
Now I want to be sure that this script has a fixed working set. No modifications should be done to the rows which the script is working on. But it is ok if new rows get added while the scripts run. These rows get processes with the next call of the script.


texts:
Do we really need to run DBCC checkdb on a database configured with Always On synchronous updates?

I believe the automatic page repair mechanism should identify the corruption and repair it automatically.


texts:
I have mysql and i use a tmpfs disk that is 768 M. Mysql uses about 70% of the memory. There is about 1170 M available.

If mysql uses ram to the point there is less than 768; will my RAM disk be limited in size?


texts: Unable to update table after creating an After update trigger

texts:
I have a table like this

/*Table: pageprivilege*/
------------------------

/*Column Information*/
----------------------

FIELD   TYPE              COLLATION          NULL    KEY     DEFAULT  Extra           PRIVILEGES                       COMMENT
------  ----------------  -----------------  ------  ------  -------  --------------  -------------------------------  -------
id      INT(10) UNSIGNED  (NULL)             NO      PRI     (NULL)   AUTO_INCREMENT  SELECT,INSERT,UPDATE,REFERENCES
pageid  VARCHAR(20)       latin1_swedish_ci  YES             (NULL)                   SELECT,INSERT,UPDATE,REFERENCES
roleid  TINYINT(4)        (NULL)             YES             (NULL)                   SELECT,INSERT,UPDATE,REFERENCES
ad      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES
ed      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES
dl      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES
rd      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES
st      TINYINT(1)        (NULL)             YES             0                        SELECT,INSERT,UPDATE,REFERENCES

I have an condition that, if the rows are updated such that

ad, ed, dl, rd are all set as 0 then the value of st automatically gets changed to 0 too

For that I wrote a procedure

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `smsdev`.`update_priv`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN

     UPDATE pageprivilege SET st=0 WHERE ad = 0 AND ed =0 AND dl =0 AND rd = 0;

    END$$

DELIMITER ;

And added a trigger which gets called after the table has been updated and calls the procedure like this

DELIMITER $$

USE `smsdev`$$

DROP TRIGGER /*!50032 IF EXISTS */ `pageprivilege_update`$$

CREATE
    /*!50017 DEFINER = 'smsdev'@'%' */
    TRIGGER `pageprivilege_update` AFTER UPDATE ON `pageprivilege`
    FOR EACH ROW BEGIN
     CALL update_priv();
END;
$$

DELIMITER ;

The issue is whenever I make any update to the any of the 4 field, ad, ed, dl, rd I get this error

  Can't update table 'pageprivilege' in stored function/trigger because
  it is already used by statement which invoked this stored
  function/trigger.

I am new to using trigger and stored procedure, I did read that is a bad idea to fire procedure from inside trigger, but this is just like a test of idea for me so if you could just bear with me and help me with this hack-ish code it would be gr8.


texts: Does SQL Server support GREATEST and LEAST, if not what is the common workaround?

texts:
Reviewing this question it seems like that's a lot of work that shouldn't be needed. They're trying to extend a range with a date. In other databases, you would just use greatest and least..

least(extendDate,min), greatest(extendDate,max)

When I try to use these though, I get

'least' is not a recognized built-in function name.
'greatest' is not a recognized built-in function name.

That would cover extension in either direction.

For the purposes of the question, you would still have to do exclusive range replacement.

I'm just wondering how SQL Server users implement query patterns to mimic least and greatest functionality.

PostgreSQL GREATEST/LEAST
MySQL GREATEST/LEAST
MariaDB GREATEST LEAST
DB2 GREATEST LEAST
Oracle GREATEST LEAST

Do you unroll the conditions into CASE statements or is there an extension, third party add-on, or license from Microsoft that enables this functionality?


texts: How to exclude tables when using pg_dumpall with postgresql?

texts:
If use pg_dump, it has a --exclude-table-data option to ignore special table.

But pg_dumpall command doesn't has that option. So how to exclude several tables when dump all databases?


texts: MySQL: records, inserted via phpMyAdmin, do not replicate

texts:
As it says on the topic, for some reason records inserted/ modified via PMA do not get replicated to the slave, If I do exactly the same insert/modification on master via mysql command line, or application, it does get replicated as it should be.

Is it some security feature ? Or something wrong ? I use MySQL 5.5.28 with CentOS 6 mostly.

Any ideas why ?


texts: Multi-threaded replay of master activity for load/performance testing

texts:
Does anyone know of any existing tools/products that accomplish what I'm trying to do?

After searching around for a bit the only thing I could find surrounding what I'm trying to accomplish is someone else looking for the same thing http://forums.mysql.com/read.php?24,192250,192250

The idea is I'd like to capture all the traffic to my master to save for a replay log against a snapshot of the entire database taken when the monitoring started.  Bin logs won't serve what I want since they only include writes.  I want read activity to realistically view the effects of proposed changes with "real" production traffic.

Real production traffic meaning everything from all applications that are hitting the database to be modified.  If there's some application level change, tests running just that app don't account for other activity going on in the system at the time.  Running all applications in a test environment aren't guaranteed to have the same state of the database.  I could take a test snapshot as a starting point as I fire them up but the applications don't have the ability to do an exact replay of their own activity.

I've use the tcpdump script from http://www.mysqlperformanceblog.com/2008/11/07/poor-mans-query-logging/ to monitor activity but this doesn't tell me which queries are coming from which connections.  Part of the playback I'm wanting is a multi threaded approach that replays the activity from the same number of threads that were actually in use.

I can't afford to turn on general query logging b/c my production master wouldn't be able to handle the performance hit for that.

The whole snapshot part of the process is to have a golden start point database to ensure everything is the same during the start of each test run.


texts: Mysql and money: precision problems with FLOAT(10,2)

texts:
I have a table like this:

mysql> describe transactions;
+-----------------------+---------------+------+-----+---------+----------------+
| Field                 | Type          | Null | Key | Default | Extra          |
+-----------------------+---------------+------+-----+---------+----------------+
| id                    | int(11)       | NO   | PRI | NULL    | auto_increment |
| cost                  | float(10,2)   | YES  |     | NULL    |                |
+-----------------------+---------------+------+-----+---------+----------------+

I (naively?) thought that would be a good way to store amounts.
I am 3 days from release, and I discovered this:

mysql> UPDATE transactions SET cost = 162295.99 WHERE id = 14891;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from transactions where id = 14891;
+-------+---------------+
| id    | cost          |

+-------+---------------+
| 14891 |     162295.98 |
+-------+---------------+

...?!? I entered .99, not .98!
So:

Why is this doing it?
How do I get out of this problem?
Would decimal work? I tried with:

mysql> alter table transactions modify cost decimal(12,2) NULL DEFAULT NULL;
Query OK, 1120 rows affected, 119 warnings (0.14 sec)
Records: 1120  Duplicates: 0  Warnings: 119

mysql>

However I am getting scary warnings. With "show warnings" I get:

mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1265 | Data truncated for column 'cost' at row 10  |
| Note  | 1265 | Data truncated for column 'cost' at row 369 |
| Note  | 1265 | Data truncated for column 'cost' at row 371 |
| Note  | 1265 | Data truncated for column 'cost' at row 378 |
| Note  | 1265 | Data truncated for column 'cost' at row 402 |
| Note  | 1265 | Data truncated for column 'cost' at row 403 |
+-------+------+---------------------------------------------

How do I see which one "row 10" is anyway?
KEY QUESTION: How do I get out of this hole safely?
(Rhetorical question bonus question) Why didn't this happen like 3 months ago, when fixing would have been easy?


texts:
I am trying to create a report that has the latest member id in the first column and the old member id in the second column based on the timestamp in audits table below. Member ids are stored in master table and the members table. audit_id from the audits table is a foreign key in members table.
I tried to write the query below after researching but I am getting syntax errors.
SELECT
  (SELECT mem.member_id
   FROM members mem
   JOIN audits aud ON mem.audit_id = aud.audit_id
   ORDER BY aud.update_time DESC FETCH FIRST 1 ROWS ONLY) AS latest_id,
       mem1.member_id AS old_id
FROM members mem1
JOIN master ma ON ma.member_id = mem1.member_id

audits
audit_id |update_time
656566   |2021-01-15 17:01:34
656565   |2020-05-13 17:01:34
545245   |2020-08-11 17:01:34

members
member_id  |audit_id
5655656    |656566
653363     |656565
545454     |545245

master
id | member_id
545634 | 5655656
545634 | 653363
545634 | 545454

Expected output
latest_id | old_id
5655656    |653363
5655656    |545454


texts: After Upgrade to 2016, SQL Server Agent can't connect to Instance

texts:
On a clustered Windows 2012 R2 server with SQL Server 2014 Enterprise.
Just upgraded an instance from 2014 SP1 CU4 to 2016 RTM and now getting this error when attempting to start the SQL Server Agent.
SQL SERVER AGENT LOG
2016-06-06 11:53:58 - ? [100] Microsoft SQLServerAgent version 13.0.1601.5 (X64 unicode retail build) : Process ID 10884
2016-06-06 11:53:58 - ? [495] The SQL Server Agent startup service account is DOMAIN\USERNAME.
2016-06-06 11:54:28 - ! [150] SQL Server does not accept the connection (error: 65535). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2016-06-06 11:54:28 - ! [000] Unable to connect to server 'SERVERNAME\INSTANCENAME'; SQLServerAgent cannot start
2016-06-06 11:54:33 - ! [298] SQLServer Error: 65535, SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQLSTATE 08001]
2016-06-06 11:54:33 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2016-06-06 11:54:33 - ! [298] SQLServer Error: 65535, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
2016-06-06 11:54:33 - ! [382] Logon to server 'SERVERNAME\INSTANCENAME' failed (DisableAgentXPs)
2016-06-06 11:54:33 - ? [098] SQLServerAgent terminated (normally)

Windows Application Log
SQLServerAgent could not be started (reason: Unable to connect to server 'A08SQL-EDI\EDI'; SQLServerAgent cannot start).

The agent starts and runs for about 30 seconds then dies with the above error. Anyone run into this issue? And do you know how to resolve it?


texts: Aggregate function to return any (first) encountered value

texts:
How do I tell PostgreSQL to return first encountered value instead of an aggregate column?

                          Table "public.cache"
  Column   |  Type   | Modifiers | Storage | Stats target | Description
-----------+---------+-----------+---------+--------------+-------------
 user_id   | integer |           | plain   |              |
 object_id | integer |           | plain   |              |
 data      | integer |           | plain   |              |
 data2     | boolean |           | plain   |              |
Indexes:
    "cache_object_id_user_id_key" UNIQUE CONSTRAINT, btree (object_id, user_id)
    "cache_user_id_object_id_key" UNIQUE CONSTRAINT, btree (user_id, object_id)
Has OIDs: no

Query grouping by object_id and data2 will make hash aggregate, what I want to avoid.

SELECT object_id, data2 FROM cache GROUP BY object_id, data2;

I found bool_or() but it will scan all values in bad case.

SELECT object_id, bool_or(data2) FROM cache GROUP BY object_id;

Moreover, there is no such function for any datatype. What I want to do is to get any of the values from data2 column so that engine don't have to iterate all rows.

What about data column which is integer?


texts: Want to create partitions of a MySQL table and change the charset of new partitions

texts:
I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in that table which has latin1 character encoding. I want to change it to utf8. Since I have a very large amount of data, I was thinking if there is some way I can create new partition with that column encoded in utf8 and leave the old partitions as they are. They will get deleted at some point of time in future and I will have whole data encoded properly.
Is there any way of doing this?


texts:
DD/mm/yyyy in varchar(max) needs to be converted to yyyymmdd format
Thought to get the experts opinion.

Below options to convert / cast didn't work:

CONVERT(VARCHAR(19),CONVERT(DATETIME, wfm.date,3),112)
convert(varchar(50), cast(wfm.date as datetime2), 112)
cast(cast(wfm.date as datetime2(15))as datetime)

Getting the error message for convert syntax as

  Conversion failed when converting date and/or time from character string.

For cast syntax getting the error message:

  Line 1: Specified scale 15 is invalid.


texts:
I have a frequent problem, that is one of my databases goes to suspect mode.

Many times I have recovered it with:

ALTER DATABASE Your_Db SET EMERGENCY
DBCC checkdb('Your_Db')
ALTER DATABASE Your_Db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('Your_Db', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE Your_Db SET MULTI_USER

It continues to become suspect after. Please provide me the permanent solution.


texts: How to do a recursive sum of children in mysql?

texts:
I've searched and tried for long hours, and can't seem to get what I need.
I have a table of accounts as a tree -parents and children-, only leaf accounts can have transactions, any parent will NOT have debits and credits itself.
My fiddle of data is here
What I need is to show sum of "Debits of children" and sum of "Credits of children" in their parent's row, like this picture:

How can I do it in MySql?
Thank you


texts: Falied to load resource files of Oracle APEX plugin

texts:
I've installed a plugin in APEX, but when I've tried to use it on a page, I've got some nasty errors and page not working properly.

The files are present in the plugin settings:

But, as you can see below, they are not being loaded for some reason. I get 404 errors instead.

I have ORDS running and APEX 19 installed on DB 19c XE.

What could be causing this? Wrong ORDS configuration maybe? How to fix it?


texts: With foreign keys automagically dropping out, can I set up an alert?

texts:
A strange thing happened to my company's Oracle database:

  Foreign key constraints keep dropping out by themselves.

If you could solve this problem, that would be great. But even if you can't, another method would be great, that is,

  Setting up an alert that will send an email when a foreign key constraint is dropped.

If you could solve any of the above 2 problems, it would be great to me.


texts: Performance hit of CONVERT from nvarchar to money (SQL Server)

texts:
I have a dollar amount value, that for historical reasons has always just been stored in an nvarchar field. Now that we are storing many more rows than we have in the past, and are using this field to total up amounts using an application that will automatically refresh frequently, I'm concerned about performance.

But I'm also concerned about the cost of making a change like this late in the development cycle.

Soon this database will be used by a customer that will generate millions of rows. All of these rows will not participate in this calculation. The rows are divided into groups of around 10,000, each parent of the group will be processed every 10 minutes or so, which is when these totals will be calculated.

Is it worth it now to change the column type to money, including the stored procedures, UDT's, data layer, etc?

It seems like it could have a performance impact, but unfortunately, I can't generate enough volume with the resources I have to do performance tests thta would be realistic. So I'm hoping someone has experience with a string to number conversion and can give me an idea if doing this conversion on 10,000 rows at a time will be a problem.


texts: Select where next row's value is greater

texts:
I have a road_insp table.

  create table road_insp
   (
    insp_id int,
    road_id int,
    insp_date date,
    condition number(38,2)
   ) ;

insert into road_insp (insp_id, road_id, insp_date, condition) values (1,1,to_date('01-JAN-01','DD-MON-YY'),10);
insert into road_insp (insp_id, road_id, insp_date, condition) values (2,1,to_date('01-JAN-04','DD-MON-YY'),09);
insert into road_insp (insp_id, road_id, insp_date, condition) values (3,1,to_date('01-JAN-07','DD-MON-YY'),08);
insert into road_insp (insp_id, road_id, insp_date, condition) values (4,1,to_date('01-JAN-10','DD-MON-YY'),06);

insert into road_insp (insp_id, road_id, insp_date, condition) values (5,2,to_date('01-JAN-02','DD-MON-YY'),10);
insert into road_insp (insp_id, road_id, insp_date, condition) values (6,2,to_date('01-JAN-05','DD-MON-YY'),08);

insert into road_insp (insp_id, road_id, insp_date, condition) values (7,4,to_date('01-JAN-03','DD-MON-YY'),10);
insert into road_insp (insp_id, road_id, insp_date, condition) values (8,4,to_date('01-JAN-06','DD-MON-YY'),12);
insert into road_insp (insp_id, road_id, insp_date, condition) values (9,4,to_date('01-JAN-09','DD-MON-YY'),08);

insert into road_insp (insp_id, road_id, insp_date, condition) values (10,5,to_date('01-JAN-01','DD-MON-YY'),10);
insert into road_insp (insp_id, road_id, insp_date, condition) values (11,5,to_date('01-JAN-03','DD-MON-YY'),09);
insert into road_insp (insp_id, road_id, insp_date, condition) values (12,5,to_date('01-JAN-06','DD-MON-YY'),08);
insert into road_insp (insp_id, road_id, insp_date, condition) values (13,5,to_date('01-JAN-09','DD-MON-YY'),07);
insert into road_insp (insp_id, road_id, insp_date, condition) values (14,5,to_date('01-JAN-12','DD-MON-YY'),06);
insert into road_insp (insp_id, road_id, insp_date, condition) values (15,5,to_date('01-JAN-15','DD-MON-YY'),05);
insert into road_insp (insp_id, road_id, insp_date, condition) values (16,5,to_date('01-JAN-18','DD-MON-YY'),20);

select
    *
from
    road_insp
order by
    road_id,
    insp_date

   INSP_ID    ROAD_ID   INSP_DATE  CONDITION
---------- ----------   --------- ----------
         1          1   01-JAN-01         10
         2          1   01-JAN-04          9
         3          1   01-JAN-07          8
         4          1   01-JAN-10          6

         5          2   01-JAN-02         10
         6          2   01-JAN-05          8

         7          4   01-JAN-03         10
         8          4   01-JAN-06         12 <-error
         9          4   01-JAN-09          8

        10          5   01-JAN-01         10
        11          5   01-JAN-03          9
        12          5   01-JAN-06          8
        13          5   01-JAN-09          7
        14          5   01-JAN-12          6
        15          5   01-JAN-15          5
        16          5   01-JAN-18         20 <-error

For each road, I want to know if the condition ever goes UP over time. The idea being, roads don't just magically improve in condition.

If the condition of the road ever goes up, then the culprit inspection should be flagged.

How can I analyze this table to find anomalies (conditions that go up)?

Example:

      INSP_ID   ROAD_ID     COND_ERROR_DATE  COND_ERROR
    ---------- ----------   ---------------- ----------
            8           4   01-JAN-06                12
           16           5   01-JAN-18                20


texts: What are the performance considerations between using a broad PK vs a separate synthetic key and UQ?

texts:
I have several tables where records can be uniquely identified with several broad business fields.  In the past, I've used these fields as a PK, with these benefits in mind:

Simplicity; there are no extraneous fields and just one index
Clustering allows for fast merge joins and range-based filters

However, I've heard a case made for creating a synthetic IDENTITY INT PK, and instead enforcing the business key with a separate UNIQUE constraint.  The advantage is that the narrow PK makes for much smaller secondary indices.

If a table has no indices other than the PK, I don't see any reason to favor the second approach, though in a large table it's probably best to assume that indices may be necessary in the future, and therefore favor the narrow synthetic PK.  Am I missing any considerations?

Incidentally, I'm not arguing against using synthetic keys in data warehouses, I'm just interested in when to use a single broad PK and when to use a narrow PK plus a broad UK.


texts: postgres auto_explain : setting log_min_duration doesn't start

texts:
postgres 8.4.20
With these settings, the daemon starts :

postgresql.conf
shared_preload_libraries = 'auto_explain'

With these settings, the daemon doesn't start :

postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 1000

It is a server with a legacy application, so we cannot upgrade PG.
I cannot find documentation of this behaviour / bug...


texts: Altering View in MS SQL Server by Adding Col based on Join

texts:
I have created a view that queries a table called Sheet1$. It has a carID field, and a carName field.

So I created my view by doing the following:

CREATE VIEW corvettes
AS
SELECT *
FROM Sheet1$
WHERE carName = 'corvette'

I have another table called Sheet2. It has a carID field, and a carYear field. I want to alter my original view by adding a COL to it called YEAR and then populate YEAR with the values of Sheet2$.carYear via join of the two carID fields.

I can get the YEAR field appended with the correct data, but if there is no match of the ID's, then the whole row goes away. I'd rather have the YEAR say NULL instead of going away if the ID's don't match.

This is what I have. Where am I going wrong?

ALTER VIEW corvettes
AS
SELECT a.carID, a.carName, b.carYear
FROM Sheet1$ a, Sheet2$ b
WHERE a.carID = b.carID
AND carName = 'corvette'


texts: MySQL proxies_priv ignored in --skip-name-resolve mode

texts:
I'm running MySQL (MariaDB) inside docker container and with every start, it shows the following warning:

  [Warning] 'proxies_priv' entry '@% root@mariadb-3702996102-qbr18' ignored in --skip-name-resolve mode.

So, it writes the current container hostname in proxies_priv and since I have option skip-name-resolve in my.cnf it gives the warning.

Is there a way to hide this warning? Perhaps there's an option to not write the hostname variant to proxies_priv?


texts: Help with recursing self-referencing table

texts:
I have two tables:

Datasource ( A self referencing table)
------------------------------
Id, Type, ParentId, Name
1   A     NULL      Foo
2   B     1         Bar
3   C     1         FooBar

Variable
------------------------------------
Id DataSourceId Name
1  3            Foo
2  2            Bar
3  1            Foobar

My desired output is something like:

Output
---------------------------------------------
VariableId  DataSourceId
1           1
2           1
3           1

Such that for each variable, the datasource is the topmost parent in the hierarchy.

I'm struggling with the required recursion of the self referencing table.


texts: Basics of creating a table and refreshing it via a mysql event

texts:
My apologies in advance for my display of ignorance.  Is there a way for the event_body of an event to take the following:

DROP TABLE IF EXISTS compdist1.comp_CL1;

CREATE TABLE compdist1.comp_CL1 AS SELECT * FROM compdist1.prices_CL_analysis;

I am discovering that only one line is being picked up. (the top one).  Is there a way to make it do both steps? Basically, drop the table and repopulate it from the view prices_CL_analysis.


texts:
I have a script for scripting out user permissions I use on SQL 2014 that works fine but when I try on SQL 2008, I am getting the following error:

Msg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column 'UserName', table '@DB_USers'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(0 row(s) affected)

DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT

dbname,username ,logintype ,create_date ,modify_date ,

STUFF(

(

SELECT ',' + CONVERT(VARCHAR(500),associatedrole)

FROM @DB_USers user2

WHERE

user1.DBName=user2.DBName AND user1.UserName=user2.UserName

FOR XML PATH('')

)

,1,1,'') AS Permissions_user

FROM @DB_USers user1

GROUP BY

dbname,username ,logintype ,create_date ,modify_date

ORDER BY DBName,username


texts: MS SQL Server 2000 ODBC driver for Linux

texts:
Microsoft has official ODBC drivers for MS SQL Server, for Linux. Great! Nice installation instructions for all the major distros, too.

Unfortunately, their ODBC drivers, going back to the oldest version I can find only support SQL Server 2005 and newer:

  Native Data Access APIs – ODBC and OLE DB – for SQL Server 2005 and later.

I need to connect to a very old SQL Server (2000):

SELECT @@VERSION
→
Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)
       May  3 2005 23:18:38
       Copyright (c) 1988-2003 Microsoft Corporation
       Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

I've confirmed that the v13 driver does not work:

$ sqlcmd -U XXXX -S 10.1.2.3
Password:
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : ODBC Driver 13 for SQL Server does not support connections to SQL Server 2000 or earlier versions..
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection.

—
Is there an official MS SQL ODBC driver that will run on a modern 64-bit Linux and connect to SQL Server 2000?

Or do I need to go to third party drivers like the open-source FreeTDS?


texts: Multiply several discrete values in multiple columns and then sum

texts:
I need to multiply values from two tables and sum results. The database is either Postgres or MSSQL (long story).

Table Bought_Products

ID | Name | Customer_ID | Product_ID | Foo | Bar | Blarg
1  | test | 123         | 321        | NULL| 1   | NULL
2  | tast | 123         | 231        | 5   | NULL| 20
3  | tost | 456         | 321        | NULL| 3   | NULL

Table Price

ID | Item | Product_ID | Cost
1  | Foo  | 321        | 10
2  | Bar  | 321        | 5
3  | BASE | 321        | 100
4  | BASE | 231        | 50
5  | Blarg| 231        | 0.5
6  | Foo  | 231        | 1

I need a result like this:

Customer | Products | Revenue
123      | 321, 231 | 170

For Customer 123 there are 2 products: 321 and 231, with a base price of 100 and 50 respectively; plus 1 x 'Bar' item at 5; plus 5 x 'Foo' items at 1; and 20 x 'Blarg' items at 0.5.

This is what I have for a start:

select sum(p.cost) from price p
join Bought_Products bp on bp.product_id = p.product_ID
where p.item = 'BASE'

I need to add the relevant 'Foo', 'Bar' or 'Blarg' values from Bought_Products multiplied with the respective Price.Cost (matching the column header to the item).

I've not used basic mathematical functions in SQL, yet. I'm not sure how to join between the 2 tables to then multiply the values. With subqueries and subsequent joins?


texts: DB2 for i setting connection specific collation

texts:
I read an answer to a question that said you can set connection-specific collation for a DB2 for i connection. However, my Google skills are failing me and I can't find anything about it.
Specifically, I was to set the collation to be case insensitive.
Is this a pipe dream or does it exist?
Please and thank you!


texts: Percona xtrabackup transaction log corrupted

texts:
I am attempting to prepare and restore a full backup taken through xtrabackup via a scheduling script called surrogate.

I have tarred and gzipped the backup, transferred to a local machine running the same setup (aside from a slight version difference in xtrabackup - 2.2.12 on source, 2.3.9 local).

Upon extracting I am running the following command to prepare the backup for restore:

innobackupex --use-memory=1G --apply-log /home/matthew/full_2017-08-13_0700/

This is responding:

  innobackupex version 2.3.9 based on MySQL server 5.6.24 Linux (x86_64) (revision id: fde0e3e)
  xtrabackup: cd to /home/matthew/full_2017-08-13_0700/
  xtrabackup: This target seems to be not prepared yet.
  xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(661464509389)
  xtrabackup: using the following InnoDB configuration for recovery:
  xtrabackup:   innodb_data_home_dir = ./
  xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  xtrabackup:   innodb_log_group_home_dir = ./
  xtrabackup:   innodb_log_files_in_group = 1
  xtrabackup:   innodb_log_file_size = 2097152
  xtrabackup: using the following InnoDB configuration for recovery:
  xtrabackup:   innodb_data_home_dir = ./
  xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  xtrabackup:   innodb_log_group_home_dir = ./
  xtrabackup:   innodb_log_files_in_group = 1
  xtrabackup:   innodb_log_file_size = 2097152
  xtrabackup: Starting InnoDB instance for recovery.
  xtrabackup: Using 1073741824 bytes for buffer pool (set by --use-memory parameter)
  InnoDB: Using atomics to ref count buffer pool pages
  InnoDB: The InnoDB memory heap is disabled
  InnoDB: Mutexes and rw_locks use GCC atomic builtins
  InnoDB: Memory barrier is not used
  InnoDB: Compressed tables use zlib 1.2.8
  InnoDB: Using CPU crc32 instructions
  InnoDB: Initializing buffer pool, size = 1.0G
  InnoDB: Completed initialization of buffer pool
  InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
  InnoDB: Setting file ./ibdata1 size to 12 MB
  InnoDB: Database physically writes the file full: wait...
  InnoDB: Setting log file ./ib_logfile101 size to 2 MB
  InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
  InnoDB: New log files created, LSN=45781
  InnoDB: Doublewrite buffer not found: creating new
  InnoDB: Doublewrite buffer created
  InnoDB: 1 rollback segment(s) are active.
  InnoDB: Waiting for purge to start
  InnoDB: 5.6.24 started; log sequence number 0

  xtrabackup: error: The transaction log file is corrupted.
  xtrabackup: error: The log was not applied to the intended LSN!
  xtrabackup: Log applied to lsn 0
  xtrabackup: The intended lsn is 661464919534

Any idea what this is and how I overcome it?


texts: Count Over returns Row_Number based on Order By

texts:
Quick question based on some interesting syntax I've found out.
I have a table with these columns:

ID INT,
DimBuyDetails_Id INT,
WeekOfBuy INT,
Spots INT,
Retired BIT

If I run this query I will get the ROW_NUMBER() instead of the COUNT()

SELECT *
    , COUNT(ID) OVER (
        PARTITION BY DimBuyDetails_Id, WeekOfBuy
        ORDER BY ID --<
) AS ct
FROM Core.FactBuysPerWeek

However, if I change the ORDER BY clause I'll get the COUNT()

SELECT *
    , COUNT(ID) OVER (
        PARTITION BY DimBuyDetails_Id, WeekOfBuy
        ORDER BY DimBuyDetails_Id, WeekOfBuy --<Changed Order by here
) AS ct
FROM Core.FactBuysPerWeek

Anyone know what causes this?


texts: I have 10 databases on a server. Each database is part of a different AG. Each database has its own listener. Can I hide all databases and show only the one database belonging to a listener? I also do not want to create a separate user for each listener as it will be a lot of users to be created and maintained across many database servers.


texts: MySQL Slave has unmanagbly higher IO load than master

texts:
A MySQL slave having a higher disk load than the master or not being able to keep up is not unusual, nor are questions about it on Stack Exchange, but I did want to run my issue by here, because in my case, the difference is so great.

(Edit3: by now, I have figured out that a large part of this is that I DELETE FROM x LIMIT y a lot, in binlog MIXED mode. See below.)

My MySQL slave basically has 100% disk load all the time, and cannot get better than being a day behind. Here are some Munin graphs:

Master disk utilization:

Slave disk utilization:

The first peak in the graph (the 50% peak) is when I loaded the 200GB master dump into the slave. Then on the 20th, it started processing bin logs.

I have tried both STATEMENT and MIXED format; there wasn't much difference. In the end, I can't use STATEMENT, because there are quite a lot of unsafe statements.

The master gets thousands of new rows per minute, and also deletes expired rows daily with DELETE ... limit x repeated a lot (in a script). However, as you can see, that doesn't face the master much. From the moment replication was started (the 20th in the graph), the master graph shows less load (because it no longer does daily mysqldump), yet the slave is perpetually busy.

The Munin graphs for queries per second show approximately the same amount. Master sql queries:

Slave sql queries:

The master:

virtual machine, 2.4 GHz, 16 GB RAM, HDD storage. RAID10, but since it's an online service, I don't know the details.
MySQL 5.5
query_cache_limit = 4M
query_cache_size = 512M
innodb_buffer_pool_size = 4G
innodb_log_file_size = 1024M
innodb_flush_method=O_DIRECT

The slave:

Physical machine, 4 GB RAM, HDD.
Quad core Intel(R) Xeon(R) CPU E5405  @ 2.00GHz
3 drive RAID1 (WDC WD2003FYYS; 2 TB)
MySQL 5.5
query_cache_limit = 4M
query_cache_size = 512M
innodb_buffer_pool_size = 1G
innodb_log_file_size = 1024M
innodb_flush_method=O_DIRECT

This can't be normal, right? How can I properly debug this? I can't really do explain analyze on the binlog...

edit: When I do (while replication is running and while it's making a sqldump):

dd if=/dev/zero of=big3 bs=4096 bs=1M count=1000 oflag=direct

I get:

1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 51.0737 s, 20.5 MB/s

iostat -x 1 normally show about 5 MB/s of writing, even less reading. Dd'ing increases this to about 25. Apparently, the disks have throughput to spare. I guess MySQL must be causing seeks.

Edit2: I thing that might be useful: the thousands of new rows per minute are added using multi row insert statements. We used to use multiple single row statements, and using multi row statements improved performance drastically. How is that deal with in MIXED binlog format? When I still used STATEMENT, they weren't mentioned as unsafe, so I guess they must still be processed as multi row inserts?

Edit3: What I mentioned above, is that there is also a daily script that deletes old data on the master, a couple of million rows per day. I disabled that for a few days, and the slave was able to catch up. Because we're dealing with so many records, I use a LIMIT clause on the DELETE, turning it into an usafe statement. In the MIXED log format, this statement seems to be transferred as STATEMENT, because otherwise it would be unsafe.

Can I force this statement being logged as STATEMENT? I already turned it into an ordered delete, and I wouldn't even really care about the unsafety of it (but there are other queries for which I do care, so I can't use STATEMENT as global binlog format).


texts: Why does "SELECT *" take longer than "SELECT "?

texts:
I'm having a problem with a query running on a little PHP program.
When I use * to select all the columns, it takes way more to return the result than when I use just one column.

The query that is running is this:

mysql> SELECT cupom_fiscal_id FROM cupom_fiscal WHERE send_date IS NULL AND travado IS
FALSE AND status = 0 AND ong_id = 1618 AND data_emissao BETWEEN '2014-04-01' AND '2014-
05-05' LIMIT 1;

In this case it returned:

1 row in set (0.00 sec)

But when I replace the column with '*':

mysql> SELECT * FROM cupom_fiscal WHERE send_date IS NULL AND travado IS FALSE AND
status = 0 AND ong_id = 1618 AND data_emissao BETWEEN '2014-04-01' AND '2014-05-05'
LIMIT 1;

1 row in set (1.37 sec)

The funny thing is the result of the query below:

mysql> SELECT * FROM cupom_fiscal WHERE cupom_fiscal_id = (SELECT cupom_fiscal_id FROM
cupom_fiscal WHERE send_date IS NULL AND travado IS FALSE AND status = 0 AND ong_id =
1618 AND data_emissao BETWEEN '2014-04-01' AND '2014-05-05' LIMIT 1);

1 row in set (0.00 sec)

What I'm doing wrong?

PS: Sorry for the bad english.

EDIT 1: Posting the 'EXPLAIN' and the 'PROFILING' requested by: @ValerieParham-Thompson

mysql> EXPLAIN SELECT * FROM cupom_fiscal WHERE send_date IS NULL AND travado IS FALSE
AND status = 0 AND ong_id = 1618 AND data_emissao BETWEEN '2014-04-01' AND '2014-05-05'
LIMIT 1;
+----+-------------+--------------+-------------+--------------------------------------------------------------------------------+--------------------+---------+------+------+--------------------------------------------------+
| id | select_type | table        | type        | possible_keys                                                                  | key                | key_len | ref  | rows | Extra                                            |
+----+-------------+--------------+-------------+--------------------------------------------------------------------------------+--------------------+---------+------+------+--------------------------------------------------+
|  1 | SIMPLE      | cupom_fiscal | index_merge | fkey_id_ong,status,ong_id,data_emissao,send_date,ong_id_2,ong_id_3,send_date_2 | status,fkey_id_ong | 4,4     | NULL | 3712 | Using intersect(status,fkey_id_ong); Using where |
+----+-------------+--------------+-------------+--------------------------------------------------------------------------------+--------------------+---------+------+------+--------------------------------------------------+
1 row in set (0.00 sec)

Profiling result:

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000121 |
| checking permissions | 0.000015 |
| Opening tables       | 0.000030 |
| init                 | 0.000054 |
| System lock          | 0.000018 |
| optimizing           | 0.000027 |
| statistics           | 0.000282 |
| preparing            | 0.000041 |
| executing            | 0.000014 |
| Sending data         | 0.627346 |
| end                  | 0.000027 |
| query end            | 0.000018 |
| closing tables       | 0.000021 |
| freeing items        | 0.000081 |
| cleaning up          | 0.000012 |
+----------------------+----------+
15 rows in set, 1 warning (0.04 sec)

EDIT 2: I think I got it!
I just created an index with the the same fields and in the same order that they appear in the WHERE.

mysql> ALTER TABLE cupom_fiscal ADD INDEX `process` (`send_date`, `travado`, `status`, `ong_id`, `data_emissao`);

I really don't know if that makes any sense, but it solved my problem. The same query that took more than 1 second to return the results now takes < 7ms.


texts: High CPU usage from MySQL with no queries at all running

texts:
I noticed that MySQL is using way more CPU than usual on my workstation (usual is 1% or less, because this is my workstation, not a server).

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14004 mysql     20   0  536m  44m 7136 S  141  1.2  65:24.72 mysqld

I looked in my slow log and there are no queries at all. I ran tail -f on the general log to see what was running and there was nothing at all there either. The MySQL processlist is empty as well.

+-----+------+-----------+----+---------+------+-------+------------------+
| Id  | User | Host      | db | Command | Time | State | Info             |
+-----+------+-----------+----+---------+------+-------+------------------+
| 165 | root | localhost |    | Query   | 0    |       | show processlist |
+-----+------+-----------+----+---------+------+-------+------------------+

Every other post I've seen is related to high CPU usage due to a specific query, but I've got no queries running at all. Does anyone know how to determine why MySQL is using all that CPU?

In case it matters, here's more info about my system:

Linux kubuntu 3.2.0-26-generic

Server version          5.5.24-0ubuntu0.12.04.1-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 57 min 8 sec

Threads: 1  Questions: 406  Slow queries: 0  Opens: 567  Flush tables: 1
Open tables: 140  Queries per second avg: 0.118


texts: MySQL Full Text search increase relevance for exact matches?

texts:
I have a MySQL database of ~10,000 organisation names that I want to be able to search. I would like to use a full text search because this would enable me to find "institute of doobry" by searching "doobry institute" etc.

The problem I have is that I have lots of entries like "institute of doobry canteen" and "institute of doobry alumni association".

MATCH (names) AGAINST ("doobry institute")

will return all of these records and will not score institute of doobry higher than the canteen.

I'd sort of like to say: score it higher if the word count is similar.

Is there any way to achieve this?


texts: Database not naturally shrinking log file

texts:
I have a database that is set to Simple recovery mode. There is 99% free space in the log file and I can manually truncate the log file right down to 500 MB.

The next day, after a daily import process runs, the log file will be back up to ~70GB.

If I check log_resuse_wait in sys.databases it returns 0 which would indicate nothing is blocking a shrink operation.

Why isn't the log file naturally shrinking? Also, how can I find what SQL queries are causing the log file to grow to such a size?


texts: Why can't we perform joins in a distributed database like Bigtable?

texts:
From this answer to "What database does Google use?":

  Bigtable is not a relational database. It does not support joins nor does it support rich SQL-like queries.

Is this obvious? I do not understand why.


texts:
Let me preface this what what I'd like to accomplish and see if this is inline with the expectation of using Azure SQL DB.

Currently I use a couple Virtual Machines in Azure that use a linked server to execute some stored procedures to hit a database outside of Azure (the external database is in AWS)

I'd like to move to use the Azure SQL Server to query an external database but I'm not seeing documentation around setting up an external data source that's just a SQL DB - only elastic within your own Azure network. Is this outside the scope of the product?


texts: Oracle save mandarin characters with we8iso8859p1

texts:
Im trying to save Mandarin characters into Oracle instance database 11g.
Field datatype - NVARCHAR2 ;
NLS_LANG - AMERICAN_AMERICA.WE8ISO8859P1

If I change NLS_LANG, it will affect other data already in this huge database but I need it to support manadarin as well.
Im also searching for some way if available on JAVA side while inserting these characters into DB.
Please help.


texts: Naming question for functions: implicit or explicit naming of grouping in function name

texts:
Requesting help with naming TSQL functions.

Existing function names (see below for table and relationship descriptions)

I currently have functions named as follows:

CustomerYearsGetByYearRangeOffset: For each CustomerID, return all of their CustomerYears records, over a specified begin/end range
CustomerYearsGetLatestByYearRangeOffset: For each CustomerID, return their latest CustomerYears record, over a specified begin/end range
ObjectRevisionIDsGetByYearRangeOffset: For each ObjectID, return all of their ObjectRevisionIDs records, over a specified begin/end year range
ObjectRevisionIDsGetLatestByYearRangeOffset: For each ObjectID, return their latest ObjectRevisionIDs record, over a specified begin/end year range

Note: All accept a caller-specified begin/end year range offset (relative to the current year). For example, begin/end year offsets -1, 0 would indicate the year one-year prior and the current year.

Two new functions

The two new functions will implement more sophisticated logic, as follows:

For each CustomerID, return their latest ObjectRevisionIDs records tied to their latest CustomerYears record, over a specified begin/end year range
For each CustomerID, return all their ObjectRevisionIDs records tied to their latest CustomerYears record, over a specified begin/end year range

What to name the new functions?

Some drafts:

ObjectRevisionIDsInLatestYearGetLatestByYearRangeOffset
ObjectRevisionIDsInLatestYearGetByYearRangeOffset

Other ideas?

Am I thinking about this wrong? For example, abandon the explicit naming approach above and name according to the higher level need it meets?

I see myself calling these new functions frequently, so I prefer to get something that seems "right" each time I use it. ;o)

Thanks for any assistance. Usually when I get this far in writing a question the answer presents itself, but not this time.

Background: tables and relationships

To keep things simple for this question, the database contains four tables (in reality many more tables and fields):

Customers (CustomerID)
CustomerYears (CustomerID, Year)
Objects (ObjectID, CustomerID)
ObjectRevisions (ObjectRevisionID, ObjectID, CustomerID, RevisionNumber, Year)

In a nutshell: each year the Customer participates, they get a new record in CustomerYears. In each year, they will have one or more Objects, each of which can have one or more revisions.

For example:

Customers (CustomerID)
1
2

CustomerYears (CustomerID, Year)
1|2011
1|2012
2|2012

Objects (ObjectID, CustomerID)
A|1
B|1
C|2

ObjectRevisions (ObjectRevisionID, ObjectID, CustomerID, RevisionNumber, Year)
1|A|1|1|2011
2|A|1|2|2011
3|B|1|1|2011
4|A|1|1|2012
5|B|1|1|2012
6|B|1|2|2012
7|C|2|1|2012
8|C|2|2|2012


texts:
If I have SQL Log Shipping scheduled as follows:

Primary Server (VM): Backup Job Schedule - Every 5 Minutes (To LogCopyTo)

Secondary Server (VM): Copy Job - Every 15 Minutes (To LogCopyFrom)

Secondary: Restore Job - Every 25 Minutes No Recovery Mode

If I take a snapshot of the Secondary VM remove it from the network (for 24 hours) and run "Restore With Recovery" on each database and work with the data for testing purposes and restore the snapshot and put it back on the network, will log shipping catch up and continue without issues?


texts: Mysql - lock wait timeout while updating 70 million records

texts:
i have deployed my website and mysql server on a single server.

Disk space - 20 GB;

RAM - 16GB;

Currently there are 70 million records in the database and i am writing update query to change the "status" attributes of all this 70 million records. after executing the update query i am getting error as "mysql lock wait timeout". below is my sql  query

update authentication_codes set status=true;

I think i am getting this error because mysql is not able to execute this query within lock timeout specified in mysql cofig file. increase lock timeout value will work but in the future database will go on increasing so i think this is not good solution.

Please tell me what mysql settings i have to change  to increase the mysql performance. Do i have to increase the ram for processing 70 million records?? Do i have to increase hard disk size??


texts:
I need to work with a remote postgresql server. My application needs to perform the following actions:

to be able to create/drop database (I only need to work with 1 specific database)
to be able to lock db for new connections / to be able to drop all connections.
to work with the DB directly (SQL queries)

Assuming that I can request an administrator user for the DB I am going to work with, and that I'm not going to get a user with administrative permissions for the complete DB server, what is the possible solution for this matter? Is it possible to have one user that can do all that I need?

Thanks.


texts: Using SSD as Read Cache in SQL Server having SAN as storage

texts:
We are deploying SQL Server 2012 Ent. Ed. on WSFC cluster using a DELL SAN as storage.
SAN comes with 24TB of SAS disks (expandable) & 1.6 TB of SSD. We want to use this SSD as DB Read Cache.

While creating SSD read cache, the I/O characteristic type value has to be specified which, includes three options:
1. File System
2. Database
3. Web Server

Wanted to know which I/O option is to be selected for our use-case and what are the implications to keep in mind for it?

Kindly help with your valuable feedback.


texts: get all the count of sub category from the product table in single query with mysql optimization

texts:
Table structure:

    CREATE TABLE some_table (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) NOT NULL,
  `Product_model` varchar(255) NOT NULL,
  `product_discount` double(10,2) NOT NULL,
  `product_price` double(10,2) NOT NULL,
  `product_price_with_discount` double(10,2) NOT NULL,
  `product_quantity` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `subcat_id` int(11) NOT NULL,
  `product_color` varchar(255) NOT NULL,
  `product_short_description` text NOT NULL,
  `product_large_description` text NOT NULL,
  `meta_keyword` varchar(255) NOT NULL,
  `meta_description` text NOT NULL,
  `status` tinyint(1) NOT NULL COMMENT '0=>inactive,1=>active,2=>deleted',
  `publish` tinyint(11) NOT NULL COMMENT '1=published,0=unpbulished',
  `product_feature_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1=>set_feature,0=>unset_feature',
  `product_pre_order` tinyint(4) NOT NULL COMMENT '0=>no,1=?yes',
  `pre_order_description` text NOT NULL,
  `availability` tinyint(4) NOT NULL COMMENT '0=>out of stock,1=>in stock',
  `childcat_id` varchar(100) DEFAULT NULL,
  `title` varchar(256) NOT NULL,
  `sold_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`product_id`),
  KEY `Product_model_3` (`Product_model`),
  KEY `product_name_7` (`product_name`),
  FULLTEXT KEY `product_name` (`product_name`),
  FULLTEXT KEY `product_name_2` (`product_name`,`Product_model`,`product_short_description`,`product_large_description`),
  FULLTEXT KEY `product_name_3` (`product_name`),
  FULLTEXT KEY `product_name_4` (`product_name`,`Product_model`,`product_short_description`,`product_large_description`),
  FULLTEXT KEY `Product_model_2` (`Product_model`),
  FULLTEXT KEY `Product_model_4` (`Product_model`),
  FULLTEXT KEY `product_name_5` (`product_name`),
  FULLTEXT KEY `Product_model_5` (`Product_model`),
  FULLTEXT KEY `Product_model_6` (`Product_model`),
  FULLTEXT KEY `Product_model_7` (`Product_model`),
  FULLTEXT KEY `product_name_6` (`product_name`,`product_short_description`,`product_large_description`),
  FULLTEXT KEY `Product_model` (`Product_model`),
  FULLTEXT KEY `product_short_description` (`product_short_description`,`product_large_description`)
) ENGINE=InnoDB

Query executing :

SELECT * FROM some_table where status = 1 and publish = 1 and subcat_id in(1,2,3,4,33,47)

Desired result: To get all the count of product per sub category.

Trying to avoid:

Joining the table itself as the number of rows are too much to join as execution time is too much.

Edit:

SELECT count(product_id) FROM some_table where status = 1 and publish = 1 and subcat_id in(1)

Output
114

This query works properly for the single subcat_id but how to implement for multiple subcat_id


texts:
have two machine PC#1 with db mysql and appache and PC#2 with a c# form try to connect to this db

here is my connection string used in PC#2: SERVER=My_public_ip;PORT=3306;DATABASE = my_test; UID = root; PASSWORD = xxxx;"

when i try to connect locally from PC#1 it work good:

SERVER=localhost;DATABASE = my_test; UID = root; PASSWORD = xxxx;"

i did make my root user remote enabled like that:

`GRANT ALL ON *.* to '%'@'%' WITH GRANT OPTION;`

and all i get is an Event viewer: unable to connect to any of the specified MySQL host

now i think it has nothing to do with firewall aftr turn them off on both pc and i still get the same error message, but i can't figure it out


texts:
Imagine your requirement is that you have 3 huge tables (structured data) with say 30 billion rows in each (total size of 4TB) and your many concurrent users (which are parallel os threads on remote LAN machines) will need to read a portion of the data through their SELELCT WHERE GROUPBY queries and highly concurrent, say 10,000 concurrent reads at the same time and also users need to insert (no update) data into these tables highly concurrent too like 2000 concurrent writers (all over the data center LAN network). The users would want to read and insert as fast as possible form this storage where each read and write will happen is ms to 1 second range.

What technologies do you recommend to satisfy such requirement? Is there any data storage or key value store that could do this? Cloud is NOT an option.

Some Clarifications:

The users do NOT have to see the data right away and eventual consistency is acceptable.
The data is accesses through whatever driver the storage can provide and users are again just threads running on remote machines of the data center. The queries are mostly like SELECT WHERE GROUPBY.

The data is in tabular format and each row is about 60 bytes.

No cloud option where I can not use DynamoDB or similar solutions. I have to be able to host it internally in the data center.

All the data of the tables can be read all the time and usage pattern is unpredictable. There is no join or super long query. No DR required but a reasonable HA is required but it does not have to be fancy. Every reader is getting a batches of rows based on its where clause and rows are not really related. We probably can have fixed length for each row but I am hoping the storage layer will worry about it.

Also, my biggest concern are all those concurrent writes that are happening with concurrent reads.

Your insights into this is highly appreciated.

And more thing, I have three of those tables with each 30 billion rows holding different object types


texts: SQL Server 2012 - Common Table Expression (CTE) returning more rows than it should

texts:
CTE SQLFiddle

I have one lifetable with 121 rows and 3 columns (age, male and female). I have to fetch all the 121 rows considering the gender. If the gender is "Male" I fetch the column "Male" from lifetable as qx and the column "Female" as qy and vice-versa.

However, I need to calculate a fourth column, vx, whose value starts with 1 and the subsequent values are calculated as follows, using the previous value of vx itself:

vx = (vx / (1 - @interestRate)^age)

The age always goes from 0 to 120, incremented by one each step.

I am not well versed with CTEs, therefore, I'm taking baby steps to accomplish my tasks with this new method to me. and I need help.

When I try to calculate vx, the query returns 7381 instead of 121.

Here is my CTE:

DECLARE @gender VARCHAR(10) = 'MALE'
DECLARE @interestRate NUMERIC(7, 5) = 0.0354

;WITH cte AS (
    SELECT
        lt.Age,
        CASE
            WHEN @gender = 'MALE' THEN
                lt.Male
            ELSE
                lt.Female
        END AS qx,
        CASE
            WHEN @gender = 'MALE' THEN
                lt.Female
            ELSE
                lt.Male
        END AS qy,
        CAST(1.0 AS NUMERIC(7, 5)) AS vx
    FROM LifeTable AS lt

    UNION ALL

    SELECT
        cte.Age + 1,
        cte.qx,
        cte.qy,
        CAST((vx / (1 + @interestRate)) AS NUMERIC(7, 5)) AS vx
    FROM cte

    WHERE cte.Age < 120

) SELECT COUNT(*) AS NumberOfRows FROM cte OPTION (MAXRECURSION 0);

I am a little bit tired for I spent my afternoon trying to solve this, it's already night, so my question might be taken as low effort, but I really need your help to see where I am making mistakes.


texts: What do you call something that's not the primary key?

texts:
I've been working with a group who have built a system where they've coined a few new terms in the process, and I wanted to know if there's something more standardized they should be calling it.

Basically, they have tables which hold versioned info, so the unique key for the table is the identifier for the document being tracked, plus the record number (although, the record number is auto-numbered, so it's unique in itself).  They refer to the fields that make up the document's identifier (it changes for each table), as the 'prime key' ... but the fields both individually and collectively (eg, if it has fields A, B and C to get a unique identifier, they might interchangable refer to A as being 'a prime key' and A/B/C as being 'the prime key'

If they had a normalized database, this would be an obvious candidate for a foreign key -- but it's not normalized, so it's not a reference to a different table.

Is there some name for this concept -- an identifier to select some concept that's not what the table is based on, but which isn't a foreign key?

update: it's not an alternate key, as it doesn't identify a unique record within the table; it identifies a group of records.  Take the folowing table:

id   document_id   version_no
--   -----------   ----------
 1             1            1
 2             2            1
 3             1            2
 4             2            2
 5             2            3

So we're tracking metadata for 5 objects, which are two different files, one of which has been updated once (2 versions/editions of it), and one which has been updated twice.

We have the following:

candidate keys : (id) or (document_id, version_no)
natural key : (document_id, version_no)
surrogate key : (id)
primary key : (id, document_id)  (I didn't select it)

... if document_id were a relationship to another table, it'd be a foreign key, but 'foreign key' typically also infers a constraint (ie, the value has to exist in some other table before it can be used here) ... but in this case, it's not.  So, is there some other term to describe this as something that selects a related grouping of objects rather than a single record out of the table?

The first definition of surrogate on the wikipedia page for surrogate key fits, but that's not the common usage of the term, where most people consider a surrogate key to fit the second definition:

Surrogate (1) : This definition is based on that given by Hall, Owlett and Todd (1976). Here a surrogate represents an entity in the outside world. The surrogate is internally generated by the system but is nevertheless visible to the user or application.
Surrogate (2) :
This definition is based on that given by Wieringa and De Jonge (1991). Here a surrogate represents an object in the database itself. The surrogate is internally generated by the system and is invisible to the user or application.

update 2 : Basically, if you had a table that handled versioning of records, if you moved the records to a new table and stored editing history, the fields that link back to the original table would be considered a foreign key ... but if you stored all of it in one table, without relating to a secondary table, is there a name for the group of fields that identify the multiple versions of the record?

The reason I ask is that the group I'm working with has decided to call it a 'prime key', and when I came on board, I reviewed all of their documentation, and told them they had a typo, and it was the 'primary key', but they explained that no, it was the phrase they had coined to describe this concept.

I'm trying to identify if there's a accepted name in the database community for this concept.


texts: What impact on the index storage size does overprovisioning of varchar types have?

texts:
Does over-provisioning varchar columns (as in using varchar(100) to store values from 1 to 10 characters long vs using varchar(10)) have any impact on the index storage size and if yes than why?

I recall reading a Q/A here about this but cannot find it using any of the terms which come to mind.

I do not care about memory grants or plans - only about the physical storage of the indices on such data.


texts: How can we optimise this OR and ORDER BY query?

texts:
We have a table with the following structure:

+----+----------+-------------+---------------------+-----------+---------+----------+
| id | Batch_id | Merchant_id |     ExpiryDate      | Allocated | MaxUses | IsBarred |
+----+----------+-------------+---------------------+-----------+---------+----------+
|  1 |        1 |        5793 | 2019-11-03 01:23:40 |         1 |       1 |        0 |
|  2 |        1 |        1238 | 2019-11-07 01:23:40 |         0 |       1 |        0 |
|  3 |        2 |        9427 | 2019-11-09 04:49:40 |         1 |       1 |        0 |
+----+----------+-------------+---------------------+-----------+---------+----------+

In DDL terms this is:

CREATE TABLE `merchant_coupon` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ExpiryDate` datetime DEFAULT NULL,
  `MaxUses` int(11) NOT NULL,
  `Batch_id` int(11) DEFAULT NULL,
  `Merchant_id` int(11) DEFAULT NULL,
  `Allocated` int(11) NOT NULL,
  `IsBarred` tinyint(1) NOT NULL
  PRIMARY KEY (`id`),
  KEY `IDX_8C9078EBAF5CDCE0` (`Batch_id`),
  KEY `IDX_8C9078EBE10A5A83` (`Merchant_id`),
  CONSTRAINT `__FK_8C9078EBAF5CDCE0` FOREIGN KEY (`Batch_id`) REFERENCES `batch` (`id`),
  CONSTRAINT `__FK_8C9078EBE10A5A83` FOREIGN KEY (`Merchant_id`) REFERENCES `merchant` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10388795 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

We would like to find the row which:

Is not Barred
Has Allocated < Max Uses
Has an Expiry Date greater than a certain date
Is a member of one or more batches
Is the soonest expiring (i.e. ExpiryDate is closest to now)
We only need 1 of the rows.

Our query at the moment looks like this:

    SELECT mc.*
    FROM merchant_coupon mc
    WHERE 1 = 1
      AND mc.IsBarred = 0
      AND mc.AllocatedCount < mc.MaxUses
      AND mc.ExpiryDate > '2018-11-13 18:44:17'
      AND (mc.Batch_id = 288
               OR mc.Batch_id = 446
               OR mc.Batch_id = 561
               ...
               OR mc.Batch_id = 4167
               OR mc.Batch_id = 5731
        )
    ORDER BY mc.ExpiryDate ASC
    LIMIT 1

This works, but we suspect there are opportunities to improve it.

The explain looks like this:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,mc,range,"IDX_8C9078EBAF5CDCE0,IDX_8C9078EBE10A5A83",IDX_8C9078EBAF5CDCE0,5,,184481,Using where; Using filesort

Some other things to note:

The database is MySQL
The IDX_8C9078EBAF5CDCE0 is on the Batch_id. We also have an index on the Merchant_id
The Batch_id normally groups the rows into groups of between 2-10,000 items
The Batch that the Batch_id refers to also refers to the Merchant_id, so we could eliminate the AND Merchant_id = <x>, but we keep it as a safety check, just in case
The table contains about ~10 million rows, and is growing in the order of ~20,000-60,000 a week atm.
Once a row is selected, the Allocated column will be incremented - we do a SELECT ... FOR UPDATE in fact.
IsBarred is mostly 0.
In most cases MaxUses is 1, but in some it can be a number in the range of 1-1000.

What indexes would most likely optimise the query? Or, what changes could we make to the query to optimise it?


texts: Does an excessive table violate normalization rules?

texts:
Here is an example of some tables that exist in a database where I work. The data isn't actually around schools, but the structure is identical.

There are four tables:

** School **
School Id, School Name

** ClubType **
ClubType Id, ClubType Name

** Club **
Club Id, School Id, ClubType Id

** Student **
Student Id, Name, Club Id

Knowing that the Club table will never have additional columns (because the real data isn't actually about school clubs),

I believe a clearly better design, eliminating the Club table to avoid joins, would be:

** School **
School Id, School Name

** ClubType **
ClubType Id, ClubType Name

** Student **
Student Id, Name, School Id, ClubType Id

Edit: We also know that each club id may only have one type. The relationship from Club to ClubType is 1 to 1.

My question is, does the first example violate some known rule of database normalization or some other mathematical principle? Or is it just a case of poor design?


texts:
I have created a fresh db dump from a production server with the --data-only and --column-inserts flags, so I only have a bunch of insert statements to insert data when performing a restore on a staging server.

pg_dump -h localhost -U adminuser --data-only --column-inserts maindb > maindb.sql

How do I delete all data in the staging server database first, before restoring the data from the production dump?

I want to delete all data only so I don't have to drop and create the database and all that stuff. I just want to remove data and insert new data that is all.

I don't have the option to drop and create the database for several reasons. I will have to remove all data and just insert only, so whatever it takes to find how to do this, am willing to go for it but need help obviously to start with.

I also need to automate this process. Will automate "dumping data from production db", then "deleting data on staging db", and then "restoring data to staging db". I just need help on the "deleting data on staging db" part.

I am running on PostgreSQL 9.5.2


texts: Can multiple fact tables have the same structure?

texts:
I'm designing the schema of a data base and I have two solutions :
the first is to use one fact table linked to multiple dimensions.
the second is to use multiple fact tables with same structure for each group (I have eight groups ) and all these fact tables are linked to the same eight dimensions.

I think the second solutions sounds good because I can avoid some joins to analyse per group.But I'm not sure about using multiple fact tables with same attributes for each group.
for example :
Fact1:
Id_Ap #|Id_cst#|Id_Ac#|Id_Dt#|Bp
Fact2:
Id_Ap#|Id_cst#|Id_Ac#|Id_Dt#|Bp
.. Fact 8 : the same.


texts: MySQL remote connection: Public vs. private IP address

texts:
I just went through the obligatory pain of getting MySQL to accept a remote login. Yes, I had to create a user of the form ''@'%', and I did so. Yes, I had to GRANT ALL ON . to my new '@%' user and then flush privileges, and I did so. Yes, I had to open up port 3306 on the server, and I did so. But, trying to access MySQL via HeidiSQL, I continued to have no luck at all.

Cutting to the chase, my struggle was with the bind address. I'm running Ubuntu Linux on an AWS server instance, so I have a public IP address and a private IP address. And of course there's always the MySQL default IP address of 127.0.0.1. That default wasn't allowing me any remote connections, though, so I figured I should try the public IP. But in that case, MySQL would not start. Finally I took a shot and used the private IP address as the bind address, and everything fell into place: I was able to stop and restart MySQL, and I was able to connect from HeidiSQL

But I am totally confused. HeidiSQL addresses this server via its public IP address. But MySQL refuses to bind to the public IP address! (As I said, when I tried, MySQL wouldn't even start.) MySQL has the private IP address as its bind address. What's going on here? Why, to get a remote connection, do I need HeidiSQL targeting port 3306 at the public IP address but MySQL bound to the private IP address?


texts: Why is a temp table a more efficient solution to the Halloween Problem than an eager spool?

texts:
Consider the following query that inserts rows from a source table only if they aren't already in the target table:

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

One possible plan shape includes a merge join and an eager spool. The eager spool operator is present to solve the Halloween Problem:

On my machine, the above code executes in about 6900 ms. Repro code to create the tables is included at the bottom of the question. If I'm dissatisfied with performance I might try to load the rows to be inserted into a temp table instead of relying on the eager spool. Here's one possible implementation:

DROP TABLE IF EXISTS #CONSULTANT_RECOMMENDED_TEMP_TABLE;
CREATE TABLE #CONSULTANT_RECOMMENDED_TEMP_TABLE (
    ID BIGINT,
    PRIMARY KEY (ID)
);

INSERT INTO #CONSULTANT_RECOMMENDED_TEMP_TABLE WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT new_rows.ID
FROM #CONSULTANT_RECOMMENDED_TEMP_TABLE new_rows
OPTION (MAXDOP 1);

The new code executes in about 4400 ms. I can get actual plans and use Actual Time Statistics™ to examine where time is spent at the operator level. Note that asking for an actual plan adds significant overhead for these queries so totals will not match the previous results.

╔═════════════╦═════════════╦══════════════╗
║  operator   ║ first query ║ second query ║
╠═════════════╬═════════════╬══════════════╣
║ big scan    ║ 1771        ║ 1744         ║
║ little scan ║ 163         ║ 166          ║
║ sort        ║ 531         ║ 530          ║
║ merge join  ║ 709         ║ 669          ║
║ spool       ║ 3202        ║ N/A          ║
║ temp insert ║ N/A         ║ 422          ║
║ temp scan   ║ N/A         ║ 187          ║
║ insert      ║ 3122        ║ 1545         ║
╚═════════════╩═════════════╩══════════════╝

The query plan with the eager spool seems to spend significantly more time on the insert and spool operators compared to the plan that uses the temp table.

Why is the plan with the temp table more efficient? Isn't an eager spool mostly just an internal temp table anyway? I believe I am looking for answers that focus on internals. I'm able to see how the call stacks are different but can't figure out the big picture.

I am on SQL Server 2017 CU 11 in case someone wants to know. Here is code to populate the tables used in the above queries:

DROP TABLE IF EXISTS dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR;

CREATE TABLE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR (
ID BIGINT NOT NULL,
PRIMARY KEY (ID)
);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT TOP (20000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
OPTION (MAXDOP 1);

DROP TABLE IF EXISTS dbo.A_HEAP_OF_MOSTLY_NEW_ROWS;

CREATE TABLE dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (
ID BIGINT NOT NULL
);

INSERT INTO dbo.A_HEAP_OF_MOSTLY_NEW_ROWS WITH (TABLOCK)
SELECT TOP (1900000) 19999999 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;


texts: Rule of thumb on what changes require a database reboot

texts: I am working on enabling SSL for database listeners. As a part of it, I have to modify the sqlnet.ora and listener.ora files. Does this require a database bouncing to take effect? Is there a general rule of thumb that I can follow when deciding whether or not to reboot the database for some changes to take place.


texts:
Are there any best practices when configuring TDE in SQL Server 2008? On SQLMag, the article "Transparent Data Encryption FAQs" says the CPU may have increased usage by up to 30%?

Other than adding server horsepower, is there anything else DBAs typically do when turning on TDE?


texts:
Assuming that there are only two available paths, say /log and /data. Would it be better to set DBPATH to /log or /data. I.e.

CREATE DATABASE WHATEVER
AUTOMATIC STORAGE YES
ON /data DBPATH ON /log;
UPDATE DB CFG WHATEVER USING newlogpath /log;

or

CREATE DATABASE WHATEVER
AUTOMATIC STORAGE YES
ON /data DBPATH ON /data;
UPDATE DB CFG WHATEVER USING newlogpath /log;

I noticed the other day a system that used /data for both DBPATH and tablespace storage, and I'm wondering whether /log would have been a better choice for DBPATH?

Any thoughts?


texts: How to find the size, name and other details of Analysis server database?

texts:
How to find the size, name and other details of an Analysis Services database ?

I have a Analysis Services server and I can see one database inside that.

I want to know the size and other basic details of this DB.


texts: How to views all documents in a particular collection of database in MongoDB through mongo Shell?

texts:
In the MongoDB shell, how to views all documents in a particular collection for the current database that I'm using?

when i am trying through the query

> db.getCollection().find()

To getting the error as mention below

2017-10-14T00:57:34.363+0530 E QUERY    [thread1] Error: collection constructor called with undefined argument :
DB.prototype.getCollection@src/mongo/shell/db.js:34:16
@(shell):1:1

I am also uploading the screen shot of mongo shell command prompt here


texts: Transaction Table in Personal Finance App

texts:
I'm a noob designing a Personal Finance App to teach myself Rails. This is just for learning purposes, but I actually intend to use it to replace my current system in Google Spreadsheets. I want to apply best practice.

I created a transactions table with the following columns:

user_id: integer
type: string (income or expense)
date: date
account_group_id: integer
account_id: integer
amount: integer (my currency does not need decimals)
comment: string

Now, the way is this designed, all users' transactions would be recorded on the same table. Is this a good idea?

Or should I create a different transactions table for each user?

Does it matter if I do it either way?


texts: I have Change Data Capture enabled and working fine.  The question that I have is how do I query the data to show users what changes occurred.


texts: SSIS Package fails to "Transform File and create output file"

texts:
I have an SSIS (2012) package which was written by one of the developers.  I've been tasked to with fixing the error. Each time the package is run I receive the following three messages indicating the Write Output File [339] produced errors.

Transform File and create output file:Error: Write Output File failed the pre-execute phase and returned error code 0xC020200E

Transform File and create output file:Error: Cannot open the datafile "\\servershare\folder\file"

Transform File and create output file:Warning: Access is denied.

I have tried granting full control to the folder share to myself, the SQL Agent account, and the SQL Services account. Whether I run the package as myself or as a service account I receive the error messages.

I'm not seeing any more details in the SQL Server error logs or the Windows Event logs.


texts: An entitiy with at least one weak entity

texts:
I have the following design:

There is a list of parents in my database. A parent should have at least one child,  and a child must belong to a parent (forget about the husband/wife relations).
(a child is a weak entity)

(No need to add rules for deletion/ update)

How can I achieve this?

I write SQL but I have the problems:

Create Table Parent(
  parentName Varchar(255),
  -- Should a parent have an entity named child?
  -- But a parent can have more than one child
  PRIMARY KEY(parentName)
);

Create Table Child(
 parentName varchar(255),
 name varchar(255),
 PRIMARY KEY(parentName, name),
 FOREIGN KEY (parentName) References Parent(parentName));

I'm assuming that I have the following error:

A parent should not exist without a child. However, I could not manage to maintain this.

As an example

parent 1, child1

parent 1, child2

parent 2, child2 --Not allowed!

parent 3, null  -- Not allowed!

null, child 4 -- Not allowed!

Also, I need a child for other relations as well. So, in my opinion, combining child entity with a relation in a table is not a good solution for me.


texts: What is best practice for confirming success of Update queries

texts:
If I am doing a SQL Update query that affects, for instance, 1000 rows, how do I check that the query worked as intended and didn't hit any strange edge cases, (short of examining each row visually myself)?
What is best practice for this ?

Additional info requested in comments
I was writing a SQL query to update any fields in a column that were blank with a standard value. That by itself is pretty easy and the number of fields were small enough that I could check them visually. But I figured there could be much more complex queries covering larger numbers of fields where examining wasn't feasible.


texts: Is this proper way to link parent and child tables with logical data instead of parent and child using primary and foreign key

texts:
I have two tables one is product which is a parent table with one primary key and i do have another child table of product, which is a product_details table. But the child table is linking with parent table(product) with logical data instead of foreign key,as we are doing this relationship with the help of java code in the coding side, instead of depending on the data base, which make it as tight couple.To avoid tight coupling between the tables we are storing the primary key value in the child table.

Please refer this image with data which is creating the logical relationship between Product and Product Details tables.

The Scripts are :

CREATE TABLE `tbl_product` (
  `product_id` varchar(200) NOT NULL,
  `product_details_id` varchar(200) DEFAULT NULL,
  `currency` varchar(20) DEFAULT NULL,
  `lead_time` varchar(20) DEFAULT NULL,
  `brand_id` varchar(20) DEFAULT NULL,
  `manufacturer_id` varchar(150) DEFAULT NULL,
  `category_id` varchar(200) DEFAULT NULL,
  `units` varchar(20) DEFAULT NULL,
  `transit_time` varchar(20) DEFAULT NULL,
  `delivery_terms` varchar(20) DEFAULT NULL,
  `payment_terms` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 CREATE TABLE `tbl_product_details` (
  `product_details_id` varchar(200) NOT NULL,
  `product_id` varchar(200) DEFAULT NULL,
  `product_name` varchar(50) DEFAULT NULL,
  `landingPageImage` varchar(100) DEFAULT NULL,
  `product_description_brief` text CHARACTER SET latin1,
  `product_description_short` text CHARACTER SET latin1,
  `product_price_range` varchar(50) DEFAULT NULL,
  `product_discount_price` varchar(20) DEFAULT NULL,
  `production_Type` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`product_details_id`),
  UNIQUE KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Please suggest me, we are following this kind of relationship in my company, as the manager is saying it will give us flexible to us. Please suggest me the Pros and Cons of the design.I know that if we lose the data from the table, we can't know the relationship between the two tables.


texts: T-SQL - Enforce Atomic Operations x Locks

texts:
I have a database model that works similar to a banking account (one table for operations, and a trigger to update the balance). I'm currently using SQL Server 2008 R2.

Table OPERATIONS:

VL_CREDIT decimal(10,2)
VL_DEBIT decimal(10,2)

Table BALANCE:

DT_OPERATION datetime
VL_CURRENT decimal(10,2)

Procedure INSERT_OPERATION

GET LAST BALANCE BY DATE
CHECK IF VALUE OF OPERATION > BALANCE
   IF > RETURN ERROR
   ELSE INSERT INTO OPERATION(...,....)

The issue I have is the following:

The procedure to insert the operation has to check the balance to see if there's money available before inserting the operation, so the balance never gets negative. If there's no balance, I return some code to tell the user the balance is not enough.

My concern is: if this procedure gets called multiple times in a row, how can I guarantee that it's atomic?

I have some ideas, but as I am not sure which would guarantee it:

BEGIN TRANSACTION in the OPERATION procedure
Some sort of lock on selecting the BALANCE table, but it must hold until the end of procedure execution

Can you suggest some approach to guarantee that? Thanks in advance.


texts: How to get the SUM between dates in a WHERE clause?

texts:
I'm trying to get the SUM between each date in the WHERE clause of the following query, not the cummulative sum, but the sum for the period in between the dates. For example:

Query:

SELECT
cdr.datefield AS 'date',
a.id AS 'id',
a.name AS 'name',
SUM(t.debit_amount - t.credit_amount) AS 'balance'
FROM calendar cdr
JOIN transactions t ON (cdr.datefield >= t.value_date)
JOIN accounts a ON (a.id = t.account_id)
WHERE cdr.datefield IN ('2014-03-31', '2013-03-31', '2012-03-31')
GROUP BY cdr.datefield, a.id
ORDER BY a.id ASC

Records:

transactions:
123, 2012-01-01, 10000, 100, 0
124, 2012-01-01, 30000, 100, 0
125, 2013-01-01, 20000, 200, 0
126, 2014-01-01, 20000, 0, 100
127, 2014-01-01, 10000, 0, 200

Desired Result:

date        id      name                balance
-----------------------------------------------
2014-03-31  10000   Cash                -200
2014-03-31  20000   Payables            -100
2014-03-31  30000   Issued Capital      0

2013-03-31  10000   Cash                0
2013-03-31  20000   Payables            200
2013-03-31  30000   Issued Capital      0

2012-03-31  10000   Cash                100
2012-03-31  20000   Payables            0
2012-03-31  30000   Issued Capital      100

Tables:

calendar:
datefield (date)
transactions:
id (int), value_date (date), account_id, debit_amount (decimal), credit_amount (decimal)
accounts:
id (int), name (varchar), description (varchar)

I've already tried the following GROUP BY statements with no luck:

GROUP BY YEAR(cdr.datefield), MONTH(cdr.datefield), a.id
GROUP BY YEAR(cdr.datefield), a.id
GROUP BY YEAR(t.value_date), MONTH(t.value_date), a.id
GROUP BY YEAR(t.value_date), a.id

I'm thinking my problem has to do with the fact that I want to group by dates that not necessarily belong to the same year.

How can I modify my query to get the desired result?

Thanks!


texts: Why does the log file grow larger than data file for an availability group database on the secondary replica?

texts:
I've got a SQL Server 2016 AG set up with only two replicas (primary and secondary). Both synchronous, non-readable secondary, and backup the primary only.
Transaction Log backups occur on the primary replica via scheduled SQL Jobs that execute on both replicas every half hour. Full backups occur nightly. FULL recovery model.
The issue is over time the secondary replica log file eventually grows to be larger than the data file, while the primary remains fine. The only thing I do that helps is a manual failover, which seems to kick off the log backup jobs on the (now old) secondary replica since its readable again. Any tips on how to handle this?


texts: MySQL: selecting arbitrarily ordered rows after a specific id

texts:
Please excuse the awkward wording of my title, I'm not quite sure how to phrase what I'm asking.

Suppose I have a table like this:

id  value
1   10
2   40
3   20
4   50
5   40

Running this query:

SELECT * FROM table ORDER BY value DESC, id DESC

Results in:

id  value
4   50
5   40
2   40
3   20
1   10

And this query:

SELECT * FROM table ORDER BY value DESC, id DESC LIMIT 2

Results in:

id  value
4   50
5   40

How do I select the next two rows? i.e. the two rows that come after id=5 when you sort by descending value without using an offset (LIMIT 2,2)

id  value
2   40
3   20


texts:
Taking on a new dba role for an Oracle 11gR2 2 node RAC, I've been told that about 9 times a year, in schedule with other software releases, we intentionally shutdown and restart the database.  It causes all sorts of havoc -  we have unbalanced nodes and jobs that ran fine before end up on a different node running poorly, re-caching of everything, etc.  The database struggles for a few days as we put out new fires.

When I inquire as to why, it 'helped in the past' but nobody knows exactly why.  Is there a valid reason?  Is there a known memory leak somewhere in the Oracle RAC configuration that makes this needed?  (That would be shocking.) A similar question for MS SQL Server indicates 'no', but Oracle is generally quite a bit different.


texts: select columns with different conditions

texts:
This question focus to the COUNT result

1st query will show the COUNT with date condition.

2nd query will show the COUNT without date condition.

Now I need a query which make the COUNT result in 1st query without date condition which is :
COUNT(c.CustomerID) > ONLY with condition b.BookingCustomerID=c.CustomerID
(without date condition)

c.CustomerTelephone, b.* > with condition WHERE b.BookingCustomerID=c.CustomerID
AND BookingDate LIKE '%2019-05-04%'

Thanks.

SELECT COUNT(c.CustomerID), c.CustomerTelephone, b.*
FROM Booking b, Customer c
WHERE b.BookingCustomerID=c.CustomerID
AND BookingDate LIKE '%2019-05-04%'
GROUP BY c.CustomerID

SELECT COUNT(c.CustomerID), c.CustomerTelephone, b.*
FROM Booking b, Customer c
WHERE b.BookingCustomerID = c.CustomerID
GROUP BY c.CustomerID


texts:
I am creating a database that will hold a number of transaction records.

I was wondering if it is a good idea or good practice to use a hash function that can be replicated easily for verification as the ID column. i.e. SHA1(UserID + TimeOfTransaction)


texts: normalize this! allow user-defined ordering but fall back to default order

texts:
We have vendors, and they can either be company-wide (office_id=null) or office-specific (office_id is set). Company-wide vendors have an order, this is managed through the Company admin:

TABLE vendor
 - vendor_id (pk)
 - office_id (fk, nullable)
 - name
 - order (int)
 - (etc.)

By default all offices inherit the company vendors and their order. But offices are allowed to manage the order of all vendors independently if they wish. If they arrange the order of their Vendors, we create records in vendor_order:

TABLE vendor_order
 - vendor_order_id (pk)
 - vendor_id (fk)
 - office_id (fk)
 - order (int)

We have this query that gets all the vendors for an office (both company and office-specific), and orders them, first by using vendor_order.order if it exists, then falling back to vendor.order. It works fine, but I think it smells a little funny:

-- GET ALL VENDORS FOR OFFICE IN CORRECT ORDER
SELECT `vendor`.`vendor_id`,
       `vendor`.`office_id`,
       `vendor`.`name`,
       `vendor`.`order`,
       Coalesce(`vendor_order`.`order`, `vendor`.`order`) AS `office_order`
FROM `vendor` LEFT OUTER JOIN `vendor_order`
    ON (`vendor`.`vendor_id` = `vendor_order`.`vendor_id` AND `vendor_order`.`office_id` = 2)
WHERE (`vendor`.`office_id` = 2 OR `vendor`.`office_id` IS NULL
    AND (`vendor_order`.`office_id` = 2 OR `vendor_order`.`vendor_order_id` IS NULL))
ORDER  BY `office_order` ASC

QUESTIONS

Can this schema be improved/normalized?
Should we just use vendor_order table for the company order too?


texts: Restoring a database, changing recovery model

texts:
I regularly make a backup of my production database, then restore the same onto a test database, so developers can always have recent data for testing.
The production database uses full recovery model, but the test database would be better off with a simple recovery model.
So I thought to change the script, and after the database reload, running on the test database the commands:

USE master ;
ALTER DATABASE TESTDB SET RECOVERY SIMPLE ;

I read that the change in recovery model only has effect after a backup is taken.
However, if after the performing the above I do

SELECT name, recovery_model_desc
  FROM sys.databases

I can see that the recovery model is reported as simple for TESTDB.
Of course to be sure I could take a backup of TESTDB (and discard it immediately), but I would avoid it if possible.

Do I need to take a backup to actually change the recovery model of my test database to simple?


texts: How to design this database to avoid cyclic dependency?

texts:
There are two tables:

User
Address

User contains a reference to Address.

Address contains the columns CreatedBy and ModifiedBy, which is reference to User.

How do I design this database to avoid a cyclic dependency?


texts:
Is there a way (registry hack is acceptable) to remove options from the context menus in SSMS?

Specifically I'd like to remove the delete option from the context menu when you right click on a merge replication publication.  If we really want it deleted we'll do it via tsql.

And yes, there is a tragic tale motivating this question.


texts: I have a stored procedure that is stalling the database. If the stored procedure is run as a query, the results arrive nearly instantly. If the stored procedure is run as a stored procedure, with it's single parameter, it stalls the database. I'm at a loss as to how to fix the issue since there isn't anything wrong with the query on its own, just as a stored procedure. Any ideas?


texts: Reorganize and Rebuild Indexes with active connections

texts: I want to rebuild or reorganize indexes in a table. What happens if there are active connections to that table at the moment?


texts:
Is there any possibility of having built-in materialized views in PostgreSQL ?

I don't want Gardner version or somehthing like physical table populated by triggers.
I've read that was/is something being deploy...


texts: Can't add another node from primary node with MongoDB replication set

texts:
On CentOS 7.

MongoDB version: 3.2.15

There are two nodes:

node1
node2

node1

/etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 node1
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.0.100              node1
192.168.0.101              node2

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# Where and how to store data.
storage:
  dbPath: /mongo-metadata
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# how the process runs
processManagement:
  fork: true  # fork and run in background
  pidFilePath: /var/run/mongodb/mongod.pid  # location of pidfile

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1 192.168.0.100  # Listen to local interface only, comment to listen on all interfaces.

security:
  keyFile: /root/keyfile

#operationProfiling:

replication:
  replSetName: rs0

#sharding:

## Enterprise-Only Options

#auditLog:

#snmp:

node2

/etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 node2
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.0.100              node1
192.168.0.101              node2

/etc/mongod.conf

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# Where and how to store data.
storage:
  dbPath: /mongo-metadata
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# how the process runs
processManagement:
  fork: true  # fork and run in background
  pidFilePath: /var/run/mongodb/mongod.pid  # location of pidfile

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1 192.168.0.101  # Listen to local interface only, comment to listen on all interfaces.

security:
  keyFile: /root/keyfile

#operationProfiling:

replication:
  replSetName: rs0

#sharding:

## Enterprise-Only Options

#auditLog:

#snmp:

Run mongo command on node1

mongo
rs.initiate()
use admin
db.createUser(...)
db.auth('admin', '...')
rs.add('node2')

Caused error:

{
    "ok" : 0,
    "errmsg" : "Quorum check failed because not enough voting nodes responded; required 2 but only the following 1 voting nodes responded: node1:27017; the following nodes did not respond affirmatively: node2:27017 failed with No route to host",
    "code" : 74
}

Is it can't connect to node2 from node1? Is there a official guide about how to do mongodb replication set?


texts: Can SQL Server Audit be used to track hostnames or IP adresses of the users performing DML on a table?

texts:
I have been reading about SQL Server Audit, and so far I have assimilated that it tracks the command that has been executed, and some other additional information(SPID, object ID and stuff).
But can this be used to uniquely identify the individual that did it, or some type of unique ID of the computer from which the command was executed, such as host name or IP address?

I just do not want to waste my time reading it if it can not uniquely identify the machine or the person executing the command.


texts: MySQL replication without baseline data import

texts:
I've been reading about MySQL replication and all the resources I've looked at say that a data import (be it a mysqldump, or a raw data dump import) is needed before replication can start. I'm trying to understand why is that. Would it be possible to start replication from a master, where binary logs were saved from the beginning, by telling the slave to replicate from log position 1 (not sure if it's supposed to be 1 or 0 also).

Thanks in advance


texts: Does a login need to be created on MSSQL Always On replica before being granted roles on the primary?

texts:
Consider a SQL Server Login MyLogin that exists on PrimaryServer but not SecondaryServer in an Always-On Availability group. MyLogin has a number of roles already assigned.

If the login MyLogin is later created on SecondaryServer using the hashed password and SID from PrimaryServer, will it automatically have the same roles on the database mirrored on SecondaryServer?

Does it matter that the permissions were granted on the database on the PrimaryServer before the login was created on SecondaryServer?


texts: Detecting & Preventing Data Loss in MySQL Database (InnoDB)

texts:
Around April 2017 I noticed that my database lost records in a few columns of 2 tables, this happened inside 5 of databases my website is using. They are clones just with different data records. This happened after hosting company server maintenance. A few days ago the exact same issue happened again. I’ve rolled back the data from the backups again.

I’m really stressed about this and I’m searching for the solutions.

The perfect thing would be to prevent it from happening ever again, but I don’t know how to do it and first of all what is the exact mechanism behind this data loss. Hosting company technical support could not tell me either.
As long as I’m aware about data loss fast (within 1 day) and it happens extremely rarely I’m fine with backups, however, I’m afraid that as database will grow larger I might not notice what have happened in time.

Are there any quick ways to detect data loss inside MySQL database?

The only ways I see for now for detecting it is to inspect each of the databases manually, open them with some database manager and sort the columns to see if there are any empty cells. Or dumping them and restoring locally on my pc, and then comparing tables with more advanced database managing software. However this is painful as it takes time, it would take probably 30-60 min daily.

Any suggestions would be greatly appreciated.

I'm on MySQL 5.5.41, FreeBSD & Apache.