texts: PostgreSQL: alternative ways to connect to database

texts:
THIS IS A THEORETICAL QUESTION. I am posting it on this site because of this and because I think that database administrators could provide more accurate insight. The line that separates the two sites is still not crystal clear to me so I may have got it wrong.

Long story short: there is a certain Postgres database, and there is a need of creating a form like access to it, and I have run out of ideas and/or lack some knowledge as to how to do some things (in case they can be done at all)

There are some fields of a single table that need to be accessed to to read and edit them on demand. The rest of the data is imported into the database following a traditional ETL process, but these pieces of information need to be inserted manually and on demand, and the users will be business people who are incredibly fond of Excel.

Below are some of the options I have considered and the reasons as to why they have been rejected:

Web form: the time needed to develop it is not affordable.
Distribute an Access Application based form with an ODBC connection to DB: every single user would have to install both MS-Access and the psqlODBC driver and they are strongly against this *sigh*
Having a similar form to that based on Access, but developed in Excel: from what I have seen and tried, pulling data from PostgreSQL to Excel is viable although not so trivial (I have had some issues with the casing of most names, as Windows is case insensitive while Postgres is not), but the reverse is not. Maybe there is a VBA solution to do the push? I know some VBA and could manage to get a solution if this is viable. I know how to do this through VBA in Access but the VBA used in Excel has a different set of resources and I don't know them that much.
Having this single table managed by a FDW (Foreign Data Wrapper) and distribute an Excel file linked to it (with a form-like "interface"): from my research I have found that importing/pulling data from a csv file is a common task but I have not found anything on reversing the data flow: editing the "remote" csv file from Excel. If this can be done, truly dynamically linking a csv file to excel so that it can be updated, it could pose as a viable solution

Any suggestion or insight as to how can this be done will be greatly appreciated.

Edit:
The options number 3 and 4 described above are oficially rejected. My brain must have gone numb or something to even consider Excel pushing data a possibility. It has never been able to do that, it just pulls, visualizes and analyzes data. That's it. That's all there is to Excel.

Edit2:
We have successfully made it clear to the client that what he wants from this project under these conditions is plainly impossible. So they have reconsidered it and the constraints set have changed. I will be doing a web app form, which is the best solution in terms of software needed to run it.
I hope this can be of help to anyone who ever runs into a similar situation. Thanks for the attention to those who cared to read (and write) :)


texts: SQL Server 2005 database corruption. Advise

texts:
I have encounter some database corruptions in the past but I knew which object has hit the road. In this situation I run a DBCC CHECKDB against a problematic  SQL Server 2005 database and I get the following errors

    Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:2388) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:8807) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
Msg 8929, Level 16, State 1, Line 1
Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 281474980642816 (type In-row data): Errors found in off-row data with ID 1454243840 owned by data record identified by RID = (1:728:2)
Msg 8928, Level 16, State 1, Line 1
Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data): Page (1:2388) could not be processed.  See other errors for details.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). The off-row data node at page (1:2388), slot 0, text ID 1454243840 is referenced by page (1:728), slot 2, but was not seen in the scan.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'sys.sysobjvalues' (object ID 60).
Msg 8928, Level 16, State 1, Line 1
Object ID 393768460, index ID 1, partition ID 72057594063421440, alloc unit ID 72057594069647360 (type In-row data): Page (1:8807) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 393768460, index ID 1, partition ID 72057594063421440, alloc unit ID 72057594069647360 (type In-row data). Page (1:8807) was not seen in the scan although its parent (1:2862) and previous (1:8806) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 393768460, index ID 1, partition ID 72057594063421440, alloc unit ID 72057594069647360 (type In-row data). Page (1:10128) is missing a reference from previous page (1:8807). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'reservation_packages' (object ID 393768460).
Msg 8928, Level 16, State 1, Line 1
Object ID 1513772450, index ID 0, partition ID 72057594064994304, alloc unit ID 72057594071285760 (type In-row data): Page (1:5211) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1513772450, index ID 0, partition ID 72057594064994304, alloc unit ID 72057594071285760 (type In-row data), page (1:5211). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 63047689 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'reservation_services_log' (object ID 1513772450).
CHECKDB found 0 allocation errors and 10 consistency errors in database 'ChJ'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ChJ).

Object ID 0?? index ID -1 ?? partition ID 0?? I cannot make out anything out of it

The database is in Simple Recovery mode (unfortunately) and I have a month old backup.

I must have it up an running because it contains vital data.

I would appreciate any help you could give me.

Thanx in advance!

EDIT1:Unfortunately Red Gate's Data Compare cannot register the database while it is scanning the tables... :(


texts: MongoDB new secondary replica set node has much smaller data folder (dbPath)

texts:
I have a 3 node replica set that's been online since mid december. Last Friday (Jan 24), we added a 4th node in our disaster recovery data center.

We used the automatic initial sync procedure as our data set is relatively small. No problems here, after a while it's status switched to secondary and rs.printSlaveReplicationInfo() tells me everyone is in sync with little lag.

A coworker asked me to confirm that the new node was up to date and my instinct was to compare the size of the data folders (dbPath).

The new node's dbPath is ~1.88GB, but the average size for the other nodes is ~3.7GB. Why is the new node's dbPath smaller?

Thanks!

Update:
The real question is, how do I figure out if a new node of the Replica Set is fully synced?


texts: RSAT on Windows 10 fails to add node to server 2016 cluster

texts:
I have been testing server 2016 failover clustering. I have 3 hyper-v VMs running server 2016 core. I installed the RSAT tools on Windows 10 Pro to use the failover clustering wizard. I cannot add a node to the 2016 cluster using the Win10 machine, getting an error about the operating systems being different. When I go to a server 2016 machine with desktop experience, I can use the same wizard to successfully add the node. I assume it is because the OSs are the same.

My question is...is it true that a Windows 10 Pro machine will not be able to administer a server 2016 cluster?


texts:
I am experiencing several ORA-00600 error events, presumably during exceptionally high I/O. Does anyone know where I can lookup the meaning of the following arguments for ktspfmdb:objdchk_kcbnew_3? I.e. what does 13, 873730 and 4 stand for?

ORA-00600: internal error code, arguments: [ktspfmdb:objdchk_kcbnew_3], [13], [873730], [4], [], [], [], [], [], [], [], []


texts:
I have to re-build a mysql database (some tables were truncated), Is it possible? What logs do I need?

My mysql is 5.7 running on ubuntu 18.4


texts: What does a CONSTRAINT have to do with my unique index?

texts:
I have to indexes on my table.  The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change my CREATE INDEX statement to add that to it.

"customers_phonetype_customer_id_176731583d230ba5_uniq" UNIQUE CONSTRAINT, btree (customer_id, display) ;-- Django created
"customers_phonetype_customer_id_uniq" UNIQUE, btree (customer_id, lower(display::text)) ; -- Manually created

This is how I manually created the second index:

create unique index customers_phonetype_customer_id_uniq on customers_phonetype (customer_id, lower(display));

What is the meaning of the extra word CONSTRAINT in this case? I'm not seeing it in the docs.


texts: Why are some tables in PostgreSQL available from all databases in my server?

texts:
I found out that in my PostgreSQL server, there are 3 tables that I can call from any database. When I use \d command in psql I see those tables no matter to which database I'm connected.
I didn't even know it is possible for tables to be accessible from all databases.

What is this feature in PostgreSQL and how should I hide them from other databases?


texts: Postgres pg_read_all_stats default role cannot read from my schema

texts:
I have a postgres user created with the pg_read_all_stats default role permissions which states the user should be able to read as if superuser. This works fine for any of the pg_stat_* tables.

However I need some data from the pg_stats table. These queries do not show rows for my applications in the public schema.

The documentation does not indicate how this table should behave when interacting with my applications public schema. Am I completely out of luck for querying this table with pg_read_all_stats permissions?


texts: What is the best way to remove large amount of data from large tables?

texts:
I have two large SQL 2008 tables:

table_header with 7 columns and ~ 2 million records
table_detail with 22 columns and ~ 78 million records

The common field between the two is called item_name which is varchar(32).

I need to remove all records from table_detail where item_name is not in table_header.

What is the best way to accomplish this task?

This is a busy production server. There is no maintenance scheduled anytime soon, but this data needs to be gone now. I can't just run something like:

delete from table_detail
where item_name not in (
select item_name from table_header);


texts: hierarchical query for all tables in a given schema

texts:
I am trying to write a hierarchical query to show me all of the tables in a given schema from parents to children to grand children, etc. I split the code into a view and a query to hopefully make it more simple. I get back the parent tables, but none of the children or grandchildren.

Thanks

    CREATE VIEW tables_heirarchy_view
AS
WITH parent_table
AS ( SELECT DISTINCT
            rfc.constraint_catalog,          rfc.constraint_schema,    rfc.constraint_name,
            rfc.unique_constraint_catalog AS parent_constraint_catalog,
            rfc.unique_constraint_schema  AS parent_constraint_schema,
            rfc.unique_constraint_name    AS parent_constraint_name,
            tcp.table_catalog             AS parent_table_catalog,
            tcp.table_schema              AS parent_table_schema,
            tcp.table_name                AS parent_table_name
       FROM information_schema.referential_constraints   rfc
      INNER JOIN information_schema.table_constraints    tcp
         ON rfc.unique_constraint_catalog = tcp.constraint_catalog
        AND rfc.unique_constraint_schema  = tcp.constraint_schema
        AND rfc.unique_constraint_name    = tcp.constraint_name  )
SELECT tc.table_catalog,                    tc.table_schema,               tc.table_name,
       tc.constraint_type,                  tc.constraint_catalog,         tc.constraint_schema,
       tc.constraint_name,                  rfc.parent_constraint_catalog, rfc.parent_constraint_schema,
       rfc.parent_constraint_name,          rfc.parent_table_name
  FROM information_schema.table_constraints tc
  LEFT OUTER JOIN parent_table              rfc
    ON tc.constraint_catalog               = rfc.constraint_catalog
   AND tc.constraint_schema                = rfc.constraint_schema
   AND tc.constraint_name                  = rfc.constraint_name
 WHERE tc.constraint_type IN ( 'PRIMARY KEY', 'FOREIGN KEY' )
 ORDER BY 1,2,3;

WITH RECURSIVE tables_heirarchy_with
    AS ( SELECT cur_view.sort_table_catalog,         cur_view.sort_table_schema,         cur_view.sort_table_name,
                cur_view.constraint_type,            cur_view.table_name,                cur_view.constraint_name,
                cur_view.parent_constraint_catalog,  cur_view.parent_constraint_schema,  cur_view.parent_table_name,
                cur_view.parent_constraint_name,     1 AS level
           FROM tables_heirarchy_view             AS cur_view
          WHERE cur_view.parent_table_name IS NULL
          UNION ALL
         SELECT child_rec.sort_table_catalog,        child_rec.sort_table_schema,        child_rec.sort_table_name,
                child_rec.constraint_type,           child_rec.table_name,               child_rec.constraint_name,
                child_rec.parent_constraint_catalog, child_rec.parent_constraint_schema, child_rec.parent_table_name,
                child_rec.parent_constraint_name,     child_rec.level + 1
           FROM tables_heirarchy_with             AS child_rec
           JOIN tables_heirarchy_view             AS parent_rec
             ON parent_rec.constraint_catalog  = child_rec.parent_constraint_catalog
            AND parent_rec.constraint_schema   = child_rec.parent_constraint_schema
            AND parent_rec.constraint_name     = child_rec.parent_constraint_name )
SELECT level, sort_table_catalog,         sort_table_schema,         sort_table_name,
              constraint_type,            table_name,                constraint_name,
              parent_constraint_catalog,  parent_constraint_schema,  parent_table_name
  FROM tables_heirarchy_with
 WHERE sort_table_schema = 'notereadercdi_ci'
 ORDER BY 1,2,3,4,5;


texts: Suggestion for storing a lot of simple data

texts:
I'm looking to crawl a lot of webpages (500,000,000,000) records and be able to store the linking structure for a later date.  The way I planned on laying out the database was as follows:

2 Tables

Table: Pages

ID            URL - Max Length = 2048 chars
----------    -------------------------------
1             http://www.site1.com/page.php
2             http://www.site2.com/page-abc.php
3             http://www.site3.com/page-1.php
4             http://www.site4.com/page-cd.php
5             http://www.site5.com/page-nice.php
6             http://www.site6.com/page-some.php
7             http://www.site7.com/page-hrmm.php
8             http://www.site8.com/page-stack.php
9             http://www.site9.com/page-ex.php
10            http://www.site10.com/page-dba.php

Table: Links

Page          Links
----------    -------------------------------
2             1
3             1
4             1
5             1
6             1
7             1
8             1
8             9
9             1
10            1

Basically I'll be able to see what webpages link to where recurrently/several levels deep per website.  I want to map a large network of websites and their linking patterns.

So I need to know if there is a better way of doing so, and maybe some suggestions on how to design the database structure/system.  I was planning on PostgreSQL to start with since I've used it some, but with this amount of data I'm open to anything.


texts: Table design when one data is dependant of another and the second one has repeated values

texts:
I have a products, and subproducts.

Like:

Product Identifier - Product Name - Subproduct Identifier - SubProduct Name
                 1       ProA              1                   SubProA1
                 1       ProA              2                   SubProA2
                 1       ProA              3                   SubProA3
                 2       ProB              1                   SubProB1
                 2       ProB              2                   SubProB2
                 2       ProB              3                   SubProB3
                 3       ProC              1                   SubProC1
                 3       ProC              2                   SubProC2
                 3       ProC              3                   SubProC3

As you can see, the subproduct identifier is not unique. My input is gonna be the product and the subproduct, and I need to return the names.

I did this:

  Table Products: ID, Name

  Table SubProducts: IDproduct, IDsubProduct, Name

But I know that is wrong, I wanted to make 3 tables, products, subproducts and a relation between the two, but I don't know how to design it.

If I was to save the product and subproduct into another table, I would have to save both values, that table would have to have the productID and subproductID columns, that's why I feel having a relation product subproduct table would be the best.

But I'm having a hard time doing it.


texts: How to avoid creating and dropping temp tables in functions?

texts:
I have been creating some functions lately that run into the "out of shared memory" warning in postgres. I have tried to increase the max_locks_per_transaction, but it still occurs.
As far as i understand from the answer in this post ("PostgreSQL complaining about shared memory, but shared memory seems to be OK") creating and dropping temp tables creates locks which can result in the earlier-mentioned warning.
for example:
create or replace function remove_vertices(par_geom geometry)
returns geometry as $$
DECLARE
cnt int;
BEGIN

drop table if exists invalid_pnts;
create temp table invalid_pnts as
select * from selfintersects(par_geom) ;
-- calls other function. selfintersects(par_geom) returns X rows with 4 columns/attributes,
-- where X corresponds to number of faulty vertices in a postgis geometry
-- can return 0 rows

select count(*) into cnt from invalid_pnts;
if cnt > 0
then -- do something with invalid_pnts table and the input "par_geom"

else
    return par_geom

end $$
language 'plpgsql';

Since this function is supposed to be called per row of a table (i.e. select  remove_vertices(geom) from some_table), the dropping and creation of the temp table can occur as many times as there are rows in a table.
What is an alternative to dropping/creating temp tables, if you need a "table variable" in a function?


texts: How do I separate the values of one column into multiple columns In MS SQL-Server

texts:
ID     Contact No  Ext   Type
 --     ----------  ----  ----
0001    75865558    123   work
0001    207586558   NULL  home
0001    207586559   NULL  cell
0001    746655558   321   work
0002    946655558   323   work
0002    2356841     NULL  home
0003    6655558     NULL  cell

I want to split the table up into

ID      HPhone     CPhone1     CPhone2   WPhone1   Ext1   WPhone2    Ext2
 --     ----------  ----------  --------  --------- ----   ---------  -----
0001     207586558  207586559    NULL     75865558  123    746655558  321
0002     2356841     NULL        NULL     946655558 323      NULL     NULL
0003      NULL      6655558      NULL      NULL     NULL     NULL     NULL

Any id can at most have 1 HPhone, 2CPhones, and 2 WPhones/Ext.

Im new to databases so Im not specifically looking to be given the answer but any advise about how I can split the first table into something similar to the second table would be appreciate.


texts: Users running ANSI scripts are having problems with special characers

texts:
Long story short. How can I fix users environments, to make them run our scripts using ANSI encode?

The problem is, we send them scripts to run on their databases using ANSI encode.

But some of them are returning to us with this problem:

We tried to say "please upgrade your software to run our scripts using ANSI encode" but most of them can't do this.

Is there a way for us, developers ( not me at all, I'm the DBA trying to help here ) to do something ?? WE have this problem since ever. we read all the internet and we couldnt fix this issue.

Thanks


texts:
A client ask me to migrate his MySQL DB:
The server doesn't have free space and it also has a very big table that is broken, so I can't dump it.

I can't REPAIR it because of lacking of free space.

Question : Is there a way to physically move MySQL DB data files to another server and use them for the "new" MySQL ?


texts: Person table with list of friends (=persons) field in MySQL

texts:
New to database design. What i'm trying to do is transform a java object called "Persons" into a MySQL schema.

Usual values are fields like first/last name, adresses etc. with key=numeric id. But each person also knows other persons - in java i just have a list of "Person" as a member variable, but how would i go about this in MySQL where i cannot just add a known persons field with a list of person IDs ?

Table Person:

ID  | FName  | LName  | Street | ... | Knows
int | String | String | String | ... |  ???

Do i need to create a separate table "Who knows whom" ?

 person1 | person2 | Knows
 Person  | Person  | boolean

Or is there a better way, if possible using javax.persistence with annotations on the java objects?


texts: Postgresql / split one record on two rows depending of two fields

texts:
Depending of this users table structure :

CREATE TABLE users
AS
  SELECT id,name,email1,email2
  FROM ( VALUES
    ( 1, 'John', 'john@doe.com', 'foo@bar.com' ),
    ( 2, 'Baz' , 'baz@bar.com' , null )
  ) AS t(id,name,email1,email2);

Is it possible to have this output with one query ?

John | john@doe.com
John | foo@bar.com
Baz  | baz@bar.com


texts: Failed to import a mongodb database with duplicate fields

texts:
I'm trying to import a mongodb database and I've been using the mongorestore command, the import starts without problems until it gives the error:
Failed: dbprod.mys_account: error creating collection dbprod.mys_account: error running create command: BSON field 'OperationSessionInfo.create' is a duplicate field

I already tried the option --drop but without success, I had never worked with mongodb ... I must clarify that I saved it in a mongodb 2.4 with mongodump and I try to import for a mongodb 3.6


texts:
I have many text files, their total size is about 300GB ~ 400GB. They are all in this format

key1 value_a
key1 value_b
key1 value_c
key2 value_d
key3 value_e
....

each line is composed by a key and a value. I want to create a database which can let me query all value of a key. For example, when I query key1, value_a, value_b and value_c are returned.

First of all, inserting all these files into the database is a big problem. I try to insert a few GBs size chunk to MySQL MyISAM table with LOAD DATA INFILE syntax. But it appears MySQL can't utilize multicores for inserting data. It's as slow as hell. So, I think MySQL is not a good choice here for so many records.

Also, I need to update or recreate the database periodically, weekly, or even daily if possible, therefore, insertion speed is important for me.

It's not possible for a single node to do the computing and insertion efficiently, to be efficient, I think it's better to perform the insertion in different nodes parallely.

For example,

node1 -> compute and store 0-99999.txt
node2 -> compute and store 10000-199999.txt
node3 -> compute and store 20000-299999.txt
....

So, here comes the first criteria.

Criteria 1. Fast insertion speed in distributed batch manner.

Then, as you can see in the text file example, it's better to provide multiple same key to different values. Just like key1 maps to value_a/value_b/value_c in the example.

Criteria 2. Multiple keys are allowed

Then, I will need to query keys in the database. No relational or complex join query is required, all I need is simple key/value querying. The important part is that multiple key to same value

Criteria 3. Simple and fast key value querying.

I know there are HBase/Cassandra/MongoDB/Redis.... and so on, but I'm not familiar with all of them, not sure which one fits my needs. So, the question is - what database to use? If none of them fits my needs, I even plan to build my own, but it takes efforts :/

Thanks.


texts:
In table Orders, I store the orders we receive from all shops. Since an order can have multiple lines, among the columns there are OrderID and OrderLineID
where OrderID can be duplicated but OrderLineID has to be unique within an order.

Since orders can be amended, the stored procedure first checks if the received order's OrderLineID is already present in the table, and then decides to insert or to update. To do this we:

build the insert and update statements dynamically from the XML input
insert into customers table
insert into shippingAddresses table

and then the main table:

IF NOT EXISTS (Select 1 from Orders where OrderLineID=@OrderLineID ......)
INSERT INTO Orders () VALUES ()
ELSE UPDATE Orders SET ... WHERE OrderLineID=@OrderLineID

Or does the MERGE function offer better performances/control?

But the question is the following:

Due to line issue/server busy and so on, the Order message (or the modification), could be sent more than once, and we do not know in which sequence. Therefore, to avoid the Order arriving after the amendment, and therefore overwriting the amendment, we added a time column:

IF NOT EXISTS (Select 1 from Orders where OrderLineID=@OrderLineID)
INSERT INTO Orders () VALUES ()
ELSE UPDATE Orders SET ... WHERE OrderLineID=@OrderLineID AND LastModified<@CreatedTime

In this way if the later message is older than the previous, it has no effect on the table.

But, it could happen that the message and its modification are sent twice (or more) in so little timeframe that the latter message arrives before the former has been saved. Therefore the IF NOT EXISTS (Select 1 from Orders where OrderLineID=@OrderLineID) is TRUE for both executions of the stored procedure, and both times it generates an INSERT and we find duplicated rows.

Perhaps this can be avoided by simply setting OrderLineID as unique key?

I also read something about using:

set transaction isolation level serializable

but I'm not sure how this is handled. I not only want to avoid the duplicate rows but to be sure the later message executes the UPDATE query and not throw a unique key violation.


texts:
We have a moderately sized database (about 500GB) that we're going to switch from Simple to Full Recovery, with Trans Log backups every 15 minutes. Before we make the switch, I want to get an estimate of how much space those backups will take on average. (Actually, our storage folks want to know so they can carve out that space for me.) So how can we get that estimate?

Looking at how much log space is in use won't do it, since it's just using one area in the log while freeing others. So I'm guessing I would have to look instead at the rate at which the transaction log is being used. The plan is to monitor the VLF usage and track which VLFs are used and then freed over time, along with their size.

That's going to be a little challenging to set up, but it's doable. Before I go down that route, though, I'm hoping there's a better/easier way. Any ideas?


texts: Manage billions lines of sensors data in mariaDB

texts:
I'm the new "IT guy" in a small company that managed ecological footprint reduction in industries, for contracts reasons they need to keep every data changes of each sensors(some changed every seconds some  every minutes) they put in their installation for 5 years. They are doing some calculations on these data to prove the ecological footprint reduction each year and to detect some errors/illogical things on the installation.
Currently we have one project ongoing and two other coming in next weeks.
The previous IT guy have set a MariaDB server on a VPS with this structure:
CREATE TABLE `machine` (
  `Name` varchar(100) CHARACTER SET utf8 NOT NULL,
  `Site` int(11) DEFAULT NULL,
  `Emplacement` varchar(200) DEFAULT NULL,
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Name`),
  UNIQUE KEY `ID` (`ID`),
  KEY `FK_machine_site` (`Site`),
  CONSTRAINT `FK_machine_site` FOREIGN KEY (`Site`) REFERENCES `site` (`ID`)
) ENGINE=InnoDB;

CREATE TABLE `mesure` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Machine` varchar(100) NOT NULL,
  `Date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Valeur` decimal(18,5) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `machine_timestamp` (`Machine`,`Date`) USING BTREE,
  KEY `Date` (`Date`),
  CONSTRAINT `FK_valeur_machine` FOREIGN KEY (`machine`) REFERENCES `machine` (`Name`)
) ENGINE=InnoDB;

CREATE TABLE `site` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Nom` varchar(50) NOT NULL,
  `Ville` varchar(100) DEFAULT NULL,
  `Code_Postal` varchar(100) DEFAULT NULL,
  `Rue` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB;

In about 7 months  we got about 600 millions rows inside the "mesure",80 rows inside the "machine", just one inside the "site" table and the database is about 40GB
This working quite well for the moment, data can be accessed in a good time for the monthly data extraction (I have done a script that recover and generate an excel per week with all calculations done inside per seconds).
we have decided to set a new server on a new VM per project for the moment.
To summarize the context

One database and server per project, for the moment MariaDB
About 100 sensors on each project with variable updates, about 600 millions records in 7 months for the current project, 40GB
Data cannot be reduced, normalized, and we need to keep these data available for 5 years
low interactions with the DB, only writing inside with the application reading sensors data. And extracting one time each month to recover monthly data.
Limited budget for IT, cannot affrod big servers. I'm working on 4 cores, 8GB RAM, SSD VPS, only used for the data recovery app(low ressources usage) and the DB

My questions

It seems that we're going to reach max int value for the mesure ID PK fast, do I need the set it to bigint ? was thinking about change the PK to (Machine,Date) couple is this a good idea or not ?
Do we will face any limitations in the future with this way of doing ?
Is it a good idea to stay on MariaDB or do I need to look to other DB ? was looking  for TimeScaleDB, any comments, positive/negative reviews on this ?
Any optimisations that I can do to reduce size of the DB for example ? was thinking about yearly "archive" compressed DB dumps for each project inside a unique less costly server with big HDD(these dumps will be backup locally and on a cloud drive), to reduce size of backups we're doing on mariaDB servers and only restore dump on local DB if data need to be retrieve in very rare cases. Any comments on this ?


texts: How large will a MySQL database be relative to the dump file?

texts: I'm restoring a ~52GB dump to a MySQL database. The ibdata1 file has already exceeded the size of the dump file and the restore is still incomplete. Is there any way to estimate the final size of the ibdata1 file if the size of the MySQL dump file is known?


texts: Why does dropping decimal columns not free up any space?

texts:
I have several tables, each of which had a few columns of data type Decimal. I decided to drop these columns to free up some space.
I used the ALTER TABLE DROP COLUMN col1 command for the same.
However, I can not view any space being freed up. Why should it be so, since Decimal is not a variable length column and should free up space instantly?

PS. This is the query I'm using to track the amount of space available:

SELECT
b.groupname AS 'File Group',
Name,
[Filename],
CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],
CONVERT (Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],
CONVERT (Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)],
'DBCC SHRINKFILE ('''+Name+''','+CAST(CONVERT (Decimal(15,2),ROUND((FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS VARCHAR(10))+')' QUERY
FROM dbo.SYSFILES a (NOLOCK)
JOIN SYSFILEGROUPS b (NOLOCK)
ON a.groupid = b.groupid
ORDER BY b.groupname


texts: What to do when handed a server the first time?

texts:
I'm a new SQL Server DBA in a new company. Just recently, they have handed me access to the server. There are about 50 OLTP databases with different sizes, some ranging 250MB and the biggest database is 25GB with a total size of 150GB data, 100GB logs for all 50 databases.

I would like to know what could be done if you are handed out access to the instance the first time? The action plan in my head right now is to  run a server-side trace and perfmon to see what's going on with the database

Is this a good plan to begin with running that server-side trace? I would like to get some insights on what to normally do in this case.

Also, they have this job that does full backup of all the 50 databases in one job every day.  Is that something that needs to be separated in chunks? My thought on this one is that it's probably OK to backup all the databases in one job since the size is not that big, and still can afford to do daily full backups because of the size. (Let me know if im wrong). And according to them, they can live with the server being down for 5 days. So it seems like the RTO is very flexible.

What are the necessary things that I need to know to better perform the administration of the databases?


texts: Removing records from database periodically that surpass timeline

texts: I have a reports table which has literally millions, if not hundreds of millions of report records, which date back to 2011. I think it's slowing down my queries exponentially. I only want to keep reports from the past 6 months. I have a time column in reports table which gives the current time so I can use that to measure reports older than 6 months. So I am thinking about running some type of cron job on my ubuntu server in which the postgresql server is installed on. But I am looking for some guidance about going about this.


texts: Performance impact of waiting on a service broker message

texts:
I have a situation where multiple client apps send messages via the Service Broker (utilizing stored procs).  These messages are picked up by yet another client app and then processed.  The way the messages are picked up is that the app issues the following SQL statement (pseudo code):

LOOP {
    WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM SB_ReceiveQ)
    ProcessMessage
}

So basically the code just blocks until a message is received.  This all works fine.

My question is about the implication of issuing a WAITFOR (RECEIVE... command which hangs on to some service broker based resource basically forever.  Are there any performance issues associated with this pattern that I should know about?

For reference, this is SQL Server 2005.


texts: How to improve multiple case when query performance?

texts:
I have the following query which counts when the event_time is equal to the day of the month.

I also have indexes on the id of each table, template_name, from_email_address, event_time, event_status.

Is there any more way I can optimise it?

I am running this against a database of at least 30 million records and this query will likely scan through at least 3 million records. I also have to run this query multiple times for different from_email_addresses.

Edited to include the explain query result.

select_type        tbl type   possible_keys                   key

PRIMARY            e   ref    PRIMARY, IDX_FROM_EMAIL_ADDRESS IDX_FROM_EMAIL_ADDRESS
PRIMARY            d   eq_ref PRIMARY                         PRIMARY
PRIMARY            s   ref    PRIMARY, IDX_EVENT_TIME         PRIMARY
DEPENDENT SUBQUERY ms  ref    PRIMARY, IDX_EVENT_STATUS       IDX_EVENT_STATUS
ref        rows       Extra
const      3,012,345  Using where, Using index, Using temporary, Using filesort
e.id       1
e.id,func  1          Using where; Using index
const,d.id 1          Using where; Using index

    SELECT
     d.template_name,
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 1 THEN 1 END) AS '1',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 2 THEN 1 END) AS '2',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 3 THEN 1 END) AS '3',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 4 THEN 1 END) AS '4',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 5 THEN 1 END) AS '5',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 6 THEN 1 END) AS '6',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 7 THEN 1 END) AS '7',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 8 THEN 1 END) AS '8',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 9 THEN 1 END) AS '9',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 10 THEN 1 END) AS '10',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 11 THEN 1 END) AS '11',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 12 THEN 1 END) AS '12',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 13 THEN 1 END) AS '13',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 14 THEN 1 END) AS '14',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 15 THEN 1 END) AS '15',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 16 THEN 1 END) AS '16',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 17 THEN 1 END) AS '17',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 18 THEN 1 END) AS '18',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 19 THEN 1 END) AS '19',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 20 THEN 1 END) AS '20',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 21 THEN 1 END) AS '21',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 22 THEN 1 END) AS '22',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 23 THEN 1 END) AS '23',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 24 THEN 1 END) AS '24',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 25 THEN 1 END) AS '25',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 26 THEN 1 END) AS '26',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 27 THEN 1 END) AS '27',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 28 THEN 1 END) AS '28',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 29 THEN 1 END) AS '29',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 30 THEN 1 END) AS '30',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 31 THEN 1 END) AS '31',
     COUNT(*) AS 'Grand Total'
FROM
  detail d
LEFT JOIN
    email e on d.id = e.id
LEFT JOIN
    status s on s.id = d.id
 AND s.event_time = (
                        SELECT
                          MINE(ms.event_time)
                        FROM
                          status ms
                        WEHRE
                          ms.id = d.id
                        AND
                            ms.event_status = 'SENT'
                    )
WHERE
    CASET(s.event_time AS DATE) BETWEEN '2020-03-01' AND '2020-03-31'
 AND
    e.from_email_address = 'abc@google.com'
GROUP BY
  d.template_name


texts: ERROR 1524 (HY000): Plugin ' ' is not loaded fix?

texts:
I messed up when trying to fix an error in the webapp that I'm trying to develop. The previous error was

  ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I followed this fix

UPDATE user SET plugin='mysql_native_password' WHERE User='root';

but foolishly entered my password in the mysql_native_password part.

Now I think that my LAMP server is destroyed. Everytime I enter

mysql -u root -p

the error from the ERROR 1524 appears even when I put sudo.


texts: PostgreSQL GiST compress skeleton when no compression required

texts:
I'm trying to implement my GiST index, where storage type is the same as column type (bytea). They are even going to have same length because these are bitarrays and unions are just disjunctions of all arrays in set (in other words, bitwise OR of all arrays).

Because of that, I don't need to convert or compress data. In that case, is this a correct implementation of compress function?

Datum
sub_fp_compress(PG_FUNCTION_ARGS)
{
    GISTENTRY  *entry = (GISTENTRY *)PG_GETARG_POINTER(0);
    // Column and Storage data types are the same (bytea)
    // so no compression is required.
    GISTENTRY  *retval = entry;

    PG_RETURN_POINTER(retval);
}

The reason why I'm asking is that when creating index I get some unexpected values and first occurrence of this is in compress function. To be more precise, all my bitarrays stored in DB are 256 bytes long (checked) and the GiST nodes I create have same length. But all of sudden I get in penalty function as second newentry param a bitarray that is 9 bytes long. I have traced this to compress function where it first appears so it's first place I need to check.


texts: Strange blocking on readable secondary after reboot

texts:
We are running an SQL Server 2014 Availability Group with 3 replicas, one synchronous (SQL2 for this matter) and one asynchronous secondary replica. We also configured read-only routing to the synchronous secondary replica.

Last night SQL2 rebooted from automatic windows update installation. The server went back online, SQL Server service started (delayed start) and the database went into recovery. After a while the event viewer showed the database integrity check succeeded and the database was ready for use.

The database showed synchronized state in SQL Management studio. The AG state was healthy, but no queries were getting results from the database.

The queries were blocked by the wait type: HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING.

Sometimes the wait type changed to ‘lck_m_s’ wait type and the blocked by a pid that was a process performing a DB Startup command. I know this has to do with the Fast Recovery option that comes with SQL Server Enterprise, but I don’t understand why a simple select was blocked forever.

The main question is: How can SQL Server show the AG database is healthy but actually it isn’t? Do you recognize this problem?

To fix this, we removed the secondary from the AG and joined the database back again to the AG and now everything is working again.


texts: High SOS_SCHEDULER_YIELD and Signal Waits

texts:
I have run Paul Randals wait script and can see SOS_SCHEDULER_YIELD is at 60% with an average signal wait of 10ms.  I have also run a script to find out the what percentage of my waits are signal waits and in total it is 47%.

I can seen in task manager that the CPU usage is high and I believe I do have a CPU issue, but how do I find which queries are the ones causing the signal waits?

Just to update:  The version of SQL is 2008R2


texts: Should I create two endpoints for two availability groups on the same SQL Instance?

texts:
I have a two node cluster running SQL Server 2012. I want to create two Availability groups within the same instance of SQL Server.

Should I add one endpoint for each AG, or shall I allow both AG's to use the same endpoint?

I have read the documentation here but the only part pertaining to multiple endpoints relates to multiple SQL instances

  Note that if more than one server instance on a given computer requires a database mirroring endpoint, specify a different port
  number for each endpoint.


texts:
I have two tables, proj: id name and proj_reports: id, position, created_at, key_id, proj_id and
I want to select from key_reports for each proj_id:

- total number of `key_id` which can be done with `count(.key_id)`
- total number of DISTINCT `key_id` `count(DISTINCT .key_id)`
- total number of `key_id` where `position` > 1
- total number of `key_id` where `position` > 2 AND < 5

The problem that I have is that I don't know how to add those conditions for checking if the position is > or < a number or in a range.


texts: Is it Important to Understand Each Normal Form?

texts:
I have been studying database design and programming for quite some time now, but I still can't get a grasp of understanding each individual normal form (that is, 1NF, 2NF and 3NF).

Seeing as anytime the data is in Third Normal Form, it is already automatically in Second and First Normal Form, can the whole process actually be accomplished less tediously by fully normalizing the data from the start. I can accomplish this easily by arranging the data so that the columns in each table, other than the primary key, are dependent only on the whole primary key.

Questions

How important is it to understand each individual normal form if we can simply fully normalize the data less tediously by doing what I have described?

What I'm ultimately asking is: Is it important to go through the steps of each normal form when normalizing data, or is it appropriate to just go to Third Normal Form seeing as the result is ultimately the same?


texts:
I'm seeing thousands of rows in our error log about failed logins:

  Error: 18456, Severity: 14, State: 38.
  Login failed for user 'Ourcompany\myaccount'.
  Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 10.nn.nn.nn]

Some of our users do not have access to all databases on the server.
When I switch off Intellisense in SSMS -> Tools -> Options -> Text editor -> Transact-SQL -> IntelliSense, the spamming stops.

Is this a bug in SSMS?
Are there ways around it (apart from granting access to all databases)?


texts: I cannot get SQL Server 2012 to install the database engine

texts:
SQL Server 2012 installation on a standard Windows 2008 R2 server. It refuses to install the database engine or sql replication.

During the installation I got no errors saying that it wasn't installing. So what I did was I ran a repair on the installation and it still stated it would not install. I'm afraid to uninstall and reinstall due to DTC being installed and I don't really want this to boil down to me doing a full 2008 reinstall.

Here's a screenshot of the error I'm getting with this after attempting a repair :

Any suggestions would be greatly appreciated I've been banging my head on this for the past few days.

Also any suggestions on doing an uninstall/reinstall if need be would be great. I'm under the impression that it will cause issues though.


texts: Query that was working on MySQL is not working on PostgreSQL

texts:
I had the following query (with bin_number being a string) which was working on MySQL 5.5:

Postgres - was working on MySQL and doesn't work on PostgreSQL

SELECT "menu_items".*
FROM "menu_items"
WHERE ((header like '%red%'
         or detail like '%red%'
         or bin_number like '%red%')
  and location_id='37'
  and is_deleted=false and bin_number<>'')
ORDER BY bin_number asc;
> 0 results   # should be 17

Postgres - works so issues with and bin_number<>''

SELECT "menu_items".*
FROM "menu_items"
WHERE ((header like '%red%'
        or detail like '%red%'
        or bin_number like '%red%')
and location_id='37'
and is_deleted=false)
ORDER BY bin_number asc;
> 17 results

I tried changing bin_number<>'' to bin_number!='' but that didn't make a difference.
Why is this not working?

edit 1

The bin_number in menu_items is declared as:

  bin_number | character varying(255) |  | extended |   |


texts: Lost data in bulk Insert on Galera Cluster

texts:
I have a 2-node fresh Galera Cluster in 2 DCs. I Import a 3GB dump into one node and the other starts to sync. After importing dump, I check the size and rows on both nodes and they are not equal. This is my galera.cnf ( Customized for WAN env )

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="my-company"
wsrep_cluster_address="gcomm://XXXX,YYYY"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="XXXX"
wsrep_node_name="Node-1"

# Galera Custome setting for WAN
wsrep_provider_options="evs.suspect_timeout=PT1M"
wsrep_provider_options="evs.inactive_timeout=PT1M"
wsrep_provider_options="evs.send_window=512"
wsrep_provider_options="evs.user_send_window=512"
wsrep_provider_options="evs.inactive_check_period=PT30S"
wsrep_provider_options="gcs.fc_factor=1"
wsrep_provider_options="gcs.fc_limit=260"
wsrep_provider_options="gcs.max_packet_size=2097152"

This is Databases size after import dump:

Node1: 27329.4 MB
Node2: 17570.1 MB

This is bandwidth usage on the node that I imported dump:

Why many record did't insert? It's about mysql tuning or something else?

Ubuntu 18.0.4
mysql-wsrep-server-5.7
8 Core cpu, 16GB Ram

I hope someone can help, Thanks a lot.


texts: Subselect dependent on first select value

texts:
I wish to do a select of all data matching a particular id.  I would also like to return a selection of all data matching the alt_id field in the first select.

This example works fine for the main query of two tables and their data based on the contact_id I feed it, in this case '1':

SELECT e_contacts.*, e_credentials.*
FROM e_contacts, e_credentials,
WHERE e_contacts.contact_id = '1' AND
e_credentials.contact_id = e_contacts.contact_id
ORDER BY e_contacts.contact_id

However, one of the fields in e_contacts is called alt_contact_id (wife or friend) which when holds a value, requires I get both contacts data.

So, I would like to have a second row of data returned for that alt_contact_id's data which exists in the same table.  In other words we'll be requerying the same select statement but feeding it e_contacts.contact_id = alt_contact_id found in the first select (if alt_contact_id has a value of course).

This is the table structure:

I'm completely uncertain how to accomplish it.


texts: How to get count of an object, through 3 different tables in postgres with ID's stored in each table

texts:
I'm currently using Postgres 9.6.16.
I am currently using 3 different tables to store a hypothetical users details.
The first table, called contact, this contains:
ID, Preferred_Contact_Method

The second table, called orders, This contains:
ID, UserID, Contact_ID (the id of a row, in the contact table that relates to this order)

The Third Table, Called order_details
ID, Orders_ID (the id in the orders table that relates to this order details)

The tables contain other data as well, but for minimal reproduction, these are the columns that are relevant to this question.
I am trying to return some data so that i can generate a graph, in this hypothetical store, There's only three ways we can contact a user: Email, SMS, or Physical Mail.
The graph is supposed to be 3 numbers, how many mails, emails, and SMS we've sent to the user; since in this hypothetical store whenever you purchase something you get notified of the successful shipment, these methods are 1:1 to the order_details, so if there's 10 order_detail rows for the same user, then we sent 10 tracking numbers, and since there can be multiple order_details (each item has a different row in order_details) in an order, we can get the count by counting the total rows of order details belonging to a single user/contact, then attributing to what kind of contact method that user preferred at the time of making that order.
To represent this better: If a new user makes a new order, and orders 1 apple, 1 banana, and 1 orange. For the apple, the user set preferred tracking number delivery as SMS, for the banana, they set it to EMAIL, for the orange, they thought it would be funny to set the tracking number delivery via MAIL. Now, i want to generate a graph to this users preferred delivery method. So i'd like to query all those rows and obtain:
SMS, 1
EMAIL, 1
MAIL, 1

Here's a SQL Fiddle link with the schema and test data: http://sqlfiddle.com/#!17/eb8c0
the response with the above dataset should look like this:
method | count
SMS,     4
EMAIL,   4
MAIL,    4


texts:
I have a table A that got a field called id that is the primary key of this table. I also have a table called B which also have a field called id as primary key.

Now I want to get all rows from Table A where the id value is not present as value in any table B id field-value.

My first query looked like this:

SELECT a.id FROM a WHERE a.id NOT IN (SELECT DISTINCT b.id FROM b)

Then I build a query that looked like this to improve the speed:

SELECT a.id FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL

Now I got 600k rows in table A and 400k rows in table B and things are getting very slow. Is there any better query to run for this kind of operation or is there perhaps a better way to solve the problem at all? Any hints or pointers?


texts: Permission hierarchy vs. WITH GRANT OPTION

texts:
I have an Azure SQL database with this security setup:

SchemaUpdater user with db_ddladmin, db_datawriter, and db_datareader roles. The user is used to execute database change scripts during application deployment.
App user with db_datawriter, and db_datareader roles. The user is used by the application to work with the data.

I wanted the app user to also execute a stored procedure, say dbo.sp_MyProc. I did following:

grant execute to [SchemaUpdater] with grant option using an admin account, so that change scripts can grant permissions for any SPs.
Created the stored procedure in a change script.
Tried to grant execute on [dbo].[sp_MyProc] to [App] in the same change script. That didn't work.

The step failed with

Cannot find the object 'sp_MyProc', because it does not exist or you do not have permission.

What's interesting, that if the SchemaUpdater does grant execute to [App] instead, with no individual SP mentioned, it works.

My questions are:

Why can't SchemaUpdater grant a permission on a single SP, but can grant it on all SPs?
Is SchemaUpdater missing some other permission, so that it could grant permissions to individual SPs?


texts: Is this right decision to define 5-6 foreign key in a single table...?

texts:
If i define 5-6 foreign keys on a single table, are there any performance concerns?

CREATE TABLE [dbo].[PS_CLASSROOM_MST](
    [CLASS_ID] [int] NOT NULL,
    [CLASS_NAME] [varchar](50) NOT NULL,
    [CLIENT_ID] [int] NOT NULL,
    [SCHOOL_ID] [int] NOT NULL,
    [COURSE_ID] [int] NULL,
    [BRANCH_ID] [int] NOT NULL,
    [SESSION_ID] [int] NOT NULL,
    [SECTION] [varchar](10) NULL,
    [STATUS] [bit] NOT NULL,
    [IP_ADDRESS] [varchar](30) NULL,
    [LANGUAGE] [varchar](50) NULL,
    [CLASS_FILE_PATH] [varchar](200) NULL,
    [REMARK] [nvarchar](max) NULL,
    [SECTION_FILE_PATH] [nvarchar](max) NULL,
    [LOCATION_ID] [int] NOT NULL,
 CONSTRAINT [PK_PS_CLASSROOM_MST] PRIMARY KEY CLUSTERED
(
    [CLASS_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST]  WITH CHECK ADD  CONSTRAINT [FK_PS_CLASSROOM_MST_PS_BRANCH_MST] FOREIGN KEY([BRANCH_ID])
REFERENCES [dbo].[PS_BRANCH_MST] ([BRANCH_ID])

GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST] CHECK CONSTRAINT [FK_PS_CLASSROOM_MST_PS_BRANCH_MST]
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST]  WITH CHECK ADD  CONSTRAINT [FK_PS_CLASSROOM_MST_PS_COURSE_MST] FOREIGN KEY([COURSE_ID])
REFERENCES [dbo].[PS_COURSE_MST] ([COURSE_ID])
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST] CHECK CONSTRAINT [FK_PS_CLASSROOM_MST_PS_COURSE_MST]
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST]  WITH CHECK ADD  CONSTRAINT [FK_PS_CLASSROOM_MST_PS_LOCATION] FOREIGN KEY([LOCATION_ID])
REFERENCES [dbo].[PS_LOCATION] ([LOCATION_ID])
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST] CHECK CONSTRAINT [FK_PS_CLASSROOM_MST_PS_LOCATION]
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST]  WITH CHECK ADD  CONSTRAINT [FK_PS_CLASSROOM_MST_PS_SCHOOL_MST] FOREIGN KEY([SCHOOL_ID])
REFERENCES [dbo].[PS_SCHOOL_MST] ([SCHOOL_ID])
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST] CHECK CONSTRAINT [FK_PS_CLASSROOM_MST_PS_SCHOOL_MST]
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST]  WITH CHECK ADD  CONSTRAINT [FK_PS_CLASSROOM_MST_PS_SESSION_MST1] FOREIGN KEY([SESSION_ID])
REFERENCES [dbo].[PS_SESSION_MST] ([SESSION_ID])
GO

ALTER TABLE [dbo].[PS_CLASSROOM_MST] CHECK CONSTRAINT [FK_PS_CLASSROOM_MST_PS_SESSION_MST1]
GO


texts:
How can I check for a numeric column for empty values

I want something like this:

select * from table where column_numeric = ''

This does not works obviously, but what can be the equivalent of this for numeric column?


texts: Run an exe file that exists on a different server from a SQL Server Job

texts:
We have an .exe file on a server, say Server1 which should be run from a SQL Server job that exists on a different server, e.g.: Server2. How can it be done?

I know if it's a local file, I can use xp_cmdshell in the Job step and run the .exe file, but in our case, this file exists on a different server. If it's doable, what security permissions should be set up to achieve it?


texts:
Possible Duplicate:
  Real Time SQL Server Analysis Services Cube Processing Report

I would like to know if there is a way to generate a report to determine which SQL Server Analysis Services cubes are processing using SQL Server Reporting Services? I would like to be able to run this report without affecting the cube process. The reason I would like to know this information is because I would like to setup a notification for the users to let them know the cube is processing can reports cannot be ran at the time of the processing.


texts:
I am new to DB Admin in SQL Server.
I am more of a SQL Developer.
I am trying to restore 95GB size of BAK file to SQL Server (in Azure VM).
The issue is that it is taking more than 24 hours now..
I realized that since I have multiple more BAK files to restore after this one, I have to restore the main BAK file (95GB one) with "NoRecovery".
So, I went into a folder where 95GB Bak file is located, and selected the Recovery state as "Restore with Norecovery".
Then, I just started the process since yesterday, and it is taking more than 24 hours.
What am I doing wrong?  Or is there anything else I need to do?

As far as % complete is concerned, it was completed (with 100%) after probably 2 hours from when the process was started. But it just shows as "Restoring..." after 24 hours, and I am not able to do any query or access.
Regards to other bak files, those should be like an hourly transaction log backup. Since the original data provider only provides in SQB files format, I have to convert them into BAK file using a third party app (RedGate's SQL Backup).


texts:
We have a control table called Generators which has a NextID and varchar label. I found some bad code in a stored procedure which assigns a value w/o a where clause - something like below.

declare @NEXTID int
select top 1 @NEXTID = nextid from generators order by genname desc
select @NEXTID  -- result is 511 which is the last value in the table

select @NEXTID += nextid from generators
select @NEXTID  -- result is a summation of all fields/rows

select @NEXTID = nextid from generators
select @NEXTID  -- result is 511 which is the last value in the table

select * from generators where nextid = 511
select * from generators  -- confirms that this is the last row

It is clear that the @NEXTID gets set to the last item in the clustered index.  What isn't clear to me is, "what exactly did SQL server just do?"  Profiler doesn't show me much, but what I want to know is, did @NEXTID get set for every row and then get overwritten or did the compiler realize what was happening and went right to the LAST value?  Basically this:

select top 1 @NEXTID = nextid from generators order by genname desc

PS: I didn't create the data model.

EDIT

This confirms that SQL Server overwrites the variable (proving Randolph West's answer).

vs


texts: Changing db ownership using a user which can only see the sa

texts:
At first, I would like to thank the community for creating such a resourceful forum. I've been using StackOverflow for years, and recently, using dba on SE too and you're great. Most of my questions have been asked for someone, or some comment have led me to an answer, so thank you very much!

After that being said, I have a problem which I don't think it's usual. The case is: I have been asked to create one database which will be a central db for a new system. This db will have the info that the company I work for need (like payment info, contacts, client's emails and stuff).

After that, each new customer will be given a clean db to work (the one they will use with our software). This db should have only one user, and the user can only see and connect to that specific database. This db should be partially contained and the user can't have any link to any existing instance login.
Also, for some reasons, the process of creating a db should be done over a procedure, to be triggered by some event, after the new client finishes his registration on our website. And the user that run this procedure (which I'll be calling "CREATOR") must not have any rights besides "db creation".

These steps I already covered, creating a user which only have permission to run the procedure and to create a database.

The problem is that this CREATOR user is the db_owner of the databases it creates. I would like to change the ownership to the sa (or any other login, really), but I can't because the CREATOR can't see anyone besides itself and the sa. I know that this happened because I didn't allow it to see other logins.

Then, it comes the question. How can I change the ownership of all created dbs to sa (the one I can see) without breaking those rules or how can I grant CREATOR the permission to see (and not to drop or create) other users and change the db owner of each db he creates?

Thanks in Advance!


texts: I am trying to run a script against a database that is dropped from a third party daily so the name of the database changes daily.  I can get the name of the new database using the sys.databases folder but I am trying to populate it into an SQL statement.  I am hoping to get this to run and move it into SSRS.  Thank you in advance.


texts:
This is status on Main node. it's show some seqno as 17210843

when new empty node is join. i'm found rsync process is ok. but i see difference seqno in linux process

after finish this process my new node cannot join
and grastat.dat is zero uuid and service mysql on new node fail to start

i'm try to re-step again by manual edit
uuid to same Main node
seqno :-1
saafe_to_bootstrap : 0
and restart service mysql in new node
after new node try to sync.
service mysql on new node is fail again like this step (seqno dose not same between main node and new node)

how can i do with this problem


texts: Is there a restriction which prevents you from creating a user and a role with the same name?

texts:
I have a (SS2005) database with a user named MY_APPLICATION_USER. I'm trying to create a role in that database named MY_APPLICATION_USER.

When I try to do that using this script:

/****** Object:  Role [MY_APPLICATION_USER] ******/
CREATE ROLE [MY_APPLICATION_USER] AUTHORIZATION [dbo]
GO

I get the error message:

Msg 15023, Level 16, State 1, Line 2
User, group, or role 'MY_APPLICATION_USER' already exists in the current database.

Does SQL Server have a restriction where it is not allowed to have a user and a role with the same name? I googled various ways of asking that question and did not find anything which addresses this, but it's certainly possible that I missed something.


texts: What would be the rule for parsing parameterized sql for Netezza to be used with nzsql?

texts:
I have a bunch of legacy parametrized SQL templates that were used by nzsql utility for Netezza. See examples here: http://queforum.com/netezza/347918-parameterized-sql-file-input-nzsql.html

What is the algorithm that nzsql command line utility is using for parsing parameters?

Background: I am writing a SQL generation utility in python and I need to be able to pass the parameters to my legacy parametrized SQL templates and get pure SQL back. For that, I need to mimic the exact rule for what nzsql considers a parameter.

Part of an answer from the forum link above:

You have to specify a colon before the variable name. An example is shown below:

select * from :TABLENAME

I need to figure out where the parameter name ends. I think it can happen at word bounds, but also a period forces the end of a parameter name, i.e.

select * from :DBNAME..TABLENAME

I would love to know the exact rules, but so far have not found any documentation.


texts: CPU cost in SQL Availability Group for synchronous and asynchronous DB's

texts:
We are looking at setting up a new SQL Always On Availability Group environment for SQL Server.
We are wondering if there's a difference in CPU usage of a synchronous or an asynchronous node?

Also one of my colleagues is saying there's a limitation in the number of Listeners (and so also AG's since it's adviced to have a listener per AG) but i'm not able to find anything that says that you shouldn't create more than 10 listeners in an environment. The only thing I'm able to find is that there's an AD-setting that you have to change to be able to have more then 10 listeners.

Thanks in advance!


texts:
I have a question how to extract a substring.

Here an example string:

{{from,005021889988,,Amt},{setup-from,66,,},{alert-to,66,ConTeach Zentrale,ConTeach Zentrale},{conn-to,66,ConTeach Zentrale,ConTeach Zentrale},{transfer-to,10,kb,Kai Büsing},{conn-to,66,ConTeach Zentrale,ConTeach Zentrale {disc-to,,,},{rel-from,66,ConTeach Zentrale,ConTeach Zentrale}}

The goal is to extract '66,ConTeach Zentrale,ConTeach Zentrale' between '{conn-to,' and '},'

Any ideas?


texts: SQL Server 2012 Service Master Key Regenerate

texts:
I have a new SQL Server 2012 instance and when I run the following command I get an error.

ALTER SERVICE MASTER KEY REGENERATE

Error:

  Msg 15329, Level 16, State 1, Line 1
  The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.

When I try with the Force option I get:

  The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.

Error:

  Msg 33096, Level 16, State 1, Line 1
  A generic failure occurred during Service Master Key encryption or decryption.

Any ideas?


texts: Which databases support parallel processing across multiple servers?

texts:
I need a database engine that can utilize multiple servers for processing a single SQL query in parallel. So far I know that this is possible with the some engines, though none of them are feasible for me either because of pricing or missing features. The engines currently known to me are:

MS SQL (enterprise)
DB2 (enterprise)
Oracle (enterprise)
GridSQL
Greenplum

Which other engines have this feature? Do you have any experience with using this feature?
Edit: I have now proposed a method for creating one myself. Any input is welcome.
Edit2: I have found another one: Informix Extended Parallel Server
Edit3: Stado is a new one.
Edit4: Maybe also pgpool-II, they write:

Parallel Query
Using the parallel query function, data can be divided among the multiple servers, so that a query can be executed on all the servers concurrently to reduce the overall execution time.


texts:
I have a mysql database stored on a server via phpmyadmin. I want to link this to MS Access for other users. I often link mysql databases to MS Access which are stored locally, but never DB's stored on a server.

Is it possible to do a ODBC connection to a remote database through MS Access?


texts: MySQL select * from table without conditions does not return rows right away

texts:
I have a table with about 4 million rows and a bunch of columns on each row and I'm doing a simple select * from table in the mysql client and it doesn't return data right away. A count(*) of the same table returns a count in 3 seconds.  I'm using the windows command line mysql client mysql.exe, running on AWS.  I'm running a simple select * from the table created by the create table as select.  No where clause conditions, no indexes, just a flat table with 4 million rows and a bunch of columns in a row. It seems like some sort of processing of the output is happening before it shows the first row. I'm new to MySQL so I'm trying to understand the behavior.

mysql> select count(*) from test;
--------------
select count(*) from test
--------------

+----------+
| count(*) |
+----------+
|  4074384 |
+----------+
1 row in set (2.96 sec)

mysql> select * from test;
--------------
select * from test
--------------

Does anyone know how to get the output to start streaming right away?

Edited version of create table as select:

create table test as
select
pimxref.ap_ref,
...
        AND ipm.product_status = 'A'
--------------

Query OK, 4074384 rows affected (2 min 35.67 sec)
Records: 4074384  Duplicates: 0  Warnings: 0

Python script that runs the same query returns results instantly:

import mysql.connector
cnx = mysql.connector.connect(user='x', password='x',
                              host='x',
                              database='x')

cursor = cnx.cursor()

cursor.execute("select * from test")

for row in cursor:
    print(row)

cursor.close()
cnx.close()

If someone knows an option for the Windows mysql client to make it behave in the same way it would be great.

p.s. This is the version I am running:
Server version: 5.7.17-log MySQL Community Server (GPL)


texts: Postgres query to return JSON object keys as array

texts:
Is it possible to return a JSON object keys as an array of values in PostgreSQL?

In JavaScript, this would simply be Object.keys(obj), which returns an array of strings.

For example, if I have a table like this:

tbl_items
---------
id bigserial NOT NULL
obj json NOT NULL

And if there's a row like this:

id      obj
-----   -------------------------
123     '{"foo":1,"bar":2}'

How can I have a query to return:

id      keys
-----   ------------------
123     '{"foo","bar"}'


texts: Set up AlwaysOn in virtual environment: How to add nodes to domain?

texts:
On a beefy server, I have a few Virtual Machines (VMs) running. On one of those, I have Domain Controller (DC) with a domain name (ex:SQL.AD.COM) running successfully. Now, I need to add the other VMs into this domain. How do I do that?

For AlwaysOn, all nodes must in a domain. So, for that to happen, all nodes need to be added to the domain created by the DC. When I go to My Computer >> Properties >> Computer Name >> Change Settings >> Computer Name (tab) >> Change >>

I can change the computer name, but could not add the VM to the domain. What is missing?

This is the error I get:

I have the following network properties for each VM.

Domain Controller has 1 NIC

First:

Name: AD
Type: Host-Only Adapter
Static IP Address (of VM): 200.201.202.1

What IP address settings do I need to configure for VM's to be able to join the domain? I have 3 nodes (VMs)

After @Phil comments, I removed the 200.201.xx.xx IP and added 192.168.1.x IP address pattern.

Thanks,
    _UB


texts: Excel Data Connection to SQL Server using Windows Auth from untrusted machine

texts:
We have an Excel spreadsheet that uses a Data Connection to a SQL Server database. The database server is joined to our domain, and Windows Authentication is enabled.

Here is a sample of the connection string (with revealing info obfuscated):

Description=DataConnection;DRIVER=SQLServer;
SERVER=10.x.y.z;UID=domain_user;Trusted_Connection=Yes;
APP=Microsoft Office 2010;DATABASE=DatabaseName

And the Authentication Settings are set to "Windows Authentication"

This works perfectly fine from a domain-joined / trusted machine.  It doesn't even ask for credentials; it just works flawlessly when I hit Refresh.

It will work from a non-domain / un-trusted workstation, if I use SQL Authentication.  But we are trying to move away from that.

Likewise, it will from a non-domain machine using Windows Authentication if I use "runas" to launch Excel with domain credentials, as follows:

runas /netonly /user:domain\user %path-to-excel%\excel.exe

However, using "runas" is a kludge, and our employees will almost certainly have issues... even if I were to create batch files to help automate it.

When launching Excel normally (without using "runas") from a non-domain machine, it gives this error when I hit Refresh:

Upon hitting OK, I have the option to un-check "Use Trusted Connection" and manually enter a user/password.  I put in a valid user that would be able to connect fine from a domain-joined machine, or that worked fine using "runas".  This is the error it gives with that:

At this point, I have not a clue what to do to get Excel to successfully connect with domain credentials from a non-domain machine.

Ideas?


texts: Split field values using specific character

texts:
Please help me with this.

It is possible to split field values using a specific character?
Here is my sample table

value
10uF
2K
1.0uF
200UF

I want it to split by this:

value       capacitance/resistance
 10            uF
 2             K
 1.0           uF
 200           UF

PS: I am using Oracle and the numbers should be separated from letters


texts: Extended event to track the logins from a specific login, but that login is filtering on a an active directory group

texts:
I'm wondering if there is a way to do this in sql server. I have an extended event set up to track all user logins from a database with a filter. The way our security is setup, we have active directly groups that we give the access to, and when users login, they are authenticated with their individual active directory account based on belong to the AD group that is granted database permissions.
However, the extended event information only shows that specific user, not the AD group they are authenticating to. For example, if I want to only track logins from DBAs, we have an AD group Company\DBA, and when I login as a member, I authenticate with my Company\User_ID account. However, in the extended event, I only see filters for the specific user, it doesn't display the Company\DBA AD group I'm authenticating with or give me the option to filter from it. Anyone know how to show and filter off of that AD group instead?


texts: Passing Arguments from INSERT / UPDATE to Trigger

texts:
I need a help with a trigger, this trigger I need to pass a value that comes from the queue column in the asterisk.queue table.

CREATE TRIGGER gerarconf
AFTER INSERT OR UPDATE
ON asterisk.fila
FOR EACH ROW
EXECUTE PROCEDURE asterisk.registra_gerar_conf_fila('FILA','XXXXX');

In place of XXXX I need to pass the value of the queue column that came from the insert or update of the asterisk.queue table


texts: pg_dump. Some COPY processes stuck on 'idle in transaction' after pg_dump completion

texts:
We do a dump of huge database (~ 2 TB) via pg_dump command:
pg_dump -h 192.168.0.132 -d vodka_factory -U vodka_user -F d -j 10 -v -f /backups/vodka_factory_2021-03-26-23-56.dump/

pg_dump process successfully (return code 0, no errors), but on database we can see ~8 COPY processes and one "select" in 'idle in transaction' which never finish:

No locks. More detailed information for some processes:
postgres=# select * from pg_stat_activity where pid=358683;
-[ RECORD 1 ]----+---------------------------------------------------------------
datid            | 16404
pid              | 358683
application_name | pg_dump
client_addr      | 10.136.131.164
client_hostname  |
client_port      | 57732
backend_start    | 2021-03-26 23:56:43.311535+03
xact_start       | 2021-03-26 23:56:43.321329+03
query_start      | 2021-03-26 23:57:07.682549+03
state_change     | 2021-03-26 23:57:07.683027+03
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      |
backend_xmin     | 3802998627
query            | SELECT last_value, is_called FROM public.waterfall_task_id_seq
backend_type     | client backend

postgres=# select * from pg_stat_activity where pid=358919;
-[ RECORD 1
datid            | 16404
pid              | 358919
application_name | pg_dump
client_addr      | 10.136.131.164
client_hostname  |
client_port      | 57744
backend_start    | 2021-03-26 23:57:07.836322+03
xact_start       | 2021-03-26 23:57:07.844714+03
query_start      | 2021-03-27 00:29:19.240355+03
state_change     | 2021-03-27 00:30:10.458445+03
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      |
backend_xmin     | 3802998627
query            | COPY public.slr_child_care (id, benefit_type_id, child_count, child_index, start_month, region_index, ent_id, created, modified, external_id, begin, "end", candidate_id, hospital_json, calcs_json, years_replace, first_year_replace, second_year_replace, excluded_days, old_end, compensation, alg_code, state, correction_id, ref_number, date_of_issue, regional_factor, share_by) TO stdout;
backend_type     | client backend

Databse version: PostgreSQL 11.6 on x86_64-pc-linux-gnu
pg_dump version: pg_dump (PostgreSQL) 11.8
Could you please advice how we can investigate the issue? The iisue happens every time...


texts: How to get to know that db files are inaccessible?

texts:
Let me explain my case.

I have a mount with database files on my local PC. I attach the database (from this mount). And then close this mount (in other word i attach db and then delete db files). So I have the database without files. And any ALTER query gives me something like this:

Database 'TestTableDatabase2005' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Basically I need to detach a database but in this situation this database can only be deleted(or repaired but it is not my case). Dropping database works perfectly:

DROP DATABASE [TestTableDatabase2005]

But my goal is get to know which state does this database have. And here I have problem. Query:

SELECT state
FROM sys.databases
WHERE name = N'TestDB'

Gives me ONLINE (it's not true). But as soon as I execute for example this script(of course it finishes with error):

ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Msg 5120, Level 16, State 101, Line 10
Unable to open the physical file "C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
File activation failure. The physical file name "C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB_log.ldf" may be incorrect.
File activation failure. The physical file name "C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB_log-2.ldf" may be incorrect.
Msg 5181, Level 16, State 5, Line 10
Could not restart database "TestDB". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 10
ALTER DATABASE statement failed.

the state of my 'TestDB' will be changed on RECOVERY_PENDING.

Do you have any idea how I can check are these db files inaccessible or not?
Or may be i have an opportunity to refresh sql info about this db ?

P.S.
Following query says that files have actual size.

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = N'TestDB'

DatabaseName Logical_Name Physical_Name                                                     SizeMB
------------ ------------ ----------------------------------------------------------------- -------
TestDB       TestDB       C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB.mdf          3
TestDB       TestDB_log   C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB_log.ldf      3
TestDB       TestDB-2_log C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB_log-2.ldf    3


texts: Summarize from unlimited level depth parent-child in mysql

texts:
I have 2 table master coa and coa transaction in MySQL like this,

master :

 id | coa  | id_parent |
----+------+-----------+
  1 | 1    |  NULL     |
  2 | 11   |   1       |
  3 | 111  |   2       |
  4 | 12   |   1       |
  5 | 121  |   4       |
  6 | 122  |   4       |

transaction :

 id | debit | credit |
----+-------+--------+
  3 |     0 |      5 |
  3 |     0 |     20 |
  5 |    10 |      0 |
  5 |    15 |      0 |
  5 |     5 |      0 |
  6 |     0 |      5 |

The depth level child-parent is unlimited. What i want to get is view like this,

 id | coa  | debit | credit |
----+------+-------+--------+
  1 | 1    |    20 |     30 |
  2 | 11   |     0 |     25 |
  3 | 111  |     0 |     25 |
  4 | 12   |    20 |      5 |
  5 | 121  |    20 |      0 |
  6 | 122  |     0 |      5 |

How can i accomplish this using SELECT ? so far i only can get debit credit only the lowest depth using GROUP SUM and join. Any suggestion?


texts: Bad query plan only on first execution, not in SSMS

texts:
We have a very customizable report query system where the user can pick and chose which columns to include in the results as well as a lot of filters.

I'll be mapping what we're doing onto an example orders system.  Customers, orders, line items of orders and the like.  For purposes of the example problem also assume we've got a very free-form chunk of customer information like "wife's name", "anniversary", "names of pets", etc (stuff you might want around to personalize interactions with the customer)

To avoid massive denormalization of the order details in our orders report (when they select that output), we're populating a DataSet with 2 queries - the first query is for the orders that meet the filter criteria; the second is for the order details that match the output from the first query.

Our qa dept recently started running into some problems in the C# middleware because the output of the 2 queries is disjoint, even though the same filter criteria are used in both queries.  Both queries start by asking for the top 20 orders that match the filter criteria.

Further complicating things, the qa automation suite does a lot of database restores, setting a few data conditions, running a test and then doing that again for the next test case.  After a lot of back-and-forth, we found that they are only having the problem the first time this query is run after a restore.  Every execution after the first one (with the same query) produces the correct results.

I even got their seed database and started doing the restores myself.  If I took the SQL code and ran it in SSMS immediately after the restore, I couldn't reproduce the problem.  I can only reproduce it if I run through the app code and ADO.NET to run the query.

I used Profiler to capture the query plan on that first execution and for reasons I can't fathom, only on that first execution it completely upends the query and starts by looking for that free-form blob of pet names as the seed kernel of the results.  e.g.

SELECT TOP (20) bunch of configurable columns, xml blob of customer preferences, ...
FROM
ORDERS
INNER JOIN CUSTOMERS ON CUSTOMERS.ID = ORDERS.CUSTOMERID
INNER JOIN
    (SELECT sums, avgs, etc FROM ORDERDETAILS
     GROUP BY ORDERDETAILS.ORDERID) orderavg on orderavg.ORDERID = ORDERS.ORDERID
... whatever other joins needed based on configuration ...
INNER JOIN CUST_PREFS ON CUSTOMERS.ID = CUST_PREFS.CUSTOMERID
WHERE ORDERS.DATE BETWEEN @from and @to
ORDER BY ORDERS.DATE

SELECT ORDERID, orderdetails
FROM
    (SELECT TOP(20) ORDERID
     FROM ORDERS
     WHERE ORDERS.DATE BETWEEN @from and @to
     ORDER BY ORDERS.DATE) ORDERS
INNER JOIN ORDERDETAILS ON ORDERDETAILS.ORDERID = ORDERS.ORDERID

Seemed straightforward enough - start from the orders table and work your way down.

But for this case QA ran into, only on the first execution after a db restore, and only through ADO.NET (not SSMS), the query plan comes out starting the execution with the CUST_PREFS "find me the random customer notes blob" join (the 7th join in the query).  Re-run the same query in the app again, and it goes back to starting with the root table (ORDERS).

A) I'm at a loss to figure out why it decides the 7th join is the right place to start immediately after a restore.  The stats don't bear it out and it goes back to something more understandable on the 2nd execution.

B) I could try to encapsulate the core filter query in a subselect (like the 2nd query has) to make the filter evaluation more predictable and only join for detail after the right subset is selected, but that seems a lot of reshuffling.

C) I'm debating adding an OPTION (FORCE ORDER) hint to the first query to try and make this more predictable, but I thought I'd toss this out amongst a group with deeper knowledge than I before pulling on that thread.

Any pointers from those wiser than me would be appreciated.


texts: MS SQL Server: Replication Monitor Transactional Replication Publisher Log Reader Agent Latency Too High?

texts: Is my Transactional Replication's Publisher's Log Reader Agent Latency too high?


texts: insert results from store procedure with parameters into table

texts:
I have a stored procedure that I want to insert into a table, the stored procedure however has parameters I have to pass, how can I do this?

DECLARE @RC int
DECLARE @startweek varchar(20)
DECLARE @endweek varchar(20)
DECLARE @payroll varchar(30)
DECLARE @job varchar(25)
DECLARE @job_to varchar(25)
DECLARE @manager varchar(30)
DECLARE @office varchar(100)
DECLARE @pu varchar(6)
DECLARE @pu_to varchar(6)
DECLARE @task varchar(25)
DECLARE @task_to varchar(25)
DECLARE @Prj_pu varchar(6)
DECLARE @Prj_pu_to varchar(6)

-- TODO: Set parameter values here.
SET @endweek = dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate());
SET @startweek = DATEADD(WEEK, -25, @endweek)

EXECUTE @RC = [dbo].[TIME_lynx_extract]
   @startweek
  ,@endweek
  ,@payroll
  ,@job
  ,@job_to
  ,@manager
  ,@office
  ,@pu
  ,@pu_to
  ,@task
  ,@task_to
  ,@Prj_pu
  ,@Prj_pu_to
GO

I want to insert the result of this (the calling of the stored procedure) into a table, I have created the table with the columns, but if there were a way with temp table even better.


texts:
I'm testing running monbodb on the kubernetes platform where I can limit the resources used by the running container.

Say I set the memory limit to 256Mb. The problem is that for example while making backup memory consumption increases to the limit and container gets restarted by kubernetes.

So the question is is there a way to limit mongodb memory consumption for my case so that it would not cause the crush by exeeding memory limit set by platform.

I could of course increase the limit but I'm interested in a principal solution and would like to understand this process better because I don't really now how memory consumed by mongodb and container os. Is it possible to tune mongodb/underlying linux os to work inside existing limits.


texts:
When should you rebuild an index? vs reorganize?

What index fragmentation value is considered healthy?


texts: Modeling a product database and dealing with inheritance

texts:
So, to get better at designing databases, I'm attempting to model a database system after the AR15 Builder.

Here is what I have so far:

While it makes sense logically, I'm afraid there may be too much inheritance... Might there be a better way to handle this? Any tips appreciated; thanks in advance!


texts:
Im working on a simple school system which handles student registration and schedule. Furthermore, the system should handle different type of schools Such as Kindergarten, Primary, Secondary, and High (Pre-school and k12)..
Im not an expert in DB design but still follow what i can from what i learn through reading and practice.

Student
Parent
Student_parent (If there is more than one parents want to be in the system)
School (Where the system can check student belong to which level (Primary or Secondary ..etc))
Subject (All the subjects in the schools)
Class (Basically Schedule-timetable)
Classroom (Every room and lab in the school (Basically facilities of the school))
Attendance (Not yet)
Marks (Not Yet)

are the tables relationships sufficient or need to redesign it?
Any problem with this basic schema?
Is it sufficient for the system?
How to Implement terms (Semester 1 and semester 2) as well as Years?
What about when the year ends. How to transfer students to a new year (How to implement this)?
I hope to get some points on improvements or issues with the schema before start programming it.
Thank you

.
.
Edit: implementing John Herbert suggestions.
.
.
Implementing John points except of the last one because there are no departments in the school and the number of students really subjective to the year.

changed tables names with prefixes to group them.
Modified few field as suggested by John for better search and grouping
Added table term and connected it with school (KG- Primary- Sec..etc)
Modified field sizes from Int(11) to something smaller as needed

DB Schema After Editing

after implementing these things someone cant help but wander. Will i need to add indexes for performance in the future?
Where will indexes possibly be needed ?
I hope this can benefit someone who is interested in DB design.


texts: ONLINE BACKUP is not performed from the CRON Job on LINUX server (manually it works)

texts:
I made a script which executes successfully online backup when it is run manually by the instance owner.
Script is the following (two lines originally).

 #!/bin/bash
    /opt/ibm/db2/V9.7/bin/db2 backup database maxdb71 online to "/data/archiveBackup"
 WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 INCLUDE LOGS WITHOUT PROMPTING;

As I said when I execute it manually by the instance owner it works.
I put it in the instance owner crontab.
crontab -l lists this script, and date/time on the server is OK.

Why the script is not executing the online backup when it works OK when it is run manually?

Thank you


texts: For my scenario I have already created a database user id that is auto incremented and they have already put their company id number in the registration. In the registration table both inputs are stored, but I would like to combine both together in a (database_id) (user_id) + "_" + (company_id) form so that everyone who has signed up for the application is unique in the database. Would this be possible?


texts: Query to find customers that have deposit in all branches located in a specific city

texts:
I have 3 tables as follow:

Customer (CustomerName , CustomerCity , CustomerStreet)
Branch   (BranchName , BranchCity)
Deposit  (CustomerName , BranchName , AccountNumber , Balance)

Here I show you sample data of the above tables :

Customer ('John','City1','Street1')
         ('Lili','City2','street2')

Branch   ('Branch1','Kaj')
         ('Branch2','Bahar')
         ('Branch3','Bahar')
         ('Branch4','Bahar')

Deposit  ('John','Branch1',12,1000)
         ('Lili','Branch1',123,1222)
         ('Lili','Branch2',124,45233)
         ('Lili','Branch3',345,6000)
         ('Lili','Branch4',567,5600)

So based on the information above, I need a query to give me CustomerName
that have a deposit in all branches that are in Bahar.

I should see 'Lili' in the output because it has deposits for all branches that are in Bahar but I should not see John cause it has a deposit only in one branch of Bahar.

I did my best to write the query but I could not. Could you help me with this please?


texts: Restoring a database backup to a local machine in MySQL

texts:
I have downloaded my .sql file of my database server. It is in the D: drive of my Windows machine.

I want to restore the back up in my this machine. I used:

mysql database -u root < backupfile.sql

Where database is my new database name in this machine. My confusion is that my backupfile.sql is in the D: drive and thus I guess there is error.

ERROR 1064 <42000>:You have an error in your SQL syntax;Check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql database -u root < backupfile.sql' at line 1.


texts:
I recently updated my server's database system from MySQL to MariaDB.
After update, everything works well.

But, when I went to phpMyAdmin, the default charset and types are changed:

At the end of this image, the default type was set to 'InnoDB' and charset was set to 'latin1_swedish_ci'.
They were 'MyISAM' and 'utf8_general_ci' before.
How can I revert my character set and type settings back, and how can I customize settings when I make new database?

When I looked at /etc/my.cnf and /etc/my.cnf.rpmnew, I couldn't find any settings related to DB type/charset.


texts: Troubles with creating initial PostgreSQL base

texts:
I'm trying to create a new base /usr/pgsql/bin/initdb -D /var/lib/pgsql/data --locale=en_USand got the following:

creating template1 database in /var/lib/pgsql/data/base/1 ... FATAL:  could not create shared memory segment: Invalid argument
DETAIL:  Failed system call was shmget(key=1, size=35233792, 03600).
... To reduce the request size (currently 35233792 bytes), reduce PostgreSQL's shared_buffers parameter (currently 3584) and/or its max_connections parameter (currently 104).
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for

How to fix this? Where can I change these SHMMAX and SHMIN? Do I really need to recompile the linux kernel as PostgreSQL advises me?


texts:
I'm using MySQL 5.6. I want to have a limited service-user which is able to create/modify the users of my databases.

Now my problem is that I can quite figure out what privileges my service-user needs to perform all the user administration tasks (create user, grant/revoke db privileges, set password)

If I give him the global "create user" and "reload" privileges, he can't use "set password" [= MySQL Error: 1044 (Access denied)]
If I give him "select", "insert", "update" on 'mysql'.'user' and 'mysql'.'db', he can't use "set password" [= MySQL Error: 1044 (Access denied)]
If I give him "select", "insert", "update" on 'mysql'.*, he CAN use "set password"

I'd like to understand why this is happening, and how to achieve my approach 1 or 2. I don't want to use approach 3.

The "set password" equivalent

UPDATE mysql.user
SET Password=PASSWORD('newpass')
WHERE User='bob'
AND Host='%';

...works with my privileges 1 and 2.

Can somebody help me out?


texts:
Assuming for a moment that FK references are not available, what lock hints should be placed on the preconditions of the following example to prevent deadlocks and inconsistent data?

(This is a contrived example, of course, to illustrate a scenario in which a delete/update can only be performed dependent upon the state of some other table)

-- Schema
CREATE TABLE EntityTypes (
    EntityTypeID int NOT NULL,
    Name nvarchar(50) NOT NULL,
    CONSTRAINT PK_EntityTypes PRIMARY KEY CLUSTERED (EntityTypeID)
);

CREATE TABLE Entities (
    EntityID int IDENTITY(1,1) NOT NULL,
    EntityTypeID int NOT NULL,
    EntityName nvarchar(50) NOT NULL,
    CONSTRAINT PK_Entities PRIMARY KEY CLUSTERED (EntityID)
);

-- Test data
INSERT INTO EntityTypes (EntityTypeID, Name) VALUES (1, 'Test 1');

-- Updater 1
SET XACT_ABORT ON;
BEGIN TRAN;

IF EXISTS (SELECT 1 FROM Entities WHERE EntityTypeID = 1)
BEGIN
    RAISERROR('Cannot delete entity type which is referenced', 16, 1);
    RETURN;
END

DELETE FROM EntityTypes WHERE EntityTypeID = 1;

COMMIT TRAN;

-- Updater 2
INSERT INTO Entities (EntityTypeID, EntityName) VALUES (1, 'Test Entity')

Environment: SQL Server 2012


texts: SQL server compare a field with another that is composed of a list separated by commas of the 1st field

texts:
I need to execute the below query but I have a problem.

I cannot use
tTerritorio.terrNome = tContactoTerritorio.territorios because territorios is a list of several terrNome separated by ,

How can I make this match so that I can retrieve the information I need?

select terrNome As Territorio, espDescricao AS Especialidade, COUNT (distinct contNome) AS 'Nº Médicos'
from tContacto, tContactoTerritorio, tTerritorio,tEspecialidade
where tContacto.contID=tContactoTerritorio.contID and tContacto.tpcontID=120
and tTerritorio.terrNome = tContactoTerritorio.territorios
and (tEspecialidade.espID=tContacto.esp1ID or tEspecialidade.espID=tContacto.esp2ID or
tEspecialidade.espID=tContacto.esp3ID or tEspecialidade.espID=tContacto.esp4ID or
tEspecialidade.espID=tContacto.esp5ID)
group by terrnome, espdescricao


texts:
I'm using Oracle 11gR2 in RHEL5. Now a days my application, during run time, is showing exception as below.

  Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

and

 Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

It happens only during the peak time.

Where can I find the reason? Somewhere it might be logging all. Where is it? I checked the alert_db.log as well as log.xml inside the tnslsnr, but couldn't find anything relating this.


texts: Why do I have different usernames from mysql.user and select user()?

texts:
With 10.4.6-MariaDB on my Mac OS 10.14.5
Logged in as root I can see the list of users on my server with

MariaDB [(none)]> select Host,User from mysql.user;
+--------------+-----------+
| Host         | User      |
+--------------+-----------+
| localhost    |           |
| localhost    |           |
| localhost    | root      |
| localhost    | user_Name |
| PCName.local |           |
+--------------+-----------+
5 rows in set (0.001 sec)

but when logged in as user_Name and I check the current user

MariaDB [(none)]> select user()
    -> ;
+-------------------------------+
| user()                        |
+-------------------------------+
| user_Name@localhost@localhost |
+-------------------------------+
1 row in set (0.000 sec)

That doesn't seem right to @localhost written twice.

So my question is why this happening?