texts: Should I put users in admin DB or in the DB that they should have access to

texts:
What would the pros / cons be of putting users that should have access to one database in the admin database instead of the database that they should have access to?

Background:

I'm a C# developer. This is the first live project using mongodb I'm working on.

I would like to know from people used to managing mongodb instances how they go about setting up users.

In this case I want three users, one for administration, one "webuser" that the intranet web application which I develop will use to read and write documents in one database and a reader user used when read access is enough. Both the readwrite and the reader user just need acces to one database. The admin user should be admin of the mongoserver. The database will be hosted on the server that hosts the web


texts: How to subtract fields and insert the value in other field automatically?

texts:
id  val1   val2    total

1    10     4       10-4(automatically get the value)
.
.
.
.

I am using php and mysql. I have a total column I need to make calculation automatically in a field, how it can be done?


texts: convert date in string format to date datatype sql

texts:
I have values in a column named Date as a nvarachar data type in the form of mmddyy and want to convert the values to a date datatype in the form of yyyy-mm-dd. What sql colde can I use to convert the value?
example 02121955 -> 1955-02-12


texts: How many tables will be there in Magento 1.8

texts:
I just started using Magento with 1500 products and I saw the DB size is about 150MB and when I looked at the tables, the count is 250 tables.

Is it normal?

Should I worry about anything?

I host this on EC2 micro instance.


texts: How is TINYINT(1) converted to BOOL/BOOLEAN?

texts:
I can understand that if you UPDATE a TINYINT(1) column and pass a value of 1 or 0 it will convert it to TRUE or FALSE, respectively.

I'm just confused on what happens when you UPDATE the column with a different value, say 2. In other words, if you UPDATE a TINYINT(1) column with the value 2, will this be TRUE or FALSE? Or, is this even doable?


texts:
In the MY SQL documentation Instance Dump Utility, Schema Dump Utility, and Table Dump Utility

The compatibility option - strip_tablespaces
Does it remove all references to tablespaces or only in the GRANT statement. Can you please provide an example for how this option makes change in the dump statement?

consistent

The documentation mentions that backup locks are released before the backup then how are consistent backups taken? START TRANSACTION WITH CONSISTENT SNAPSHOT is mentioned. Does it take snapshots?
If yes, what is the memory requirement?
Thank you!


texts: Reclaiming space after delete statement in SQL Server

texts:
I have some tables used in reporting and some of them grow huge with the daily ETL so I have implemented som jobs that delete rows more that x days old. E.g. everyday 10% new data gets added and I delete 10% of the oldest rows in another job.
Do I need to do something for efficiency? Some tables are heaps with nonclustered indexes and some also have clustered indexes. E.g. to I need to rebuild the tables in case of heaps or rebuild index in case of clustered index on the table? If so how often? Most tables are only used once per day and when all joins and calculations are done they get extracted for visualization in a non-live manner.
I know a little about index fragmentation and heap fragmentation and that one can query fragmentation in percent.
How much do I need to worry about the above scenario?


texts: How the sys user gets the sysdba privilege?

texts:
I am using oracle 12c on Linux. Using the enterprise manager database express I am looking at the list of privileges and roles for the sys user but I haven’t found the sysdba privilege assigned there.

Is the sys user member of an oracle role or Linux group with sysdba privilege? What role or group?


texts: How to return decimals in the right number format with Oracle JSON_VALUE

texts:
The decimal separator in the JSON format is the period (.). However, when the number format of a system uses comma as a decimal separator (e.g. 10,3 instead of 10.3), the Oracle json_value expects that in order to return a valid number.

Example:

SELECT
json_value(METADATA, '$.Amount' RETURNING NUMBER(18,6)) As AMOUNT
FROM TABLE

This will return (null) for any JSON value that contains a decimal. I cannot save strict JSON with comma decimals. How can I then get a number back from valid JSON if Oracle cannot handle that conversion? Is there some keyword I can use in my RETURNING statement?

Note1: I have used to_number(Replace(...)) but that feels wrong and slows down some queries.

Note2: I do not want to change the number format in my session, since I want to parse the JSON and use it in a view.


texts:
I've been getting these two errors on SQL Server log:

Error 1:

  DESCRIPTION:    Import of Microsoft Distributed Transaction
  Coordinator (MS DTC) transaction failed:
  0x8004d00e(XACT_E_NOTRANSACTION).

Error 2:

  DESCRIPTION:    Enlist operation failed:
  0x8004d00e(XACT_E_NOTRANSACTION). SQL Server could not register with
  Microsoft Distributed Transaction Coordinator (MS DTC) as a resource
  manager for this transaction. The transaction may have been stopped by
  the client or the resource manager.

It happens at random times and I don't know how to track and associate it with any job or application request. No change has been made to the server that I'm awere of.

Here's what I have done so far:

Verified that Distributed Transaction Coordinator service is running on Windows;
Event Viewer didn't show any error I could relate to the problem;
Firewall is off while I'm troubleshooting;
Followed the instructions to Configure DTC
Security;
Have seen similar threads like
this
and this;
Tried to uninstall and
install MSDTC;
Tested the DTC with dtctester.exe and here's the output:

EDIT 1:

Ran test using DTCping and it showed an error:

To fix DTCping error I Set the appropriate values for the EnableAuthEpResolution and RestrictRemoteClients options on the client side;
Tested the DTC with dtctester.exe again and now it worked fine;

EDIT 2:

Configured Extended Events. It captured the error and brought
information about the user, database and server involved on the
error, but it wasn't enough to find out the culprit. Here's the code
used to create the XE session:

CREATE EVENT SESSION [MSDTC_TROUBLESHOOT] ON SERVER
ADD EVENT sqlserver.errorlog_written(
    ACTION(package0.callstack,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[client_hostname]=N'MyAppServer' AND [sqlserver].[database_name]=N'MyDatabase'))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Even though MSDTC passed the tests I'm still getting the two errors on SQL Server log. Is there anything else I can do find out what is causing them?


texts:
I used mysqldump command to export/dump .sql file with rows like:

String cmd1 = mysqldump + " -h" + host + " -u" + username + " -p" + password + " " + "--compact --no-create-info " + "--where=ID=" + "'5b4ed36d-6152-4bfd-ad06-666f781e8bdc'" + " " + database + " " + "EMPLOYEE" + " " + "--result-file=" + "C:\\employee.sql";

This is the result of above command in a employee.sql file:

INSERT INTO `employee` VALUES ('Anette','Busch','2014-08-12','admin',' ','FALSE');

Now, I want to import the values from empolyee.sql file using command:

String cmd =  mysql + " --user=" + username + " --password=" + password +  " -e" + " source " + restoreFile;

The problem is I can't execute import command. I'm using the same import command for whole database, that works fine, but not in this case.

Thanks a lot for your guidance.


texts: Unable to get anything other than 'Remote scan' when selecting from linked MySql server

texts:
I have a linked server in SQL Server 2012 (SP3) that points to a MySql 5.1.61 server on another machine.

Whatever I do, I don't seem to be able to get any where filtering to occur on the remote side.

I tried all possible combinations of the linked 'Server Options' (Collation Compatible, RPC, RPC Out, Use Remote Collation, Collation Name, Enable Promotion of Distributed Transactions for PRC), I also tried different MySql connectors for ODBC (v5.1, v5.2) and tried to put (or omit) various charset clauses in the connection string.

There is only one table I'm interested in, I'm only doing selects, there are proper indices on that table for any query I might want to make, all queries execute instantly when run directly on the MySql machine, but all of them result in this execution plan in SQL Server:

The table contains millions of rows and it is extremely slow.

The only thing that works for me in this situation is dynamic openquery, but that needs to be in a stored procedure, and I would very much like to expose this functionality as a view.

If that matters, the MySql database charset is latin1 (ISO 8859-1 West European) and collation is latin1_swedish_ci.

What have I missed and how do I make it work?


texts:
I know there is question related to inconsistent datatypes but that comes from a query. while
I am getting this error on database side when we try to access user defined datatype on our schema that is written in some other schema.
E.g.
we are having xxxtypes schema and we have defined several UDT there. Now in our database we are using xxxtypes.employeeList, so it is throwing following error from that line.

java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got SYSTPzM8RTCHEUy/gQAB/AQBa6A==

EDIT:
Q :Why this error is coming ? and How to overcome this ?

Examples:

create or replace
TYPE InstalmentList IS TABLE OF InstalmentEntry;

   create or replace
   TYPE InstalmentEntry IS OBJECT
   (
    reservationinstalment_id NUMBER,
    value NUMBER
   );

We have created above type in xxxtypes schema, to use it for several clients.
Now for a client's database  we are using it as :

  SELECT
          column_value AS list,
          rownum AS rownumber
        FROM
          TABLE(
            CAST(
              powermultiset(
                CAST(
                  MULTISET(
                    SELECT
                      rsv_int.reservationinstalment_id,
                      rsv_int.value
                    FROM reservationinstalment rsv_int
                    WHERE
                      rsv_int.reservation_id = p_reservation_id
                      AND
                      rsv_int.payer_type = p_payer_type
                      AND
                      rsv_int.type <> 'r'
                  )
                  AS
                  **xxxtypes.InstalmentList**
                )
              )
              AS
              InstalmentListList
            )
          )
      ) combinations

now xxxtypes.InstalmentList is throwing error saying that it is getting some inconsistent datatypes.


texts: Why PostreSQL 9.2 does not use index scan if it can?

texts:
My PosgreSQL planner (9.2) does not pick index and rather choose to do seq scan.

Here is my table. The index I am talking about is  name_left_prefix

cwu=# \d web_city;
                                  Table "public.web_city"
   Column   |         Type          |                       Modifiers
------------+-----------------------+-------------------------------------------------------
 id         | integer               | not null default nextval('web_city_id_seq'::regclass)
 name       | character varying(64) | not null
 latitude   | double precision      |
 longitude  | double precision      |
 time_zone  | character varying(64) | not null
 population | bigint                |
 country_id | integer               |
 location   | geometry(Point,4326)  |
Indexes:
    "web_city_pkey" PRIMARY KEY, btree (id)
    "name_left_prefix" btree ("left"(name::text, 5))
    "web_city_country_id" btree (country_id)
    "web_city_location_id" gist (location)
Foreign-key constraints:
    "country_id_refs_id_55d3b49fd200671" FOREIGN KEY (country_id) REFERENCES web_country(id) DEFERRABLE INITIALLY DEFERRED

Now I run the query that is supposed to use that index.

cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=19016.35..21188.28 rows=94677 width=10) (actual time=502.308..691.288 rows=64459 loops=1)
   ->  Sort  (cost=19016.35..19345.84 rows=131796 width=10) (actual time=502.300..660.121 rows=131796 loops=1)
         Sort Key: ("left"((name)::text, 5))
         Sort Method: external merge  Disk: 2048kB
         ->  Seq Scan on web_city  (cost=0.00..5554.45 rows=131796 width=10) (actual time=0.077..46.672 rows=131796 loops=1)
 Total runtime: 694.977 ms
(6 rows)

It does not use it though. But it does use it when I disable seq_scan and the resulting time is considerably better!

cwu=# set enable_seqscan=false;
SET
cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.00..21237.14 rows=94677 width=10) (actual time=0.187..114.887 rows=64459 loops=1)
   ->  Index Scan using name_left_prefix on web_city  (cost=0.00..19394.69 rows=131796 width=10) (actual time=0.163..79.829 rows=131796 loops=1)
 Total runtime: 118.095 ms
(3 rows)

Why this is happening? I don't really desire to set enable_seqscan=false; from my application before making that query.

EDIT: I have just found out that this does not happen on my server that has PostgreSQL 9.2.3. But it does happen on my laptop with PostgreSQL 9.2.4. Maybe I'll try to downgrade just test it out.

EDIT2: After setting work_mem = 4MB to avoid Sort Method: external merge  Disk: 2048kB(Thank you, Igor!) the time has got considerably better for the Seq Scan. Now I approve the decision not to use the index:).

cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=6213.43..6671.27 rows=36627 width=10) (actual time=95.879..111.447 rows=64459 loops=1)
   ->  Seq Scan on web_city  (cost=0.00..5554.45 rows=131796 width=10) (actual time=0.424..45.285 rows=131796 loops=1)
 Total runtime: 115.838 ms


texts:
How do I track down a user that may be running a relatively quick query, but running it excessively?  For examples, they may:

be running a report over and over and over again
have a book set on their keyboard
have a refresh interval set very low

Other users may be running the exact same queries and reports, but their load on the system is minuscule.

I know how to track down problem queries, waits, etc., but I'm looking for a cumulative user-specific problem.


texts: SQL Server Data Tools for VS 2015 - Anyone using for SSIS development to multiple versions?

texts:
According to this link, https://www.mssqltips.com/sqlservertip/4253/backwards-compatibility-in-sql-server-data-tools-for-integration-services/
 the newest version of SQL Server Data Tools - "SSDT for Visual Studio 15" (16.5) is compatible with SQL 2016 AND older versions as well. In previous incarnations of the software, whether it was called "SQL Server Data Tools", "SQL Server Data Tools - Business Intelligence", or "Business Intelligence Development Studio", we always seemed to run into version incompatibility issues when deploying.

If I'm reading this correctly, we could uninstall all older versions of the tools, and just install SSDT 16.5 for development of SSIS packages for SQL 2012 and newer. Due to many issues I've seen in past versions of the tools, I am hesitant to develop new projects in this.

Is anyone successfully deploying SSIS packages to SQL 2016 AND older versions with SSDT 16.5? Obviously, tasks that are only supported in newer versions wouldn't work in older versions.


texts:
I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will hold 180-260 million records. As it will be properly indexed table, so I am thinking of limiting the table records to 20 million this way I would have to create 9-13 tables.

But I am not quite sure about how it will improve the performance because they will be sitting on same machine (32GB RAM)?

I am using MySQL and tables would be MyISAM and big table would have index on id field and there are not further complexities like full text search etc.

Please also shed light on table partitioning vs database partitioning.


texts: Shouldn't mysql be using both indexes in a join?

texts:
I have the following tables:

CREATE TABLE `tx_in` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `txid` char(64) DEFAULT NULL,
  `hashPrevOut` char(64) DEFAULT NULL,
  `address` char(34) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `hashPrevIndex` (`hashPrevOut`(8))
) ENGINE=InnoDB AUTO_INCREMENT=641391046 DEFAULT CHARSET=latin1

CREATE TABLE `tx_out` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `txid` char(64) DEFAULT NULL,
  `btc_value` double DEFAULT NULL,
  `address` char(34) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `txidIndex` (`txid`(8))
) ENGINE=InnoDB AUTO_INCREMENT=695391886 DEFAULT CHARSET=latin1

And I am running the following query:

UPDATE tx_in LEFT JOIN tx_out ON tx_in.hashPrevOut = tx_out.txid SET
tx_in.address = tx_out.address;

I modified it into a SELECT statement so that I could run EXPLAIN on it.

EXPLAIN SELECT * FROM tx_in LEFT JOIN tx_out ON tx_in.hashPrevOut = tx_out.txid;

And got this output

+----+-------------+--------+------------+------+---------------+-----------+---------+------------------------------+-----------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref                          | rows      | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+-----------+---------+------------------------------+-----------+----------+-------------+
|  1 | SIMPLE      | tx_in  | NULL       | ALL  | NULL          | NULL      | NULL    | NULL                         | 635293613 |   100.00 | NULL        |
|  1 | SIMPLE      | tx_out | NULL       | ref  | txidIndex     | txidIndex | 9       | blockchain.tx_in.hashPrevOut |         2 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+-----------+---------+------------------------------+-----------+----------+-------------+

But the execution plan doesn't have any reference to the index on hashPrevOut.  Should I be concerned about this?  It seems like it should just be using the two indexes to do the join.  As you can see the tables are quite large so this could make a big difference in execution time.


texts:
I posted a thread on Stack Overflow and one person pointed out that my database structure wasn't the best so now I'm here to ask how to do this in a good and proper way.

Currently I have one table called "users" and in the table I have a lot of userinfo. In "users" I have a column called "tags" were information inside the column look like "#dj #personal #coding" and things like that for every user. Is there any better way to store tags describing the user? I have one column called "tags" were the user have stored tags describing him/her and also a column called "interests" were tags are stored to remember what the user is looking for...

Basically, is there any better way to store these tags for each user?


texts: Find out MySQL index size for a concrete index

texts:
In MySQL, for InnoDB engine, how do I find out the size of one of the indexes I have in the same table ?

I know I can select the index_length from information_schema.tables, but this gives the total size of all indexes in the table.

Let's suppose I have a table with PK index, several FK indexes and unique constraints, etc. Can I find out the size of one of those indexes only? Let's say, I'd like to know the size of the PK index only.


texts: Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

texts:
We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL.
In our application, we are planning to upload customer’s data using a Excel sheet and these data will be displayed on custom build interactive dashboard.

I have few questions before I start working on this application

1) For the mass data upload from excel, which db is suitable MySQL or PostgreSQL?

2) At some point is it possible that multiple customers can start data upload at same time ?. To deal with such scenario is it advisable to create a separate db for each customer or to use a single db for application(single tenancy vs multi-tenancy). Please tell me pros and cons if any for each scenario?

3) How it would affect my dashboard performance, if I go with multiple databases design and I join tables across multiple databases?

Thanks


texts: Oracle listener give "Message 850 not found; No message file for product=network, facility=NL"

texts:
I'have just completed oracle 11g database software installation on Linux.
I launched netca and configured a listener.
As soon as I try to run lsnrctl I get the following error.

[oracle@lnx-db ~]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-MAY-2019 15:34:41

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Message 850 not found; No message file for product=network, facility=NL

The environment variables seem to me set correctly.

[oracle@lnx-db ~]$ locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

[oracle@lnx-db ~]$ echo $ORACLE_HOME $ORACLE_SID $ORACLE_BASE
/u01/app/oracle/product/11.2.0/dbhome_1 ORCL /u01/app/oracle

From what I read on https://community.oracle.com/thread/2322893?tstart=0 it could be a problem with the mesg but files seems there and readable.

[oracle@lnx-db ~]$ file $ORACLE_HOME/network/mesg/*
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/naukus.msb: data
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/naukus.msg: ASCII English text
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/ncrus.msb:  data
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/ncrus.msg:  ASCII English text
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/niqus.msb:  data
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/niqus.msg:  ASCII English text
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/nlus.msb:   data
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/nlus.msg:   ASCII English text
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/nmpus.msb:  data
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/nmpus.msg:  ASCII English text
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/nmrus.msb:  data
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/nmrus.msg:  ASCII English text
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/nnfus.msb:  data
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/nnfus.msg:  ASCII English text
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/nnlus.msg:  ASCII English text
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/snlus.msb:  data
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/snlus.msg:  ASCII English text
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/tnsus.msb:  data
/u01/app/oracle/product/11.2.0/dbhome_1/network/mesg/tnsus.msg:  ASCII English text

Any hints?

EDIT1: As suggested below I've checked NLS_LANG and TNS_ADMIN variables. Both are not set. But settings them does not fix the problem:

[oracle@lnx-db u01]$ echo $NLS_LANG $TNS_ADMIN ; lsnrctl
American_America.UTF8 /u01/app/oracle/product/11.2.0/dbhome_1/network/admin

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-MAY-2019 16:16:05

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Message 850 not found; No message file for product=network, facility=NL
LSNRCTL> q


texts:
I have a postgresql DB with Thai and English values/strings in several tables.

Currently I have the following settings

| Encoding |   Collate   |    Ctype |
| UTF8     | en_US.UTF-8 | en_US.UTF-8  |

If I order by a column with Thai values, the sort order is not correct.

What settings should I change to have postgresql sort correctly?


texts: PowerPivot doesn't install in SharePoint mode on an Active/Passive Cluster

texts:
We a provisioning a new SQL Failover Cluster. We have successfully configured the SQL instance, and have moved on to configuration of the PowerPivot for SharePoint instance. We run the configuration wizard, and create a new failover cluster. However when the install completes, the resultant Analysis Services instance is in Multidimensional Mode (Compatibility level 1100) rather than in SharePoint integrated mode.

After the configuration had completed, the SSAS instance is in the wrong mode.

This works fine on a non-clustered instance in a VM lab, anyone else experienced a similar issue and hopefully has a fix?


texts: PostgreSQL - Return type and EXECUTE QUERY

texts:
My function returns a setof composite type, however the RETURN EXECUTE seems to return some other unknown type:

create type core.search_result as (
  user_id uuid,
  country core.country,
  ethnicity core.ethnicity,
  is_smoker core.smoking_status,
  existing_conditions varchar[]
);

create or replace function core.search_samples(
  country core.country,
  ethnicity core.ethnicity,
  is_smoker core.smoking_status,
  existing_conditions varchar[]
) returns setof core.search_result as $$
  declare
   _where text;
   _sql text := '
     select (
       core.user.id,
       core.user.country,
       core.health_wellness.ethnicity,
       core.health_wellness.is_smoker,
       core.health_wellness.existing_conditions
     )::core.search_result
     from
       core.user
     join
       core.health_wellness
     on
       (core.user.id = core.health_wellness.user_id)';
    results core.search_result[];
  begin
    _where := concat_ws(
      ' AND ',
      CASE WHEN country IS NOT NULL THEN 'core.user.country = $1' END,
      CASE WHEN ethnicity IS NOT NULL THEN 'core.health_wellness.ethnicity = $2' END,
      CASE WHEN is_smoker IS NOT NULL THEN 'core.health_wellness.is_smoker = $3' END
    );
    raise notice '_where: %', _where;
    IF _where <> '' THEN
      _sql := _sql || ' WHERE ' || _where;
      return query EXECUTE _sql
      USING $1, $2, $3;
    end if;
    raise notice 'all input params null';
  end;
$$ language plpgsql stable;

When I try to run the function:

select core.search_samples('United Kingdom', null, null, null);

I get the following error:

NOTICE:  _where: core.user.country = $1
ERROR:  structure of query does not match function result type
DETAIL:  Returned type core.search_result does not match expected type uuid in column 1.
CONTEXT:  PL/pgSQL function core.search_samples(core.country,core.ethnicity,core.smoking_status,character varying[]) line 29 at RETURN QUERY


texts: How to create a database that is a randomized subset of another db (mysql)?

texts:
I have a MySQL-DB containing 2 tables "records" and "features" linked by one foreign key in features referring to the primary key "id" in records.

My goal is to create a randomized but fixed subset of the data in the DB for machinelearning that can be dumped and hashed for reproducability.

So I thought it would be handy if I could draw a randomized subset of the data and move it to a seperate DB.

Is that possible and if yes: how?


texts:
I need copy full database in MySql but cant to do this.
I try
mysqldump db_name | mysql new_db_name

Its work, but DB so big and this comand need 5Gb+ in max_allowed_packed
Maybe you know best solution?
Task: Full copy Database with data
Thanks!


texts: Solve conflict between affinity mask and I/O affinity mask?

texts:
I executed this on one of our SQL Server machines:

EXEC sp_configure 'show advanced options', 1

And then executed RECONFIGURE. This presented me with the following error message:

  The affinity mask specified conflicts with the IO affinity mask
  specified. Use the override option to force this configuration.

By executing RECONFIGURE WITH OVERRIDE I can indeed force the configuration. But this is not a solution to the conflict mentioned in the error message.

The values in sp_configure are as follows:

name                 minimum        maximum       config_value   run_value
affinity I/O mask    -2147483648    2147483647    126            126
affinity mask        -2147483648    2147483647    126            126

I found some explanation here, but this basically just says "There should not be a conflict", nothing on how to solve it. Could someone knowledgeable please explain to me what this error message actually means and how/if I should try to solve it?

Update 12/12/2012

We couldn't find any reason for the setting of conflicting values for affinity I/O mask and affinity mask. I changed them back to the default values, 0 for both. After the reconfiguration affinity mask is set to 0 immediately, affinity I/O mask apparently requires a restart of the instance.


texts:
I am using windows 2003 server, with 8G of RAM and MySQL was installed on It.

I am getting error as follows

out of memory.[ERROR] mysqld: Out of memory (Needed 523712 bytes).

The configuration which i have given are as

max_connections=5000
query_cache_size=16M
table_cache=256
tmp_table_size=103M
thread_cache_size=12
myisam_max_sort_file_size=1M
myisam_sort_buffer_size=205M
key_buffer_size=175M
read_buffer_size=512k
read_rnd_buffer_size=1M
sort_buffer_size=2M
innodb_additional_mem_pool_size=12M
innodb_log_buffer_size=8024K
innodb_buffer_pool_size=1024M
innodb_log_file_size=170M
innodb_thread_concurrency=24

Can any one please tell me the solution.?why this is happening.


texts: How to find records where specific values are different

texts:
I have two conditions:

first has a value of Null
second has a value of XX

In the database there should Not be one without the other and the Rates should match.

I want to find all records where the Rates are different for the Null and XX records.

How can I do this in PL/SQL?

The database is a work db of procedure codes and I'm looking for a query that will find all procedure codes that have a modifier = null and modifier = xx where the rates are not equal. All fields are within the same table.

I'm thinking a nested type query but can't seem to figure it out.

Table name Procedure Code Price

Procedure Code = T2015

Modifier = Null.  With a Rate of $32.50

Modifier = XX.    With a Rate of $23.70

The table Procedure Code Price holds many CPT & HCPS codes. Each code can have multiple lines with different modifiers and rates. The correct entry for the example above would be the rates for Null & XX modifier are the same. What I want to find are the entries where the rates are different for these two modifiers.


texts: Is it bad to use NULL in foreign key column to mean something (e.g. this is the default data)?

texts:
I have tried searching for good guidance on this already, but without much luck. Still, apologies in advance if this is duplicated elsewhere.

The Problem

In a nutshell, we pay external contractors to work on cases for our clients. We already have tables with contractor and client information in our SQL Server database. Going forward we'd like to store accounting and billing info in there too. Pay rates can differ for each client and contractor, but usually each client has a general "default" pay rate that applies to most contractors.

Option A

The initial proposal was to create a new table with the following basic design:

clientContractorPay

clientID - foreign key to client table
contractorID - foreign key to contractor table
basePay - pay rate for this client-contractor combination
... - several more (10+ and likely to grow) columns with supplemental pay rate details
A unique index (on clientID and contractorID) to optimize lookup and also prevent multiple rows for a given client-contractor combination.

Contractor-specific pay rates would naturally be linked to the relevant contractor (and client). General (default) pay for a client would be stored in a row where contractorID is NULL. This is to avoid duplicating the same default pay for all contractors that don't have specific exceptions. For example:

| clientID | contractorID | basePay | ... |
===========================================
|    1     |      75      |   12.5  | ... |
|    1     |      82      |   15.5  | ... |
|    1     |     NULL     |   10.0  | ... |  /* Default pay rate for client 1 cases */

Option B

However, one of our senior devs has strong reservations about Option A. Their main argument is that using NULL in the contractorID column to mean "this is the default pay rate row" is unintuitive and/or confusing. In other words, it's bad to assign meaning to NULL values.

Their counter proposal was to duplicate these new pay rate columns in the client table. The data stored there would indicate the default pay for each client, while contractor-specific exceptions would still live in the new table above.

What To Do?

It seems clear both proposals would work just fine, but I have my own reservations about the second. Mainly it seems wrong to store the same type of data (client-contractor pay rate details) in multiple places, not to mention more complex logic to read/write this data. I also don't like duplicating these new columns in both tables, since it would force us to add any future pay rate columns to both tables.

However, I can see my colleague's point about potentially misusing NULL in this case. At the very least, it's not immediately obvious that rows with a NULL contractorID contain default pay rates.

It's been far too long since my database programming courses, so I'm not sure what the current best practice for this type of entity relationship is? I'm open to whatever is best long term, and would appreciate any expert guidance, especially with links to additional resources.

Thank you in advance!


texts: SELECT COUNT(*) returning incorrect results

texts:
I am seeing some very strange behavior on PostgreSQL 9.6.3 on RDS Aurora.

I am getting duplicate results from certain queries:

=> select count(id) from foos where id = 'deadbeef';
 count
-------
 2
(1 row)

=> select id from foos where id = 'deadbeef';
            id
--------------------------
 deadbeef
 deadbeef
(2 rows)

=> select id, created_at from foos where id = 'deadbeef';
            id            |         created_at
--------------------------+----------------------------
 deadbeef                 | 2018-01-01 10:00:00.000000
(1 row)

(id values, timestamps, and table names have been obfuscated)

I do not have table inheritance on this table nor any others.

This appears to only affect queries that hit exactly one index on the foos table.

Because this appears to be isolated to individual indexes, I imagine running REINDEX may resolve this issue.

However, I have no idea how many indexes exhibit this behavior.

For instance, here is similar behavior for the same record via a different index:

=> select bar from foos where bar = 'qux';
                  bar
-----------------------------------------
 qux
 qux
(2 rows)

=> select id from foos where bar = 'qux';
            id
--------------------------
 deadbeef
(1 row)

=> select bar, id from foos where bar = 'qux';
                  bar                    |            id
-----------------------------------------+--------------------------
 qux                                     | deadbeef
(1 row)

=> select bar, created_at from foos where bar = 'qux';
                  bar                    |         created_at
-----------------------------------------+----------------------------
 qux                                     | 2018-01-01 10:00:00.000000
(1 row)

Here are the table's relevant indexes:

Indexes:
    "pk_foos" PRIMARY KEY, btree (id)
    "index_foos_on_bar" UNIQUE, btree (bar)

Here's the explain plans of the first couple examples:

=> explain select id from foos where id = 'deadbeef';
                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using pk_foos on foos  (cost=0.42..8.44 rows=1 width=25)
   Index Cond: (id = 'deadbeef'::text)
(2 rows)

=> explain select id, created_at from foos where id = 'deadbeef';
                              QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using pk_foos on foos  (cost=0.42..8.44 rows=1 width=33)
   Index Cond: (id = 'deadbeef'::text)
(2 rows)

What is going on here?

Or, how can I figure out what is going on here?


texts: Set column to only accept negative values

texts:
Is there anyway to change a column data type so that it will accept only negative values?

It's SQL Server 2014 Azure.


texts: SQL Server 2008: The login is from an untrusted domain and cannot be used with Windows authentication

texts:
The question here is different from 1 & 2

When trying to connect to a SQL Server database that is on another system in the network both are in workgroup, when attempting to connect to a SQL Server 2008 instance using SQL Server Management Studio with Windows authentication, I get connected.

But when trying to connect from my application, I get the following error

  (Login failed. The login is from an untrusted domain and cannot be
  used with Windows authentication.) Native=18452).

If SQL Server Management Studio can connect, what would be required for my application to get connected?


texts: Limit number of simultaneous queries by user

texts:
I am looking for way to allow one user to execute maximum 3 queries at the time. Is there such possibility in Postgres?

Due to some technial difficulties I cant limit number of connections, and the solution should be based on limit of simultaneus queries or something similar.


texts: Change path for binary logs and scheduling

texts:
I want to change the default name of binary log and path. I saw the documentation where it discusses about base_name. However, I just need to confirm how to use it correctly. I want binary logs to be stored on another partition and also want to schedule binary logging to run at regular intervals in a day.

I am using MySQL 5.5.


texts:
I am working on application for managing football league as my homework. I want filter list of players by number of matches they have played in league. For example user can enter count > 0 and count < 5.

I wrote query which works fine when count is bigger than zero. However, in some cases user may want include players that weren't in any matches, yet. For example:

SELECT p.PLAYER_ID, p.NAME, p.SURNAME, p.POSITION, p.TEAM_ID, p.DRESS_NUM FROM player p
 LEFT JOIN player_in_squad psq ON p.PLAYER_ID = psq.PLAYER_ID
 LEFT JOIN squad sq ON sq.SQUAD_ID = psq.SQUAD_ID
 LEFT JOIN squad_match sqm ON sqm.SQUAD_ID = psq.SQUAD_ID
 LEFT JOIN match m ON m.MATCH_ID = sqm.MATCH_ID
     WHERE sqm.HOME = 'N'
     AND m.LEAGUE_YEAR = 1
     AND m.LEAGUE_TYPE_ID = 301
    GROUP BY p.PLAYER_ID,  p.NAME, p.SURNAME, p.POSITION, p.TEAM_ID, p.DRESS_NUM
    HAVING COUNT(*) < 2;

In this query only count > 0 is returned. So I have created manual condition which is detecting if user wants include count = 0 in his filtered list and then I add this to beginning of the query:

SELECT p.PLAYER_ID, p.NAME, p.SURNAME, p.POSITION, p.TEAM_ID, p.DRESS_NUM FROM player p INTERSECT

... but I don't like this workaround solution and I suppose neither my teacher will.

I think there must be better approach. Any ideas? Thy


texts: Will these queries return the same results?

texts:
I have this simple query:

SELECT
    friend_id
FROM
    default_friend
WHERE
    user_id = 1

And the results are below:

+-----------+
| friend_id |
+-----------+
|      2250 |
|      4901 |
|      7187 |
|      9337 |
|      9843 |
+-----------+

Now I have this query too a bit more complex:

SELECT
    friend_id, user_id, approved
FROM
    default_friend
WHERE
    user_id = 1
        AND (friend_id IN (SELECT
            friend_id
        FROM
            default_friend
        WHERE
            user_id = 1))
        AND approved = 0

My question is: will the SELECT into the second query return the same data as the data returned by first query? Meaning friend_id = 2250, 4901, 7187, 9337, 9843 or I need to run the first and the programmatically split the results and build the second one? Also I'll like to know which way is more optimum!


texts: query start, due, complete dates for first and last time phases from phase tracking table

texts:
linked excel file

I have a phase tracking table which consists of the fields record id, phase name, record order number etc:

RECORD_ID,phase,RECORD_ORDER,SUBMITTED_DATE,DUE_DATE,COMPLETED_DATE
1657,Initiate,0,03-Oct-17,06-Oct-17,04-Oct-17
1657,Investigation,1,04-Oct-17,17-Oct-17,07-Oct-17
1657,Initiate,2,08-Oct-17,10-Oct-17,10-Oct-17
1657,Investigation,3,11-Oct-17,18-Oct-17,17-Oct-17
1657,Approve,4,18-Oct-17,30-Oct-17,28-Oct-17
1657,Completed,5,29-Oct-17,,29-Oct-17
2378,Initiate,0,03-Oct-17,06-Oct-17,05-Oct-17
2378,Completed,1,05-Oct-17,NULL,05-Oct-17
3133,Initiate,0,03-Jun-17,06-Jun-17,04-Jun-17
3133,Investigation,1,04-Jun-17,17-Jun-17,07-Jun-17
3133,Initiate,2,08-Jun-17,10-Jun-17,10-Jun-17
3133,Investigation,3,11-Jun-17,18-Jun-17,17-Jun-17

This table is very big and certain records have been re-routed multiple times, others haven't.

I have a requirement to create a report showing for each unique record_id for each phase the first time the record entered this phase (so the lowest RECORD_ORDER number) and the last time the record entered that phase (so highest RECORD_ORDER number). Below a sample of the desired output table:

RECORD_ID,KEYWORD1,KEYWORD2,KEYWORD3,FIRST_INI_SUBM_DATE,LAST_INI_SUBM_DATE,...
1657,CUST,LOW,L1,03-Oct-17,08-Oct-17,...
2378,COMP,MEDIUM,L2,03-Oct-17,03-Oct-17,...
3133,VEND,HIGH,L3,03-Jun-17,08-Jun-17,...

I'm struggling to find the right approach to realise this through MAX.
I need a kind of statement like : give me the three dates for phase equals "initiate", lowest record order match and again for highest record order match.

I successfully created a statement which gives me the lowest and highest record_order number for each of the phases:

SELECT
TABLE1.RECORD_ID,
TABLE1.KEYWORD1,
TABLE1.KEYWORD2,
TABLE1.KEYWORD3,
MIN(CASE WHEN PT.PHASE = 'INITIATE' THEN PT.ETQ$RECORD_ORDER END) AS INIMIN,
MIN(CASE WHEN PT.PHASE = 'INVESTIGATE' THEN PT.ETQ$RECORD_ORDER END) AS INVMIN,
MIN(CASE WHEN PT.PHASE = 'APPROVED' THEN PT.ETQ$RECORD_ORDER END) AS APPMIN,
MIN(CASE WHEN PT.PHASE = 'IMPLEMENT' THEN PT.ETQ$RECORD_ORDER END) AS IMPMIN,
MAX(CASE WHEN PT.PHASE = 'INITIATE' THEN PT.ETQ$RECORD_ORDER END) AS INIMAX,
MAX(CASE WHEN PT.PHASE = 'INVESTIGATE' THEN PT.ETQ$RECORD_ORDER END) AS INVMAX,
MAX(CASE WHEN PT.PHASE = 'APPROVED' THEN PT.ETQ$RECORD_ORDER END) AS APPMAX,
MAX(CASE WHEN PT.PHASE = 'IMPLEMENT' THEN PT.ETQ$RECORD_ORDER END) AS IMPMAX

FROM
TABLE1
LEFT JOIN
TABLE2 PT ON PT.RECORD_ID = TABLE1.RECORD_ID

GROUP BY TABLE1.RECORD_ID, TABLE1.KEYWORD1, TABLE1.KEYWORD2, TABLE1.KEYWORD3

I'm struggling to pull the corresponding dates, please advice.


texts: Overriding default datetime output format

texts:
By default, my Postgres database outputs a DateTime in the current format YYYY-MM-DD hh:mm:ss. However, I want to override this and output it in this format YYYY-MM-DDThh:mm:ss with the T in between the date and the time.
Is there a way to override this with any format? I don't want to use to_char or anything similar, I want any DateTime returned by my database to be in that format (or any format I specify) by default.
Thanks


texts: How can I model an ERD with relationships that include "this or that but not both" and "this and that"?

texts:
I am having trouble drawing a entity-relationship diagram (ERD) and I wanted to ask for any guidance on what I am doing wrong.
Busines rules
The descriptions of the assignment and of the business domain of relevance are:

Revise the E-R data model you created in part A to create a new E-R data model based on the fact that students, faculty, alumni, and mentors are all a PERSON. Use the IE Crow’s Foot E-R model for your E-R diagrams. Justify the decisions you make regarding minimum and maximum cardinality. Note that:

A person may be a current student, an alumnus, or both because Highline University does have alumni return for further study.

A person may be a faculty member or a mentor, but not both.

A person may be a faculty member and an alumnus.

A person may be a mentor and an alumnus.

A current student cannot be a mentor.

Each mentor may be an alumnus, but mentors are not required to be alumni.

Alumni cannot, of course, be required to become mentors.

My current considerations
From my reading I believe the format should be in a supertype/subtype layout. The problem is, the examples I see only have two subtypes for each supertype. So does the question want me to draw out four separate supertype PERSON entities with two choices of subtypes under them?
The ERD I have drawn so far
I have a diagram started but it looks wrong to me in every way. It is displayed below:

Any help or advice would be greatly appreciated.


texts: Microsoft SQL Server 2005 Encryption Options

texts:
I'm trying to explore some encryption options on Microsoft SQL Server 2005.

I've recently got around SQL Server 2008 and was able to enable Transparent Data Encryption (TDE). Now I'm trying to figure out encryption options for SQL Server 2005.

Can someone tell me how to achieve TDE for SQL Server 2005? If that's not possible, can someone point me in the right direction? I also understand that cell level encryption must be done on the application side.

PS: The server edition is Standard Edition. I've been trying to find answers for 2005 for a few hours now but most are for SQL Server 2008.


texts:
I need my code to add the varchar CPUTM field + varchar CPUZIPTIM field which both has time values to see if greater than 2 hours. How do I do this when both fields are varchar. The value in CPUTM field is  '335:55:20.97' duration time.  My code is below.

      CPUTM =     335:55:20.97 duration time
     CPUZIPTM =     0:00:01.96  duration time

select * FROM [SMF_DATA].[dbo].[System_Management_Facility]
WHERE ((convert(varchar(13), CONVERT(time, CPUTM) + CONVERT(time, CPUZIPTM)))
       > '02:00:00.00')

Using SQL server 2012 Enterprise Edition.


texts: mariadb: replication master and slave have equal MySQL server ids

texts:
I'm a novice mariadb user trying to set up a master with multiple slaves using mariadb-10.4.17 on fedora33.
I believe at one point I inadvertently ran the "change master" command on the actual master to be itself in addition to running it on the slaves. It appears that it was running properly for a while, but I've just noticed the following in the logs on the master:
2020-12-17  8:27:57 0 [Note] Reading of all Master_info entries succeeded
2020-12-17  8:27:57 0 [Note] Added new Master_info '' to hash table
2020-12-17  8:27:57 21 [Note] Slave I/O thread: Start asynchronous replication to master 'repuser@havoc.myserver.com:3306' in log 'mysql-bin.000674' at position 465296935
2020-12-17  8:27:59 21 [Note] Slave I/O thread: connected to master 'repuser@havoc.myserver.com:3306',replication started in log 'mysql-bin.000674' at position 465296935
2020-12-17  8:27:59 21 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Internal MariaDB error code: 1593

The "mysql-bin.000674" file does not exist anywhere on the filesystem. I've now stopped the slave, but is there something else I should do?
None of the current slaves are using the same IDs and the server itself is not shown among the lists of slaves.
MariaDB [(none)]> show slave hosts;
+-----------+-------------------------------+------+-----------+
| Server_id | Host                          | Port | Master_id |
+-----------+-------------------------------+------+-----------+
|        22 | armor.myserver.com            | 3306 |       590 |
|        11 | xavier.myserver.com           | 3306 |       590 |
|         2 | mail03.myserver.com           | 3306 |       590 |


texts: Temporarily removing replication from Publisher

texts:
Is it possible to temporarily remove Transactional Replication from a publisher database in order to allow a release to be executed against the database?

If I stopped the log reader agent would I be able to perform DDL/DML changes against the publisher database without error?

The product vendor has a bespoke tool which issues the T-SQL commands against the database for the release, which will include dropping and recreating constraints, keys, indexes etc. I need to make sure releases can happen without error caused by replication - the database needs to allow the changes.

Publisher and Distribution db live on the same server. Both publisher and subscriber are SQL 2014 Enterprise. I am replicating the entire database.

Help is appreciated.
Thanks
Peter

Update: Yes I am replicating schema changes

Update: Is this the answer? https://stackoverflow.com/questions/15673829/resume-replication-after-restoring-publication-database

Update: I've tested our vendor's release on a published database and found that dropping stored procedures is the first issue we see.


texts: relation "(table name)" does not exist in non-public schema

texts:
Windows, pgAdmin, PostgreSQL server hosted on Amazon AWS. Logged in on PgAdmin 4 as admin/owner, accessing an admin created database called 'client'.

I have two identical post-gis enabled tables, one tabled is named 'testboundary' and stored in the public schema, the other is named 'testboundaryNW' and is stored in a new schema called 'NW'.

When I right click the table in public and query this:

SELECT *
FROM testboundary

I get the contents of the table returned as expected.

When I right click the table in the 'NW' schema and query this:

SELECT *
FROM testboundaryNW

I get an error:

ERROR:  relation "testboundarynw" does not exist
LINE 2: FROM testboundaryNW
             ^
SQL state: 42P01
Character: 15

Why is this? Usage/access rights are the same on public and NW schemas.


texts:
I've got this table that is intended to have a lot of activity. Anything that's in it indicates that a user action is still pending. Once deleted, it would indicate that action is no longer pending. Other areas of the system depend on this, so the table on its own doesn't really explain the scenario. However, the question I have is whether I should keep the table as is and delete, when the pending action is done, or whether I should add a flag column in and update.

Note, a record may be deleted in the same second it is inserted. I'm hoping to support up to 100 per second, but would not like to have that as the limit.

I am using SQL Server 2014 Enterprise Edition.

Here's the table's definition (all indexes are based on select queries using this table):

CREATE TABLE [dbo].[OpenRounds](
    [OpenRoundId] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [GameActivityId] [bigint] NOT NULL,
    [VendorId] [int] NOT NULL,
    [Date] [datetime] NOT NULL,
    [UserBonusId] [bigint] NULL,
    [VendorRoundId] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_GamesOpenRounds] PRIMARY KEY CLUSTERED
(
    [OpenRoundId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_GameOpenRoundsUserIdUserBonusId] ON [dbo].[OpenRounds]
(
    [UserId] ASC,
    [UserBonusId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [IX_GameOpenRoundsUserIdVendorIdVendorRoundId] ON [dbo].[OpenRounds]
(
    [UserId] ASC,
    [VendorId] ASC,
    [VendorRoundId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [IX_GameOpenRoundsVendorIdVendorRoundId] ON [dbo].[OpenRounds]
(
    [VendorId] ASC,
    [VendorRoundId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO


texts: What is best way to limit mysql database size? Is it possible to limit database a to 200mb and database b to 300mb?


texts: Access algorithms in Partial Index implementation of PostgreSQL 9.4?

texts:
I am studying big-O time-space complexities of different algorithms with the partial index of PostgreSQL 9.4:

introsort (O(n log n)-time, O(log n)-space) and
heapsort (O(n log(n))-time O(1)-space),

motivated by this thread.
The introsort is using randomized quicksort to mostly sort the array, switching to heapsort if quicksort is to degenerate.
I think the partial index implementation is not using any sorting algorithms with accesses.
To access the data of the partial index is fast when the range is sufficiently small and the number of objects is less than one billion with 32-bit system.
I am interested in how O(n log n)-time, O(log n)-space algorithms can work with the partial index with enough big ranges from the partial index.

How can you estimate suitable sorting/access algorithms in the partial index?


texts: For PostgreSQL database, restrict remote connections to password

texts:
I have recently set-up a PostgreSQL database on client's CentOs machine. Until now the remote access was restricted.

Now I have tried to open the remote connections for us, with following entry in the pg_hba.conf.

host    all             all             x.x.x.x/x      trust

The connection is fine and we are able to connect the db just with providing the user name. Password in not provided while connecting.

Now we need to restrict the access to valid credentials only.

For this I have changed the entry to

host    all             all             x.x.x.x/x      md5

And the postgresql service is restarted (I have restarted multiple times). Still if we try to connect without a password or with a wrong password, the database is connected.

I think I am doing something wrong with the pg_hba.conf.
Any pointers to what is going wrong?

Following is the content of the file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
host    all             all             0.0.0.0/0               trust
# IPv4 remote connections:
host    all             all             x.x.x.x/x      md5
host    all             all             x.x.x.x/x          md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident


texts: Converting unix timestamp to an sql timestamp in postgres

texts:
I have passed Date.now() and other plain unix timestamps like 1534360109480 into sql and ran them through this.

"@converted_date" = to_timestamp("@date") AT TIME ZONE 'UTC';

And it consistently returns 50591-11-28 22:32:38.

I do not understand how the year could possibly be 50591 and the date/time is not accurate beyond that anyways.

What am I doing wrong?


texts: How to properly clean Merge Replication Setup

texts:
What is the proper way to completely clean Merge Replication setup ?

I have tried to delete the subscriptions, then delete the publication then "Disable Publishing & Distribution" & delete the distribution database then started setting up the replication again.

unfortunately, after that I discovered that dbo.msmergesubscriptions table still have old records about old subscriptions I used to have before 2 years !!

Is there any official documentation about how to properly & totally clean Merge Replication setup?


texts: Always On Availability Group, Always redirect user to read only instance

texts:
We have an Always On availability group with a primary and a read-enabled secondary. We have a user for the implementation team who uses the database to check the correctness of the data that they are intending to put in the database.

The users only have rights to read from the database but when they connect (through SSMS) through the AG Listener they always connect to the active node.

I've tried to get them to access the read only instance directly but they're stuck in their ways and after a day or two they're all back on the active node again.

Is there a way for SQL Server to say this user is always going to be with intent readonly and redirect them there?

NOTE: I've tried setting 'ApplicationIntent = ReadOnly' in the additional connection parameters but this doesn't seem to redirect to the secondary node, and isn't the ideal solution as they will inevitably forget to set it up for new starters.

SQL Server 2012 Enterprise, Availability group 1 primary, 1 readable secondary with synchronous commit.

I'm not intending for a user to connect to a linked server, or through any other server. The users connect directly to the database through SSMS (no other application) and I would like the AG Listener (or something around there) to be able to direct that user to a secondary node if one is available (as it only has read access there is no point in it accessing primary) without the user having to do anything, since they move around machines and will forget to add the application intent. Also I find adding that to the additional connection parameters does not always direct you to the secondary node.


texts: What is the usual method to backup only specific columns in table rows with condition in MySQL?

texts:
We have multiple tables which needed to be updated on datetime columns in each, update by adding 1 hour where the datetime value between A and B.
We usually take full backup of DB, so that would be there. But we want to be able to restore only the fields which are modified.
Can we take backup of only the primary keys and the corresponding datetime columns for each table? I am able to generate SQL queries using bash scripting for taking backup into csv. But the mixed usage of SQL and bash doesnt look good. Also, some tables have composite primary keys and it is getting tricky to write script to restore from CSV. Is there any standard method where such updates are usually handled safely?
I am hoping if we could do something like this:

Take backup of to be updated rows with primary keys and corresponding updated column in CSV.
Perform the update ie.., add 1 hour on fields with condition

For restore:

Restore the CSV backup to temp table. (Handle composite primary keys by using a metadata for each table while exporting CSV to temp table)
Update main tables for every row in temp table using primary keys and value from corresponding column in temp table.

But my only concern is the mixed use of bash and sql. If that is what people generally do, then I would stick to this (or may be use python and SQL).


texts:
SQL Server Agent job fails to run SSIS package, but the package runs fine manually
The SQL server is SQL Server 2012.
The job has been working for a long time fine. This is the first time when it failed. We haven’t changed anything.

Error Message:

Executed as user: Domain\SQLServiceAcct. Started:  5:36:22 AM  Finished: 6:56:31 AM  Elapsed:  4809.11 seconds.  The package execution failed.  The step failed.

Information about the job:

It has a proper SSIS Package Credential (we use the service account as identity)
The Job runs as: Proxy SSIS Exec
Package source: SSIS Package Store
Log on the server: Use Windows Authentication (we use service account)
Package: \MSDB\FOO_BAR\name of the job

Is this something to do with the SQL Server agent?


texts: Is it safe to use set-returning sql functions instead of security-barrier views?

texts:
Since 9.2, it's been possible to use the security_barrier attribute to guard against maliciously-chosen functions and operators accessing data 'hidden' behind filters in views (full info in the postgres docs).

You can see this happening in the test below, but the same effect isn't observed with the set-returning function instead of a view at the end of the test.

Is this just a quirk of this individual test or are set-returning functions always a safe1 way to guard against this sort of leak?

testbed:

create schema stack;
set search_path=stack;
create table t(secret) as values (1), (2);

test1:

create view v as select * from t where secret^4>1;
select * from v;
/*
┌────────┐
│ secret │
├────────┤
│      2 │
└────────┘
*/
create function f(integer) returns integer cost 1 language plpgsql as $$
begin
  raise notice 'secret is: %',$1;
  return $1;
end;
$$;
select * from v where f(secret)>0;
/*
NOTICE:  secret is: 1                <-------- SECURITY LEAK
NOTICE:  secret is: 2
┌────────┐
│ secret │
├────────┤
│      2 │
└────────┘
*/

test2:

create function fv() returns setof t language sql as $$
  select * from t where secret^4>1
$$;
select * from fv() where f(secret)>0;
/*
NOTICE:  secret is: 2                <-------- no leak
┌────────┐
│ secret │
├────────┤
│      2 │
└────────┘
*/

clean up:

drop schema stack cascade;

1 There are performance reasons why you might not want to go this route even if it is safe


texts:
i'm trying to use this QUERY which contains a Clob Column (flow.IDFONCTIONNEL)
SELECT
flow.flowid,
min(flow.CONTEXTTIMESTAMP) contextTime,
flow.STATUT,
flow.IDFONCTIONNEL,
flow.ETAT
FROM Flux flow
WHERE  flow.FLOWCODE = 'HELLO'
AND flow.CONTEXTTIMESTAMP BETWEEN '06/01/20 11:36:21,566000000' AND '06/07/20 11:36:21,566000000'
GROUP BY flow.flowid, flow.STATUT , flow.ETAT, flow.IDFONCTIONNEL
ORDER BY contextTime desc

When I run this query, I get the error
ORA-00932: inconsistent data types Expected got CLOB
This is because the column flow.IDFONCTIONNEL is a CLOB data type. If I comment this column from select clause it works fine but I need this column in the output.
I have seen a post telling to try to use DBMS_LOB.SUBSTR to try avoid this problem, so i tryed :
SELECT
flow.flowid,
min(flow.CONTEXTTIMESTAMP) contextTime,
flow.STATUT,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,4000,1) as idf1,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,8000,4001) as idf2,
flow.ETAT
FROM Flux flow
WHERE  flow.FLOWCODE = 'HELLO'
AND flow.CONTEXTTIMESTAMP BETWEEN '06/01/20 11:36:21,566000000' AND '06/07/20 11:36:21,566000000'
GROUP BY flow.flowid, flow.STATUT,flow.ETAT, idf1 ,idf2
ORDER BY contextTime desc

But i get ORA-00904: "IDF2" invalid identifier.
Anyone as an idea of why it is not working ?
Thanks a lot


texts:
I m using SQL server 2012. I have a stored procedure that will eventually be tuned, but I have one question.  My server memory is 32 GB and max memory assigned in SQL Server is 20GB. When I execute this SP, the logical reads is around 4000000, so 4M data pages times with 8kb, divide by 1024, i will get 30 GB worth of data in memory.

My question is how SQL Server can keep 30 GB of data in memory if i only have assigned 20 GB of memory? Might be my question look silly but need your help guys. Thanks


texts: How to optimize table size by deleting columns or just emptying them

texts:
Should I set a column to NULL Or DELETE it to reduce DB Size.
For example in the below image you can see "SmallImageURL" (number 13) should I set it to NULL or delete the column.

( If I delete the column, Application will start throwing errors, Is it worth to delete the column and fix the errors instead of emptying it ?, My concern is only about Reducing DB size and optimizing performance)


texts: Drop privilege to tables only, Not to database

texts:
I created a user with drop privilege to a database.

How can I create a user who has the privilege to drop tables of a specified database, but not database.


texts: Upgrading to SSMS with SQL Web Edition turned off Code Outlining : How do I get it back?

texts:
I upgraded to SQL Server 2008 R2 Web from Express with Advanced Services recently and code outlining went away. How do I get my collapsible code back?

Thanks


texts: Run Server Agent Job on Schedule only if CPU is idle

texts:
I have a job set up in SQL Server Agent to run an update query. I want this job to execute every night between midnight and 5am and I want it to repeat every 5 minutes during that time frame. But I only want the job to actually execute if the CPU is idle, that is the CPU utilization is below say 20%. I know I can set up the schedule, and I can also set up the CPU Idle requirement separately. Is there a way for me to set it up so that both 'schedules' have to be true for the job to run?

Thanks


texts: Creating bacpac failed (DoAllowRowLocks / DoAllowPageLocks)

texts:
I'm creating a bacpac file from a local database for import to Azure SQL Server, but I keep getting an error which I cannot seem to resolve.

Error SQL71564: Element Primary Key has an unsupported property DoAllowRowLocks set and is not supported
    when used as part of a data package.
Error SQL71564: Element Primary Key has an unsupported property DoAllowPageLocks set and is not supported
    when used as part of a data package.

And this error repeats itself for 50 more keys. I have been focussing on this problem for 2 days fulltime now, and I would really like some help on this issue.


texts: How do I join getting one row from the left table, no matter how many matches i get from the right table?

texts:
I have two tables - one is a data table and the other is a mapping table. I want to join them together, but only preserve the data from the left table. However, it is possible that the mapping table may contain multiple records that match to a single record in the data table. I cannot use a DISTINCT because there may be identical rows in the data table, and I want to preserve the same number of rows from that table in the result set.

Here is a sample of the data I am working with:

       DataTable                    MappingTable                 Expected Result
+-----+-----+-----+-----+   +------+------+------+------+   +-----+-----+-----+-----+
| ID1 | ID2 | ID3 | ID1 |   | ID1  | ID2  | ID3  | ID1  |   | ID1 | ID2 | ID3 | ID1 |
+-----+-----+-----+-----+   +------+------+------+------+   +-----+-----+-----+-----+
|  1  |  1  |  1  |  1  |   |  1   | NULL | NULL | NULL |   |  1  |  1  |  1  |  1  |
|  1  |  1  |  1  |  1  |   | NULL | NULL | NULL |  1   |   |  1  |  1  |  1  |  1  |
|  2  |  1  |  1  |  1  |   |  3   |  3   | NULL | NULL |   |  2  |  1  |  1  |  1  |
|  3  |  1  |  1  |  3  |   +------+------+------+------+   |  2  |  2  |  1  |  1  |
|  4  |  1  |  1  |  4  |                                   |  3  |  3  |  1  |  3  |
|  2  |  2  |  1  |  1  |                                   |  2  |  1  |  0  |  1  |
|  3  |  2  |  1  |  3  |                                   |  2  |  1  |  0  |  1  |
|  3  |  3  |  1  |  3  |                                   +-----+-----+-----+-----+
|  2  |  1  |  0  |  1  |
|  2  |  1  |  0  |  1  |
|  4  |  3  |  2  |  3  |
+-----+-----+-----+-----+

Below is the join I am using. I wrote a custom function to handle the NULL-matching behavior, which I am including here as well.

SELECT * FROM DataTable P
JOIN MappingTable M ON dbo.fNullMatchCheckIntS(P.ID1,M.ID1,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID2,M.ID2,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID3,M.ID3,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID4,M.ID4,0,1) = 1

CREATE FUNCTION dbo.fNullMatchCheckIntS (
    @Value1 INT
    ,@Value2 INT
    ,@AutoMatchIfValue1IsNull BIT
    ,@AutoMatchIfValue2IsNull BIT
)
    RETURNS BIT
AS

BEGIN

    DECLARE @Result BIT = 0

    SELECT
        @AutoMatchIfValue1IsNull = ISNULL(@AutoMatchIfValue1IsNull,0)
        ,@AutoMatchIfValue2IsNull = ISNULL(@AutoMatchIfValue2IsNull,0)

    IF
        (@AutoMatchIfValue1IsNull = 1 AND @Value1 IS NULL)
        OR (@AutoMatchIfValue2IsNull = 1 AND @Value2 IS NULL)
        OR @Value1 = @Value2
        OR (@Value1 IS NULL AND @Value2 IS NULL)
    BEGIN
        SET @Result = 1
    END

    RETURN @Result
END

The problem with the way the join works is that the first two rows in the DataTable match on the first two rows in the MappingTable, giving me four identical records in the result, but I only want 2. I know that I could add an identity column to the DataTable and then use DISTINCT or PARTITION to get the result I am looking for, but I would like to avoid that route if possible.

EDIT: I figured out a way to do this using EXISTS, but it looks a little ugly in my opinion. Still interested in other answers if anyone has an idea. Thanks!

SELECT * FROM DataTable D
WHERE EXISTS (
    SELECT D.ID1, D.ID2, D.ID3, D.ID4
    FROM MappingTable M
    WHERE dbo.fNullMatchCheckIntS(D.ID1,M.ID1,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID2,M.ID2,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID3,M.ID3,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID4,M.ID4,0,1) = 1
)


texts: Running an "ORDER BY CASE" query on MySQL view breaks it afterwards

texts:
Whenever I create a MySQL view and then run a query ordering the results with ORDER BY CASE WHEN (...), the view becomes unusable.

The newly created view works properly and all SQL is correct.

This is the error message I get when I try to view all records from the view (or when I click on "Browse" in phpMyAdmin):

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'THEN0 ELSE1 END LIMIT 0, 30' at line 1

Here is example schema:

CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title` varchar(256) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
INSERT INTO `comments` (`id`, `user_id`, `title`) VALUES
(1, 1, 'user one comment 1'),
(2, 1, 'user one comment 2'),
(3, 2, 'user two comment 1'),
(4, 2, 'user two comment 2');

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `users` (`id`, `name`) VALUES
(1, 'user1'),
(2, 'user2');

Sample view:

CREATE VIEW view_sample AS
SELECT
  c.id AS comment_id,
  c.title AS comment_title,
  u.name AS user_name
FROM comments c, users u
WHERE c.user_id = u.id

At this point you can verify that the view above works properly and shows results.

Sample query that will break everything:

SELECT * FROM view_sample
WHERE comment_title LIKE 'user one%'
ORDER BY CASE WHEN comment_title LIKE 'user one%' THEN 1 ELSE 0 END, comment_title

Query above will work, but whatever you try to do afterwards will fail. Try simply this:

SELECT * FROM view_sample

I tried this several times, with different views.

What am I doing wrong?


texts: Data Synchronization from Remote MS SQL Server

texts:
I have a client server on which my user has limited permissions (i.e. SELECT only for example). I need to select some data from that server and insert into my local database.

This has to be a continuous activity on a daily basis.
I have tried OPENQUERY and Linked Server but there are permission issues.

Is there any other way to do this? My last resort is csv download but is there any other way to do this?


texts:
I am looking at rewriting a VB based on-premise (locally installed) application (invoicing+inventory) as a web based Clojure application for small enterprise customers. I am intending this to be offered as a SaaS application for customers in similar trade.

I was looking at database options: My choice was an RDBMS: Postgresql/ MySQL. I might scale up to 400 users in the first year, with typically a 20-40 page views/ per day per user - mostly for transactions not static views. Each view will involve fetch data and update data. ACID compliance is necessary(or so I think). So the transaction volume is not huge.

It would have been a no-brainer to pick either of these based on my preference, but for this one requirement, which I believe is typical of a SaaS app: The Schema will be changing as I add more customers/users and for each customer's changing business requirement (I will be offering some limited flexibility only to start with). As I am not a DB expert, based on what I can think of and has read, I can handle that in a number of ways:

Have a traditional RDBMS schema design in MySQl/Postgresql with a single DB hosting multiple tenants. And add enough "free-floating" columns in each table to allow for future changes as I add more customers or changes for an existing customer. This might have a downside of propagating the changes to the DB every time a small change is made to the Schema. I remember reading that in Postgresql schema updates can be done real time without locking. But not sure, how painful or how practical is it in this use case. And also, as the schema changes might also introduce new/ minor SQL changes as well.
Have an RDBMS, but design the database schema in a flexible manner: with a close to entity-attribute-value or just as a key-value store. (Workday, FriendFeed for example)
Have the entire thing in-memory as objects and store them in log files periodically.(e.g., edval, lmax)
Go for a NoSQL DB like MongoDB or Redis. But based on what I can gather, they are not suitable for this use-case and not fully ACID compliant.
Go for some NewSQL Dbs like VoltDb or JustoneDb(cloud based) which retain the SQL and ACID compliant behaviour and are "new-gen" RDBMS.
I looked at neo4j(graphdb), but not sure if that will fit this use-case

In my use case, more than scalability or distributed computing, I am looking at a better way to achieve "Flexibility in Schema + ACID + some reasonable Performance". Most of the articles I could find on the net speak of flexibility in schema as a cause leading to performance(in the case of NoSQL DBs) and scalability while leaving out the ACID/Transactions side.

Is this an "either or" case of 'Schema flexibility vs ACID' transactions or Is there a better way out?


texts:
I am setting up Postgres binary replication.

In my slave's recovery.conf, I am suggested to have 2 options.

I am wondering if the directory specified in restore_command and archive_cleanup_commands are referring to directory paths in the master OR the slave?

restore_command = 'cp -f /home/postgres/data/wals/%f %p </dev/null'
archive_cleanup_command = '/usr/pgsql-9.2/bin/pg_archivecleanup /home/postgres/data/wals/ %r'

For instance, in restore_command is /home/postgres/data/wals referring to the path in the master?
Likewise for archive_cleanup_command?

The reason I am asking is because the paths are not similar between the 2 servers.


texts: How to monitor progress of Oracle Materialized View

texts:
I have created a materialized view in Oracle 12.1 database. The master table has materialized view log created, so that I can do a fast refresh.

I have executed the refresh using DBMS_MVIEW.REFRESH, but it is taking too much time.

I have verified network connectivity between two databases.

How can I troubleshoot the refresh process ?


texts: Remote access to Database PostgreSql on NAS Synology DS216jv

texts:
Hi I have a Synology NAS DS216j in my LAN and I am running a postgreSQL database on it.

I am try to find a way to connect and run my database from a computer outside my LAN so I can use it and update it. In order to do that I had to forward the port 5432 (postgres) and WebDav (5005) of my router to the NAS.

The port Postgresql (5432) and WebDav (5005 )is open on my public IP. The port Postgresql (5432) and WebDav (5005 )is open on the NAS.

From the remote host:
I am able to see the database file (in LibreOffice Base), on the NAS if I connect with quickconnect or WebDav (in windows I installed the program: NetDrive).

I am able to connect to the database structure with PgAdminIII.
but I am not able to Launch/Open the database.
When I get prompt the user and password I just get this error saying:

  The connection to the data source "NAS" could not be established.
  Couldnt establish database connection to ´sdbc:postgresql:postgresql://192.168.0.20:5432/dbname´ could not connect to the serve: Connection timed out (0x0000274c/10060). Is the server running on host 192.168.0.20 and accepting TCP/IP connection on port 5432?

I wonder why I am able to connect with PgAdminIII but not able to open the LibreOffice Base from WebDav.

----UPDATE ----


texts: Reporting Services report that runs quick locally and in development, but not in production

texts:
I have a Reporting Services report with the following characteristics.

The stored procedures used run fast in SSMS.  This is true in production and development databases.
The report renders quickly in Visual Studio.  This is true if it pointed to production or development databases.
The report renders quickly when deployed to production server and connected to the development database.
The report renders super slow when deployed to development server and connected to the Production database.
All other reports are running fine.
A restart to the Reporting Services service on production resolved the problem.

I am stumped as to why this fixed it and if there is a solution to correct without the restart.

Update: In looking at ReportServer.dbo.ExecutionLog3 I see the TimeDataRetrieval jumps from a few hundred ms to over 700k ms. This drops back down to a few hundred after a restart to the Reporting Services service. It ran a few dozen times over the next few days at a few hundred ms before jumping up to over 700k ms again. I've found a few queries used in this report that have multiple plans that I'll be following up on.

Update 2: The report ran ~350 times over the next 10 days with run times around a second. This weekend it spiked back up over 12 minutes to run. The ByteCount and RowCount in ExecutionLog3 stayed steady. Restarting the Reporting Services service caused the run time to drop back down to a second.


texts: Frequency distributions segmented by a field

texts:
Some background:
I have a sample population data file. Each record in the data file has a frequency weight (FIELD NAME: wgt) indicating how many times the records needs to be replicated to get the true population. The data is set up in Microsoft SQL 2008 R2. It is usually a good practice to generate frequency distribution of the weights to see if one particular type of record is being over-represented in any way - thus help identify trends/ outliers. This task is straightforward in SQL:

SELECT wgt, COUNT(*) FROM tablename
GROUP BY wgt

Challenge:
I want to further segregate these frequencies based on other values. Say a frequency distribution of weights for different household sizes. One way to achieve this is to use different where conditions in the statement above:

SELECT wgt, COUNT(*) FROM tablename
WHERE household_size=x --x being the desired segment
GROUP BY wgt

But is there any way to create a single table with all of the different segments? Something like this:

WGT |   SIZE1       SIZE2       SIZE3       SIZE4
--------------------------------------------------
1   |    2,034      1,025       502         234
2   |    215        253         142         23
3   |    31         25          21          34
4   |    7          1           3           7
5   |    5          NULL        2           5
6   |    1          1           NULL        NULL
7   |    NULL       1           NULL        NULL

A fancier solution I am looking for: A stored procedure where I can just specify the variable (say household size or household income) and the code should be able to generate separate frequency distributions for all distinct values for that variable as a view/table.

Any thoughts folks?


texts: Trouble deciding what/how many tables to use; normalisation suggests one

texts:
More of a conceptual question for the community, as I am not "thinking with databases" as much as I would like to just yet. I am using Oracle Database 18c Express Edition with SQL Developer.

I have been asked to build a database to log sales in a shop. The things to log are;

Date
Day
Time
Value [grouped, either Low, Medium, or High, rather than absolute monetary value]
Location [on a (-5,-5) to (5,5) grid]

If we throw in an incremental UID as the Primary Key for each sale, I can't see why I would need any more than the one table to do this. I need one entity, Sale, and it would consist of six attributes (the UID and the list above). It would have a primary key, no repeating groups, no composite PK, and no non-key fields dependent on another non-key field.

Queries I have been asked to generate (which I don't need help with, yet, but might influence the design) are;

Find quietest day
Find busiest hour
Find highest income location
Generate a report on sales by value range
Follow on report of purchases by hour in each value range
Identify locations with no income

There are obviously some constraints I need to deal with (shop isn't open Sundays, only open between x and y hours, escalator to get to the floor is at (0,0), etc), but these are relatively simple and I am confident that some more reading and tweaking will get me there. Technical problems are much easier to work though, in my experience.

My real drama comes from trying to come up with the database design. Normally I would expect normalisation to do most of the grunt work for me, directing me to where I need to make new tables to separate off different attributes in to their own entities. The task specifies data needs to be 3NF (and then also goes on to say there needs to be no normalising, confusingly). I am reading through "Systems Analysis : A Beginner’s Guide" by Kevin Bowman as my main point of reference, and based on my understanding of it, the data I am expecting to log is already 3NF, and doesn't need more than the one table.

This seems an awful lot too simple, and I suspect I am simply not getting it at some point in the process. A colleague has said he plans on making a table for each attribute, which just seems mad and unnecessary to me.

Does anyone have any thoughts, or see any issues with my reasoning?


texts: SQL Server Merge Replication Dummy Update issue

texts:
We have SQL Server 2005 SP4, using merge replication.

We started getting this error when the subscribers try to replicate (not all of them but most):

  The process could not deliver dummy update at the 'Publisher'. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200993)

We tried to 'reinitialize' one of the subscribers but that doesn't help. It starts applying the schema and seems do that fine, but when it comes time to download new changes it gets the error.


texts: How to have PostgreSQL running forever with constant insert and delete per day

texts:
Question:

Lots of rows are getting inserted every second. Batch deletion is performed when the disk is close to full. This causes two problems:

It is impossible to examine actually how much space in how many pages are reusable. All I see is the disk is almost full, because the database does not return disk space to OS after deletion.
Fragmentation happens. The disk usage slowly grows, and eventually becomes full, crashing the machine.

Solutions I have considered:

Monitor disk usage, and perform a VACCUM FULL when the disk is 48% full. But reserving 52% (yes, the disk is mostly occupied by database) of the disk just to delete records sound very wasteful.
Partition my tables by hour of record creation time. Only delete old partitions and hope that the deleted paritions free up complete pages and so autovacuum will return disk space of those completely empty pages.
Use a on-file database like SQLite. Separate data into files by hour. Only load the most updated 24 hours unless otherwise specified. When I need to delete, just delete the files. I haven't tried this, but it sounds like lots of overhead to attach all the 24 databases every hour.

I really appreciate any suggestion. Below are the additional details. Many many thanks!

Context:

I am currently using PostgreSQL to host a streaming of live social media posts. There is a streamer that perform batch inserts, and a middleware that perform purely-read queries for client requests. The whole application is delivered using Docker image.

As the streamer inserts records, some day the disk will be full. So we need to delete records. But deleting records doesn't reclaim disk space and can cause fragmentation. So ultimately a VACCUM FULL is needed to compact the tables again.

Requirement:

The users who deploy this app is non-technical. They don't even know what is a shell/terminal.
The users will perform no maintenance for the database. So VACUUM FULL is an option only when the disk have enough space for the new tables.


texts: Mysql Server Login and Permission Deny Error

texts:
I have a problem with MySQL server. After upgrading MySQL server from 5.3 to 5.4, I am not able to log-in using my MySQL root password (Yes, I am able to log-in without password!).

Also not able to grant permissions to users as well. Not able to change in databases using root account, while taking backup of database due to same.

When I looked into MySQL databases using root account, it doesn't show anything. While on other server, I am able to find all entries.

Kindly please help me to resolve this issue as it is critical.

Log of mysql is as below.

130906 18:46:13 [Warning] Found invalid password for user: 'root@x.x.x.x'; Ignoring user
1843 130906 18:46:13 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
1844 130906 18:46:14 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
1845 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
1846 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
1847 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
1848 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
1849 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
1850 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
1851 130906 18:46:14 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
1852 130906 18:46:14 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
1853 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
1854 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
1855 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
1856 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
1857 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
1858 130906 18:46:14 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
1859 130906 18:46:14 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
1860 130906 18:46:14 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
1861 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
1861 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
1862 130906 18:46:14 [ERROR] Column count of mysql.db is wrong. Expected 22, found 13. Created with MySQL 50045, now running 50532. Please use mysql_upgrade to fix this error.
1863 130906 18:46:14 [ERROR] mysql.user has no `Event_priv` column at position 29
1864 130906 18:46:14 [ERROR] Cannot open mysql.event
1865 130906 18:46:14 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
1866 130906 18:46:14 [Note] /usr/libexec/mysqld: ready for connections.
1867 Version: '5.5.32-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Atomicorp


texts: How to determine Windows Server start time using T-SQL

texts:
I am looking for a way to find out when the Windows Server was last started using only t-sql commands.

I am trying to stay within the default security configurations (i.e. I don't want to enable xp_cmdshell)


texts: Is it worth duplicating a table for different types of an entity when one should be unique?

texts:
In my database, I have a CUSTOMER table. I'm migrating an OPPORTUNITIES table, which contains two different types of opportunity, each having identical columns (e.g. status, reason lost, probability of success, etc.). One such type is the opportunity of gaining a new customer. The other such type is the possibility of a sale to an existing customer.

I see two possible ways to do this, and am uncertain which is better (or if there's a third, better way).

Option A:
Migrate into two separate tables: POSSIBLE_CUSTOMER and POSSIBLE_SALE. Both tables would have the same columns, including a foreign key to CUSTOMER. POSSIBLE_CUSTOMER's foreign key would have a unique constraint on it.

Option B:
Migrate into a single table, with a column to determine type. This way we're not duplicating the table's columns as above... but there's (to the best of my knowledge) no way to ensure that there's only ever at most one possible customer per customer.


texts: NoSQL, CouchDB vs. CouchBase, What should i do?

texts:
I started to encounter some issues on my production site, when having a web page that needs to load a very big ResultSet (currently coming from relational database, MySQL) it takes forever, and problem these result sets are only getting bigger and bigger.

I started to seek after a better solution, and what I came across with is the idea of saving the data in a NoSQL database. (I'm already using Mongo, but Mongo is inefficient due to high amount of DML in my environment.) So when searching the web I thought about the following 2 options:

CouchDB
CouchBase

When looking at both of the above, I can tell say that both are JSON document based (ok, that's a good start), but when getting into some technical background I do look for better caching (I don't wan't to kill my server's I/O) then MongoDB's master-master replication ability (I saw that CouchDB can replicate easily based on source->destination / destination->source).

Can someone provide me some of your input, and if you had tried the above solutions I will be happy to hear about your experience.


texts: Importing from CSV file, is crashing mysql server

texts:
I am facing a problem with importing large data into single mysql table.
Basically I assume it is happening because limited machine resources but,
I am having troubles to proof it.

this is my CSV file information:

root@my:~# tail -n 1 /tmp/keys.csv
ffff7771-e330-9d8e-6783-daf8a75fe4ef,7
root@my:~# wc -l /tmp/keys.csv
162281544 /tmp/keys.csv

So basically I have 162,281,544 lines of

ffff7771-e330-9d8e-6783-daf8a75fe4ef,7

in my CSV and its file size is 6G

I need to import this table on a daily basis and to use it only for SELECT information by the KEY eg:

SELECT uuid_tags_codes FROM keys WHERE uuid = 'ffff7771-e330-9d8e-6783-daf8a75fe4ef';

I choose MyISAM as my table engine because I read that it should be better from selecting information.

CREATE TABLE `keys` (
  `uuid` varchar(60) NOT NULL,
  `uuid_tags_codes` int(11) NOT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

After all this Information and I really hope it is clarifying all my technical stuff, If you think I missed something I will be happy to provide more details as you wish.

The problem is when I am using load data in file command as below:

LOAD DATA LOCAL INFILE '/tmp/keys.csv'
INTO TABLE key
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(uuid,uuid_tags_codes);

After I am executing this command it mysql is running but it is unresponding and I have to delete the table from filesystem and restart it,
Also I think the system have no enough data storage 10G free space in total before importing the file.
But this is OK I always can Improve the server machine, by extending the SSD size.

Here are my questions:

In case I extend my SSD drive should this process be OK, is mysql single table should be able to handle 6-8G data of 2 cols?
Is Mysql is the right solution to my case, or should I use different DB server for the pulling this info?
In case using mysql is ok, is my table definition is ok for the data I am storing in it? and what type of indexing should I use?


texts: Why is it important to carefully determine and select statistics for the query optimization process?

texts: It's a general question rather than a db engine specific one. In popular db engines, we see the query optimization process taking statistics into consideration. I'm trying to understand what types of statistics the optimization function is using and on what basis these statistics are selected and used.


texts: Indexes: integer vs string performance if the number of nodes is the same

texts:
I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is searching for very specific attributes on a model.

I am currently deciding whether to use an integer type or simply use a typical string type (e.g. character varying(255), which is the default in Rails) for the columns, as I'm not sure what the performance difference will be on the index.

These columns are enums. They have a fixed size for the amount of possible values they can have. Most enum lengths do not exceed 5, meaning the index would be more or less fixed throughout the lifetime of the application; thus, the integer and string indexes would be identical in the number of nodes.

However, the string that would be indexed could be around 20 characters long, which in memory is roughly 5x that of the integer (if an integer is 4 bytes, and the strings are pure ASCII at 1 byte per character, then this holds). I don't know how database engines do index look-ups, but if it needs to "scan" the string until it matches exactly, then in essence that means that the string lookup would be 5x slower than an integer lookup; the "scan" until match for the integer lookup would be 4 bytes instead of 20. This is what I'm imagining:

The lookup value is (integer) 4:

  scanning............................ FOUND | getting records...
  |BYTE_1|BYTE_2|BYTE_3|BYTE_4|BYTE_5|BYTE_6|BYTE_7|BYTE_8|...|

The lookup value is (string) "some_val" (8 bytes):

  scanning.....................................................................................
  FOUND | getting records...
  |BYTE_1|BYTE_2|BYTE_3|BYTE_4|BYTE_5|BYTE_6|BYTE_7|BYTE_8|...|

I hope that that makes sense. Basically, because the integer takes up less space, it can be "matched on" faster than its string counterpart. Perhaps this is a completely wrong guess, but I'm no expert, so that's why I'm asking you guys! I suppose that this answer I just found seems to support my hypothesis, but I want to be sure.

The number of possible values in the column wouldn't change in using either one, so the index itself would not change (unless I added a new value to the enum). In this case, would there be a performance difference in using integer or varchar(255), or does using an integer type make more sense?

The reason I am asking is that Rails' enum type maps integers to string keys, but they aren't meant to be user-facing columns. Essentially, you can't do verification that the enum value is a valid one, because an invalid value will cause an ArgumentError before any validations can be run. Using a string type would allow validations, but if there's a performance cost I'd rather just hack away around the validation problem.


texts:
we have a SQL Server DB running on Amazon RDS, i need to refresh it. But in RDS there is no option to restore a backup. What other ways i can use to refresh this DB? It is 60 GB, and has 200 tables.

Thanks


texts: What does it mean that sp_who2 is not documented?

texts:
I am reading a book about SQL Server administration where I encountered this sentence

  sp_who2 is undocumented stored procedure which gives you more verbose information about each processes (in comparison with sp_who).

What does it mean that sp_who2 is undocumented? Is it deprecated or is the use of it not recommended?

Thanks for your time.


texts:
Many user forms contain an option for "Other - Add Comment" where the user can provide an item not found in the given list.

In other words, the developer sets the list of items. The user must either select an item or type a value.

Examples:

How did you hear about us? {Web, TV, Friend, Radio, other}
Reason for contacting us?  {Sales, Tech Support, Customer Service, other}
Select your printer model?

Proposed schema:

model:     model_id (PK), model_txt (UK), show (bool)

request: request_id (PK), model_id (FK), {other non-relevant fields}

Display models where show = 1

On insert,

If model selected from list, use its model_id
If model typed,

If model_txt exists, use existing model_id
Else, insert new model_txt and set show = 0

Likes:

This solution avoids NULL values and empty strings
Obtaining model_txt is the same for all requests regardless of if the user typed the entry.

Dislikes:

The model table will be polluted with items only tied to one request.
The insert model_txt requires an extra step before the INSERT command.

How can I improve this schema?