texts:
I am very new to MongoDB and am trying to do database sharding.

I have created two MongoDB shards and inserted 10000 records in it. I have used shard key as "no" which increments by 1 in every document in ascending order.

mongos>sh.addShard("localhost:27004",{"no":1})
mongos>sh.addShard("localhost:27005",{"no":1})

Here, when I insert the records, the shard 1 gets only 6 records and shard 2 gets the rest of the records.

The output of sh.shatus() is:

 databases:
    {  "_id" : "shard_test_2",  "primary" : "shard0000",  "partitioned" : true }
            shard_test_2.test
                    shard key: { "no" : 1 }
                    unique: false
                    balancing: true
                    chunks:
                            shard0000       2
                            shard0001       1
                    { "no" : { "$minKey" : 1 } } -->> { "no" : 1 } on : shard0000 Timestamp(2, 1)
                    { "no" : 1 } -->> { "no" : 6 } on : shard0000 Timestamp(1, 2)
                    { "no" : 6 } -->> { "no" : { "$maxKey" : 1 } } on : shard0001 Timestamp(2, 0)

Now, I want my shard one to store more documents than 6.

I read that how many documents will go to which shard is the function of balancer. So, how do I configure the balancer?

Or is there any other way of setting how much data should go to which shard?
No resource online is helping much in this.


texts: The Mysql database vanished and there is no more user

texts:
I have an ubuntu production server running Mysql, i changed the datadir to this :

user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /srv/datadisk02/dbs
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
innodb_use_sys_malloc = 0

The db was created like this :

CREATE DATABASE sport;CREATE USER admin; SET PASSWORD FOR admin = PASSWORD('*****');GRANT ALL PRIVILEGES ON sport.* TO admin@localhost IDENTIFIED BY '****'; exit;

After that the credential were given to Wordpress, I did not interact directly with mysql. All of this was done a week ago, and everything worked perfectly until now.

Since a few hours the mysql is empty. The admin user does not exist anymore, and the db has vanished.

I have mysql logs at /var/log and /var/log/mysql/ and they are all empty except /var/log/mysql/error.log :

150310  8:29:35 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
150310  8:29:35 [Note] Plugin 'FEDERATED' is disabled.
150310  8:29:35 InnoDB: The InnoDB memory heap is disabled
150310  8:29:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150310  8:29:35 InnoDB: Compressed tables use zlib 1.2.3.4
150310  8:29:35 InnoDB: Initializing buffer pool, size = 128.0M
150310  8:29:35 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
150310  8:29:35  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
150310  8:29:35  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
150310  8:29:35  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
150310  8:29:36  InnoDB: Waiting for the background threads to start
150310  8:29:39 InnoDB: 5.5.41 started; log sequence number 0
150310  8:29:39 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
150310  8:29:39 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
150310  8:29:39 [Note] Server socket created on IP: '127.0.0.1'.
150310  8:29:39 [Note] Event Scheduler: Loaded 0 events
150310  8:29:39 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.41-0ubuntu0.12.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
150310  9:16:03 [Note] /usr/sbin/mysqld: Normal shutdown

150310  9:16:03 [Note] Event Scheduler: Purging the queue. 0 events
150310  9:16:03  InnoDB: Starting shutdown...
150310  9:16:04  InnoDB: Shutdown completed; log sequence number 1595675
150310  9:16:04 [Note] /usr/sbin/mysqld: Shutdown complete

What happend? what can i do? is my work all gone?


texts:
How can I group (by) two joined tables (in an Oracle database) like this:

cant:
id      CANT_VAL    COD_VAL fk_id_chest
18059   18.56       R12     1
18060   11          R10     2
18061   15          R11     3

col:
FK_ID_COL_DMA   CANT_VAL    COD_VAL
18059           1134        R10
18059           1234        R3
18061           1111        R5

to look like that:

id      CANT_VAL    COD_VAL    fk_id_chest
18059   18.56       R12        1
18059   1134        R10        1
18059   1234        R3         1
18060   11          R10        2
18061   15          R11        3
18061   1111        R5         3

fk_id_chest is a "link" to another table that I need in the final result.
Thanks!


texts:
Let's assume this is my current data:

product_number  date         value
100             2010-01-01   1
100             2010-02-01   1
100             2010-03-01   1
200             2010-01-01   1
200             2010-03-01   1

How can I join the table on "itself", to fill the dates where other products have missing dates (compared to all the results, that change all the time)?

As you can see, product #200 doesn't have a 2010-02-01 date and I need to get a NULL value there.

Expected result:

product_number  date         value
100             2010-01-01   1
100             2010-02-01   1
100             2010-03-01   1
200             2010-01-01   1
200             2010-02-01   NULL # add NULL values where it doesn't have a date
200             2010-03-01   1

Basically: get a list of all distinct dates, find products where that date doesn't exist and simulate an entry with a NULL or empty value to fill the gap


texts:
My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish and product (can be different).

SELECT count(*) as daySum,
    p_name,
    SUM(pp_price) as daySumPrice,
    o_day,
    o_time,
    s_id,
    s_name,
    t_name,
    t_id,
    SUM(ust10) as ust10,
    SUM(ust20) as ust20,
    SUM(sdUst10) as sdUst10,
    SUM(sdUst20) as sdUst20 ,
    fk_p_id,
    u_name
FROM
(
    SELECT tbl_orders.o_id,
        tbl_orders.o_comment,
        DATE_FORMAT( $dateField,  '$sqlDateFormat' ) AS o_day,
        tbl_orders.o_time,
        tbl_orders.o_t_name AS t_name,
        tbl_orders.fk_t_id AS t_id,
        tbl_orders.fk_u_id AS u_id,
        tbl_orders.fk_s_id AS s_id,
        tbl_orders.o_s_name AS s_name,
        tbl_orders.o_u_name AS u_name,
        tbl_orders.fk_p_id,
        CONCAT(tbl_orders.o_p_name,IF(sideDish.p_name IS NULL,'',' ('),IFNULL(GROUP_CONCAT(DISTINCT(sideDish.p_name) SEPARATOR ', '),''),IF(sideDish.p_name IS NULL,'',')'),IF(tbl_orders.o_comment='','',' - ".getLanguageKey('label_comment').": '),tbl_orders.o_comment,IF(tbl_orders.o_comment='','','')) as p_name,
        ROUND(IFNULL(tbl_orders.o_p_price,0)+IFNULL(sideDishPrice.pp_price,0),2) as pp_price,
        IF(tbl_orders.o_p_ust=10,IFNULL(tbl_orders.o_p_price/(100+tbl_orders.o_p_ust)*tbl_orders.o_p_ust,0),0) AS ust10,
        IF(tbl_orders.o_p_ust=20,IFNULL(tbl_orders.o_p_price/(100+tbl_orders.o_p_ust)*tbl_orders.o_p_ust,0),0) AS ust20,
        sideDishPrice.ust10 AS sdUst10,
        sideDishPrice.ust20 AS sdUst20
    FROM tbl_orders
    INNER JOIN tbl_dailyReport dr1
        ON dr1.dr_id = tbl_orders.fk_dr_id
    LEFT JOIN
    (
        SELECT  o2.o_id,
            SUM(IFNULL(o2.o_p_price,0)) as pp_price,o2.o_p_name as p_name,o2.o_parent AS o_parent,
            SUM(IF(o2.o_p_ust=10,IFNULL(o2.o_p_price/(100+o2.o_p_ust)*o2.o_p_ust,0),0)) AS ust10,
            SUM(IF(o2.o_p_ust=20,IFNULL(o2.o_p_price/(100+o2.o_p_ust)*o2.o_p_ust,0),0)) AS ust20
        FROM tbl_orders o2
        GROUP BY o_parent
    ) sideDishPrice
        ON tbl_orders.o_id = sideDishPrice.o_parent
    LEFT JOIN
    (
        SELECT o_id,o_p_name AS p_name,o_parent
        FROM tbl_orders
        GROUP BY o_id

        ) sideDish
            ON tbl_orders.o_id = sideDish.o_parent
WHERE
                    tbl_orders.fk_c_id = '$c_id'
                    $stornoNull

                    AND tbl_orders.o_parent IS NULL
                    $onlyDayClosingData
                    $whereClauseTime
                    $whereClauseID
                    $whereClauseStorno
                        GROUP BY  tbl_orders.o_id,tbl_orders.o_p_name,sideDish.o_parent

                    ) products  $groupBy $orderBy;

The query is working and it gets the right numbers but it takes too long.

rows in database: 7000 orders in march: 3500 time to print 6 of these queries with different group by's (date, waiter, table, products, payment method, and cancellations,...): about 30-40 secs.

Imagine how long it would take if we have 10000000 of rows (which could be realistic in a few years?)

Is there any way to improve this performance?

EDIT:
I already solved it using a second table.
As the table tbl_orders is used for the orders itself (recursive, for orders with sidedishes) i just put it joined into a new table tbl_report. There it is possible now for me to group things like i want them with good speed :)

Thank you for your advices. Some of them were helpful though :)

how should i mark my question? solved?


texts: Optimise the selection of two distinct column pairs from the same table

texts:
I need help with rewriting or editing the query/logic to improve it's performance. SQL Server 2014

I have the table structure specified below. I want to select two column pairs:

(SourceID + SourceName) and (TargetID + TargetName)

The query needs to return all distinct ID and Name pairs within each GroupID.

  Take the following as an example,

GroupID    SourceID    SourceName    TargetID    TargetName

1          0           'ant'         2           'dog'
1          1           'bat'         2           'elk'
1          2           'dog'         3           'fox'
1          3           'fox'         4           'pig'
1          4           'pig'         5           'ram'
1          5           'ram'         6           'owl'
...

  As such the result would be:

GroupID    LinkID    LinkName

1          0         'ant'
1          1         'bat'
1          2         'dog'
1          2         'elk'
1          3         'fox'
1          4         'pig'
1          5         'ram'
1          6         'owl'

  I was able to achieve the results using the following logic

SELECT GroupID AS GroupID
  , SourceID   AS LinkID
  , SourceName AS LinkName
FROM TABLE
UNION
SELECT GroupID AS GroupID
  , TargetID   AS LinkID
  , TargetName AS LinkName
FROM TABLE


texts:
Is there any SQL query to get the syntax of an existing table in my MySql database?

Intent:
Iam in the early stages of working with databases and I am using the MySql workbench for creating tables and updating them whenever needed. At some point there are cases where I would like to have the syntax that the table is now holding so as to document it or paste it in forums like StackOverflow(expecting that I can show all the constraints in fact the foreign key constraints on the table in an easy way).

Please let me know if there is any workaround for this.


texts:
I am currently creating a web application using the twitter streaming API and would need some advice concerning my database scheme.

I basically have a list of hashtags that I track in real-time. All the tweets having that hashtag are stored in a table.

Thing is that the table is getting quickly huge, and it is far from efficient to crawl it afterwards (to get the list of tweets for a given user for example).

Just for information, I got something like 700.000 tweets crawled in less that 24 hours.
This was for 5 hashtags, but I would like to scale up to a hundred at least.

I store them all and then populate another table of unique users out of it, which is transformed into a leaderboard.

So I was considering adding indexes, using the hashtag as a key.
But I was also considering separating the tweets in several tables, one per hashtag in order to have a less juge amount of data to crawl.

The table would be created at runtime when a new hashtag is added.

Am I going the right way with this, or is that a bad practice?

Or is Mysql just a bas idea and I should go for another technology?


texts: SQL Server 2014 - Give user permission to run sp_refreshview

texts:
I need to give a user the required permission to run sp_refreshview for a specific database on our server.  I've tried adding the user to the db_executor role but that only gave them access to certain stored procedures.

Am I looking to accomplish something that is impossible for SQL Server permissions or am I just looking in the wrong places?  Thank you in advance for any guidance you can provide.


texts:
I have a table of hierarchical data like this:

BookGroupID  GroupName  ParentGroupID
-----------  ---------  -------------
0            Primary    0
1            abc        0
2            abd        0
3            abe        0
4            cbc        1
5            ceg        2
6            cjd        3
7            hjd        5
8            bjy        5
9            mfs        4
10           ikl        7
11           hjy        8
12           mnf        5
13           aws        10
14           qws        11
15           aqe        13

I want to select a specific BookGroupID and all its direct and indirect children down the hierarchy. For instance, for the BookGroupID of 2 the output should look like this:

BookGroupID
-----------
2
5
7
8
10
11
12
13
14
15

To explain, row 5 is in the output because it is a direct child of row 2, rows 7, 8 and 12 are direct children of 5, 15 is a child of 13 which is a child of 10 which is a child of 7, 14 is a child of 11 which is a child of 8.

So, how can I do that in SQL?


texts:
I'm working on an eCommerce-based website using MongoDb.

In my DBCollection I have two types of documents:

Company details Item

{ doc_type : 'company',
  name : 'Acer',
  type : 'Laptops',
  helpline : '1800-200-000'
}

Item Details

{doc_type : "item",
  item_id : 1001,
  price : 2000,
  discount : 20}

Now in the product page I need to get data from both documents.

So, first I run

db.collection.find({doc_type:'item', item_id : 1001 });

to show product data, and then

 db.collection.find({doc_type:'company', name: "Acer"});

to get company data.

Is there any way to reduce these two calls to one and get data in a single result set?


texts: "relation does not exist" trying to import mysql dump into postgres

texts:
environment:

ubuntu 10.04
mysql server 5.1.69
postgres 9.2

Here's the sequence of steps:

created a new postgres database, myDatabase
executed this command: mysqldump -u root -p --compatible=postgresql myDatabase > mydump.sql
executed this command: psql -h localhost -d myDatabase -U postgres
At postgres prompt, executed these commands:

    SET standard_conforming_strings = 'off';
    SET backslash_quote = 'on';
    \i mydump.sql;

At this point, the process began, and after getting tons of

HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
psql:mydump.sql:196: WARNING:  nonstandard use of escape in a string literal
LINE 1: ...012053',50.18,50.36,49.4,49.69,59592,184824),(115,'\"2012060...

I get

psql:mydump.sql:196: ERROR:  relation "pricedata" does not exist
LINE 1: INSERT INTO "pricedata" VALUES (113,'19981102',0.6275,0.6275...

I checked the mydump.sql file, and it has a CREATE TABLE "pricedata", but obviously this didn't have the desired effect on the postgres db.

I'm not sure what I'm supposed to do to get this to work.  Any assistance would be greatly appreciated.


texts: Error on updating a field with binary values

texts:
I have a problem on emoji showing on webpage after migration to new DB Server. But I have a workaround. I will get the binary values of a certain field of TABLE1 on our OLD DB Server. Then update the TABLE1 on the new server using the script below:
UPDATE old_table t1 INNER JOIN new_table t2 ON t1.id = t2.id
SET t2.message = CAST(CONCAT("0x",HEX(t1.message)) AS BINARY);

But after executing this my message field on the new table becomes "0x1E395565"
If I do this script below, it will show the real message. But doing this for a million of records will take too long to execute hence the UPDATE INNER JOIN:
UPDATE new_table SET message = 0x1E395565 where id =1;

How to update this correctly? I concat "0x" on the HEX(t1.message) because when I do a mysqldump --hex-blob and charset=BINARY. There are 0x on the values.
My data type for message field is VARCHAR(1000) I know this might be wrong but is there any workaround without altering the table.


texts: SQL Server Linux(Ubuntu) Connection Options

texts:
In general SQL Server Connection Protocols allows three different connection protocols:

  TCP/IP
  Named Pipes
  Shared Memory

in linux by default installation we can connect to the SQL Server using TCP/IP, the question is is there any options for named pipes or shared memory or UNIX sockets?

Also, it seems there is no option to enable or disable such features in Linux.

TCP/IP:

sqlcmd –S tcp:ServerName –U sa –P

Named Pipes:

sqlcmd –S np:\\ServerName\pipe\sql\query –U sa –P

Shared Memory:

sqlcmd –S lpc:ServerName –U sa –P


texts:
I'm having some recent timeout issues in our SQL 2008 R2 server. This started occurring late last week and has since somewhat improved.

Issues:
When using SSMS and right clicking over a database...menu will delay on showing. Could be anywhere from 2-3 seconds to 10 seconds.

When trying to access reports in SSMS, hour glasses. It will either error out or eventually show the reports.

When using Team Foundation Server and checking in changes, It won't check in due to timeouts.
When debugging web apps, can timeout when accessing DB

I've looked at profiler and haven't seen anything that stands out. CPU utilization is low, memory is fine, and there is no locking issues of any sort.

Problems go away when:
Logged into the local DB server and connect via SharedMemory? "[local]". Everything is quick and runs as it is suppose to. When I switch the connection to use TCP/IP ["127.0.0.1"] I see the same delays.

Again, this doesn't happen all day....mornings are typically fine and then points in the after noon I'll start seeing timeouts.

Not seeing anything odd in: "select * from sys.dm_exec_requests order by status". Most are showing sleeping.

Not seeing anything odd in: "select * from sys.dm_exec_sessions order by login_time desc". About 48 active sessions.

 sp_monitor

 cpu_busy           io_busy         idle
 31331(2593)-17%    573(41)-0%  4025036(224233)-1531%

 packets_received         packets_sent  packet_errors
 6131943(343656)          6305718(356043)   0(0)

 total_read           total_write       total_errors    connections
 1372503(103382)      1678338(140267)   0(0)            5324156(296335)

 select @@MAX_CONNECTIONS == 32767


texts: Login mapping only works for the first query in a window

texts:
I have a really odd login mapping error for one DB that I can't seem to figure out.

When running a simple select statement while impersonating a login (A) on DB(1), it appears to work fine. However it only works the first time the query is run on each query window.

After that I repeatedly get an error stating

  Access to the remote server is denied because no login-mapping exists.

Opening a new query window and running exactly the same statement while again impersonation login (A) works every time - but again only the first time it is run before the same mapping error returns.
This is also repeatable on the same DB(1) for any other logins on the instance that are not sysadmin.

Nothing is being changed on the mapping settings in-between running the queries. I've even gone through and checked no settings have dropped off. No other DBs on the instance experience this issue. The logins use Windows Authentication. The required 'User mapping' has been set at instance level with a tick against
the DB, and the login is designated as DB_Owner. This has been used to create the DB login.

The login only has the server role 'Public' to avoid them accessing other DBs.
SysAdmins have no issues on this DB. This DB(1) has previously experienced issues with login mappings randomly dropping off. Re-creating the logins has always solved any issues. Obviously this shouldn't be required but I've found no reason why this happens either. I've re-created this login several times but on this ocassion it is not solving the issue.

To me it's a pretty standard setup and seems to work fine everywhere else, but I can't see why this DB is saying no login-mapping exists, especially when it works the first time on each query window.

For security reasons I'd rather not give the account sysadmin rights just to resolve this. I've never seen anything like this before though and can't find any google results suggesting this is a known bug.

Anyone seen this before and got any ideas on the cause?

I'm Using SQL Server 2008.


texts: ORA-12705: Cannot access NLS data files or invalid environment specified with jmeter test

texts:
I have trying to execute a simple test with jmeter in Oracle 19.7 database. This is my lang configuration in database
SQL> select * from nls_database_parameters;
...
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_CHARACTERSET    AL32UTF8
NLS_TERRITORY   AMERICA
NLS_LANGUAGE    AMERICAN

In my linux machine I have this variables
$ export LANG=en_us
$ echo $LANG
en_us
$ echo $NLS_LANG
American_America.UTF8

and I execute this test
$ ./jmeter -n -t ../prueba_select_jdbc.jmx -l /tmp/resultado -e -o /tmp/prueba_jmeter -Jthreads=10 -Jloop=5 -Duser.country=US -Duser.language=en
    Creating summariser <summary>
    Created the tree successfully using ../prueba_select_jdbc.jmx
    Starting standalone test @ Mon Nov 30 19:51:43 CET 2020 (1606762303758)
    Waiting for possible Shutdown/StopTestNow/HeapDump/ThreadDump message on port 4445
    summary =     50 in 00:00:09 =    5.4/s Avg:    51 Min:    29 Max:   556 Err:    50 (100.00%)
    Tidying up ...    @ Mon Nov 30 19:51:53 CET 2020 (1606762313390)
    ... end of run
$

and the error that i allways get is
null 0/java.sql.SQLException: Cannot create PoolableConnectionFactory (ORA-00604: error occurred at recursive SQL level 1\nORA-12705: Cannot access NLS data files or invalid environment specified\n)

I have tried multiple conbinations but none of then works.
Regards
Edit 1:
I have configured instant client and I used sqldeveloper launch from here without problems.
$ env |grep ORA
ORACLE_HOME=/usr/lib/oracle/19.3/client64
$ set |grep LIB
LD_LIBRARY_PATH=/usr/lib/oracle/19.3/client64/lib
$ echo $PATH
...:/usr/lib/oracle/19.3/client64/bin


texts: need help understanding the error-message: Error Connecting to database FATAL : no pg_hba.conf entry

texts:
We're trying to connect to a postgres/postgis installation on a remote server, using QGIS-Application. When accessing from another pc or from the same pc via pgadmin4v4 with the same credential/authentification settings, it works just fine. Therefore I conclude the pg_hba.conf is ok and it's in fact a problem of QGIS.

In order to better understand the problem and maybe file a bug for QGIS I need help analysing the error message. It sais:

Area: dbname = <databasename>, host <server-ip>, port 5432, user <username>, password <password>, authcfg = <id_of_auth_record>

SSL error: certificate verify failed
FATAL: no pg_hba.conf entry for host <some_other-ip>, user <username>, database <databasename>, SSL off

My main questions:

shouldn't the host-ip addresses be identical? Could it be part of the problem, that the second ip mentioned is not the correct server-ip? As far as I can see, there should be only one host (remote server) and one client involved (pc trying to get access).
why doesn't it say "no pg_hba.conf entry for client ..."
does QGIS think is it is a certificate problem, or a pg_hba.conf problem, or is that both the same

Update: further Details about the installation

Client-PC1: Win10, QGIS 3.4 [error] pgAdmin 4v4 [access ok]
Client-PC2: Win10, QGIS 3.10 [access ok] pgAdmin 4v4 [access ok]
Server: postgres 12
pg_hba.conf:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             0.0.0.0/0               scram-sha-256
# IPv6 local connections:
hostssl all             all             ::0/0                   scram-sha-256


texts:
When I dump the schema of my database

mysqldump --no-data my_db > schema.sql

I get lines like this

KEY `index_tbl_on_col` (`col`)

but I want lines like this

KEY `index_tbl_on_col` (`col`) USING BTREE

The indexes in question do, in fact, have Index_type = 'BTREE'.

show index from tbl;
| Table | Key_name      | Index_type |
+-------+---------------+------------+
| tbl   | ix_tbl_on_col | BTREE      |

I have tried a variety of mysqldump options including using --skip-opt --create-options together.

I am using mysqldump 5.6.26 from homebrew.

mysqldump --version
mysqldump  Ver 10.13 Distrib 5.6.26, for osx10.10 (x86_64)


texts: A multiple choice question, with multiple or only one correct answer, and sometimes no correct answer

texts:
It's a question & answer based model, and here is the scenario:

A question with multiple choices given, with 3 to 7 correct answers among those choices.
A question with multiple choices given, with only one correct answer among those choices.
A question with no choices given, with only one correct answer, and the solver will have to come at that answer themselves.

What I have done so far:

answers:

content (text-based column, length could be of 100 - 150 chars)
question_id (foreign_key)
correct_answer (boolean)

questions:

id (primary key)
content (text-based column, length could be of 100 - 150 chars)
multiple_choice (boolean)

Having a column multiple_choice, so in case if it's false, it means the particular question will be given no choice, and the solver will have to provide the answer all by themselves.

Issue:

Given that an answer only has to store a string of few characters, a boolean of either correct or not, why can't/shouldn't I store it as datatype jsonb in the questions table?


texts: How can i fix the Oracle Enerprise Manager error - Agent is blocked?

texts:
I have installed OEM 12c and after restarting my oms the agents got the following error:

Heartbeat Status : Agent is blocked
Blocked Reason : Plugin mismatch found between agent and repository.

I have restarted the agent but sill no success.
Any ideas there ?


texts:
I try to create a sp that checks if a job that didn't started already exists and if not creates a new one. It should return the id of the job.
For now I have the following code:

CREATE OR REPLACE FUNCTION
public.create_category_training_job(category_id integer) RETURNS integer AS
$BODY$
-- check if a job for this category is already queued and not started
DECLARE job_id Integer := (SELECT id FROM category_training_jobs WHERE startdate IS NULL);
-- if a not started job is present return the id it
IF job_id IS NOT NULL THEN
    RETURN job_id;
-- if id is not present create a new job and return its id
ELSE
    (INSERT INTO
        category_training_jobs (category_id, submissiondate, startdate, enddate)
     VALUES
        (category_id, NOW(), NULL, NULL)
     RETURNING id);
END IF;
$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF
COST 100;

For some rease I get "ERROR:  syntax error at or near "IF"
LINE 6: IF job_id IS NOT NULL THEN"

As far as I know IS NOT NULL should be a boolean expression, so it should be a valid expression. Am I missing something?


texts: MySQL : if server_id is not explicitly set, then get error `ERROR 1200 (HY000) at line 1: The server is not configured as slave;`

texts:
Our mysql version is "mysql  Ver 14.14 Distrib 5.1.35, for portbld-freebsd7.2 (amd64) using  5.2"

We have two database servers with replication, in a simple master/slave relationship. mysql2 is the master, mysql1 is the slave. A year ago, mysql was the master and mysql2 was the slave. We had to reverse their roles during a hardware failure.

On the master, we explicitly set the server_id (server-id)

[root@mysql2 ~]# grep server-id my.cnf
server-id       = 2
[root@mysql2 ~]# mysql -e "SHOW VARIABLES LIKE 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+

But, this setting was not set in my.cnf on the slave.

[root@mysql1 ~]# grep server-id my.cnf

Whoops. This shouldn't matter, because the server_id is set to 1 by default.

[root@mysql1 ~]# mysql -e "SHOW VARIABLES LIKE 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+

If I restart mysql, then the replication will work fine.

However, if I manually "STOP SLAVE" "START SLAVE", I get an error:

[root@mysql1 ~]# mysql -e "STOP SLAVE;"
[root@mysql1 ~]# mysql -e "START SLAVE;"
ERROR 1200 (HY000) at line 1: The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Why do I get this error when I don't explicitly set a value in my.cnf ? Why does replication work when I restart, but not when I "STOP SLAVE;" "START SLAVE;"?

It appears that mysql assigned a default value to the variable server_id.
According the default my.cnf, the server_id will default to 1

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
#server-id  = 1

The fix is to explicitly set server-id in my.cnf, as suggested by Baron Schwartz at http://www.xaprb.com/blog/2007/08/01/why-mysql-server-not-configured-as-slave/

  server-id=1


texts:
I have a Datawarehouse star schema.
One Dim table is 'DimTweet'. This contains tweet details.

For a selected date I want to be able to display the number of tweets.

However, I have a problem in my current design. How do I use one TweetID as FK in the Fact tbl for multiple rows in the DimTweet tbl?

Code:

INSERT INTO [CarDW].[dbo].[FactCarDetail]
           ([CarID]
           ,[RegionID]
           ,[DateID]
           ,[TweetID]
           ,[SharePrice]
           ,[ShareQty]
           ,[SalesQty]
           ,[TweetCountPositive]
           ,[TweetCountNegative])
SELECT
    c.CarID,
    r.RegionID,
    d.DateKey,
    -- ????? AS TweetID
    scsp.Price,
    scsp.Quantity,
    scs.Quantity,
    -- COUNT NO. OF TWEETS Postive
    -- COUNT NO. OF TWEETS Negative
FROM
    dbo.DimCar c
INNER JOIN
    dbo.StagingCarSale scs
    ON scs.CarModel = c.Model
INNER JOIN
    dbo.DimRegion r
    ON r.Region = scs.Region
INNER JOIN
    dbo.DimDate d
    ON d.Date = scs.SaleDate
--INNER JOIN
--  dbo.DimTweet t
--  ON t.
INNER JOIN
    dbo.StagingCarSharePrice scsp
    ON scs.CarModel = scsp.CarModel
        AND scs.SaleDate = scsp.PriceDate

UPDATE: Added separate Twitter Schema
Is this design feasible?


texts: Disable top (n) Sort Optimization for a specific view

texts:
I have a a view with a complex logic and three levels of deepness (nested views). Because of the complexity I can't paste the execution plan.

As the purpose of the view is to provide some business analytics to data analysts, while they are developing reports they use to check a sample of the view by doing a select (top N) query.

This (top N) queries in the view perform super bad because the optimizer is choosing a different execution plan for this view (afaik CQScanTopSortNew)

I've tried to do some optimizations for the top (N) use case, like using hash joins but this spoils the non top (n) use cases.

The non top (n) performs good. I would like to know how can I prevent the optimizer to choose a different execution plan when it has a top (n) clause without dramatically change the structure or functionality of the view.

For instance, if I add a select distinct inside the view, the optimizer chooses the correct plan always, but the functionality of the view changes.


texts: I noticed that a Schema has been created in the USERS Tablespace but our local rule is that it should be in a Tablespace by itself. In Oracle how do you move a Schema to a different Tablespace?


texts:
I am designing a database layout for a site. Agents registering on the site will have to enter their address including the country. Later I will build a dashboard where you would be able to search/sort by fields (including country field) and produce various reports (which includes country as well).

Is it better to store country name directly in a table or have a separate table with countries and only store the id of a country?


texts: Postgres DB is extremely fast with a query with a certain parameter, but extremely slow for another

texts:
I have a table called stats, where I store every action on a certain website (basically when the user clicks a link, data is saved in the db). The data is modeled with PostgreSQL as

(
id integer NOT NULL DEFAULT nextval('stats_id_seq'::regclass),
company integer NOT NULL,
layout integer NOT NULL,
browsername character varying(50) COLLATE pg_catalog."default",
lang character varying(5) COLLATE pg_catalog."default" NOT NULL,
...)

There are other fields that I don't think are relevant. Basically what I want to do is see which are the most used browsers for a certain company, by counting how many pages are visited by that browser and how many users there are for a given browser.

My query is:

select browsername, count(DISTINCT phpsessid) as visitors, count(*) as pages
from public.stats
where company=1 and createdate>'2017-01-01' and createdate<'2018-12-12'
group by browsername order by visitors desc;

The stats table contains around 80 millions rows. Now, this query is extremely fast if I set company=1 or company=222 (it's the company ID). It takes less than half a second to fetch around 20k rows. However, it is EXTREMELY slow if I set company=13549, for instance (we're talking about literal hours here). Obviously something is wrong, either in the data modeling or in the way I query.

How come there's such a difference for different companies? The DB was not done by me, so I apologize if I left something useful out, and feel free to ask.

The indexes are:

CREATE INDEX stats_new_company_13549_index
ON public.stats USING btree
(company, createdate)
TABLESPACE pg_default    WHERE company = 13549

CREATE INDEX stats_new_company_14863_index
ON public.stats USING btree
(company, createdate)
TABLESPACE pg_default    WHERE company = 14863

CREATE INDEX stats_new_company_createdate_cet_index
ON public.stats USING btree
(company, date(timezone('CET'::text, createdate)))
TABLESPACE pg_default;

CREATE INDEX stats_new_company_createdate_index_1
ON public.stats USING btree
(company, createdate)
TABLESPACE pg_default;

Here's the plan for the fast query: https://explain.depesz.com/s/uKmJ

And here's the plan for the slow one: https://explain.depesz.com/s/wysA

Just by looking at the plans you can see it took several minutes for the second one to even query the explain.

I also noticed that after running the explain a couple of times, it got done in reasonable amounts the third time. From that moment onward, the query would also drastically reduce its execution time, from hours to a mere 4 seconds.
It's the second time it happens, and I swear I'm not crazy.  If I change the company ID once again, the query takes hours again. I'm at lost here: is there some index problem?


texts: SQLOS Resource Monitor and QueryMemoryResourceNotification Win API

texts:
Here's some info about SQLOS and the Resource Monitor I found from Jonathan Kehayias:

  As a general rule, SQL Server will use as much memory as you can give
  it, and it will not release the memory that it has allocated under
  normal operations, unless the Windows Server OS sets the memory low
  resource notification flag. A component of the SQLOS called the
  Resource Monitor monitors the QueryMemoryResourceNotification Windows
  Server API and when Windows sets the low memory resource notification,
  the SQLOS will respond by sweeping its caches internally to reduce the
  process working set, and release memory back to Windows.

So, if windows sets the low memory resource notification, when the Resource Monitor discovers it, is there a way for the DBA to know it too?  An event notification?  An alert?  Something else?  Ideally, I'd like to send an email when it happens.


texts: How to enable email notification for failover in SQL Server

texts: We have a few servers in a High Availability group and need to configure email notification for any failover that happens on the server.


texts: How to update one column in table from select statement?

texts:
I have a table that looks like this:

utc date                       est date
2018-03-01 07:06:38.000
2018-03-01 07:07:38.000

est date is blank. I have a select statement that calculates EST time.

How do i update the est date column from my select statement?

SELECT toCONVERT(datetime,
               SWITCHOFFSET(CONVERT(datetimeoffset,
                                    table."utc date"),
                            DATENAME(TzOffset, SYSDATETIMEOFFSET())))
       as "etc date"
FROM table where  "utc date" > '2017-10-01'


texts: User can see Oracle tables via ODBC that her account does not have access to

texts:
We have a user whose Oracle account, according to ALL_TAB_PRIVS, has access to tables A, B, and C. However, when the user creates an ODBC and connects to Oracle via Microsoft Access (using her Oracle credentials in the ODBC), she can also view data from D, E, and F. We know that she isn't sharing someone else's credentials because we had her create a new ODBC and a new MS Access database, and the issue is still happening. One of our DBA's ran a query (I don't know which system views or tables) that shows her username as having used MSACCESS.EXE and odbcad32.exe to connect to Oracle, but nothing more specific than that.

This is worrisome. How is ODBC apparently bypassing Oracle security?

EDIT: To clarify, the tables in question are not in the user's schema. We keep many business tables in a particular schema S and then grant users access to just those tables in that schema. Consequently, most of the end users' schemas are empty. Our workflow generally goes like this:
1. User X requests access to tables on S schema.
2. Oracle account X is created on the server.
3. X is granted access to specific S tables.


texts:
When I right-click on a DACPAC file in windows explorer, I don't have an option to "Unpack" the file, like this document indicates:

https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/unpack-a-dac-package?view=sql-server-ver15

I have both Visual Studio 2019 and SSMS v18.4 installed. Am I missing something?  Right now the file is associated with Notepad.


texts: PostgresSQL: Cannot get to console under different user name

texts:
I normally get to the console in UBUNTU by typing:

sudo -u postgres psql

But I need to delete a database owned by a different user.

When I type:

sudo -u username psql

It tells me "database "username" does not exist.

But... I wasn't trying to connect to a database, I was trying to connect as a user.


texts: Retrieving changed rows only from a table with 10^7 rows

texts:
I have a table with about 300 columns and about 107 rows and I have to retrieve changed rows only, ordered by time (not distinct, but changed). The result set may be limited to 100 rows as well.

I've tried the following query with lag window function:

-- Suppose, we want to retrieve data from column1, column2 and column3 fields
-- There may be other fields, though
SELECT
  w."stamp",
  w."column1",
  w."column2",
  w."column3"
FROM (
       SELECT
         o.stamp,
         o.obj_id,
         "o"."column1",
         "o"."column2",
         "o"."column3",
         lag(o."column1") OVER (ORDER BY stamp) AS "_prev_column1",
         lag(o."column2") OVER (ORDER BY stamp) AS "_prev_column2",
         lag(o."column3") OVER (ORDER BY stamp) AS "_prev_column3"
       FROM "table_name" o
       WHERE o.stamp BETWEEN '01.12.2015 00:00' AND '23.01.2016 00:00'
       ORDER BY o.stamp DESC
     ) AS w
WHERE
  w.obj_id = 42 AND w.stamp BETWEEN '01.12.2015 00:00' AND '23.01.2016 00:00' AND
  ("w"."_prev_column_1", "w"."_prev_column_2", "w"."_prev_column_3") IS DISTINCT FROM
  ("w"."column_1", "w"."column_2", "w"."column_3")
ORDER BY w.stamp DESC
LIMIT 100;

However, it takes too much time to complete. Is it possible to optimize this query, or the problem should be solved in another way (e.g., custom function)?

Table definition:

CREATE TABLE table_name (
  id BIGINT PRIMARY KEY NOT NULL DEFAULT nextval('ds_dyn_sequence'::regclass),
  obj_id BIGINT NOT NULL,
  stamp TIMESTAMP NOT NULL DEFAULT now(),
  column1 BIGINT,
  column2 BIGINT,
  column3 BIGINT
  -- Other fields, all BIGINT NULL
);

CREATE UNIQUE INDEX obj_id_stamp_key ON table_name USING BTREE (obj_id, stamp);

The table contains about 104 rows per a hour. The table will be limited to three months, so, the total number of rows will approximately be 2*107.

PostgreSQL version: 9.3


texts: MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

texts:
I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records)

I'm going to be converting this table to InnoDB in a production environment.

The DB is on a cloud server with 8GB of RAM (will likely upgrade to 30GB RAM just for the conversion).

One method to do this is to simple write an ALTER query:

  What settings and configs would be optimal to convert in this way as quickly and smoothly as possible?

Some of the suggested settings I've read:

large innodb_buffer_pool_size (is 4GB the largest possible?)
preread your old myisam data/index files using shell commands
increase innodb_log_file_size (what's best size in the case?)
Do the alter table in X parallel threads, where X is the qty of CPU cores on your server (how can you do this?)
other minor tweaks (innodb_doublewrite=0, innodb_flush_log_at_trx_commit=0)

  Note: Similar question here (but much smaller table example with
  different server size):
  https://stackoverflow.com/questions/2081823/speeding-up-conversion-from-myisam-to-innodb/2081871#2081871

Of course, there are other methods to do this like:

Do a MYSQLDUMP of the old table and reinsert into a new clean InnoDB table,
Run "SELECT * ... INSERT INTO... " query, or
Run "SELECT INTO OUTFILE..." query on old table and then a "LOAD DATA INFILE... " query into the new table

1. How would you configure your settings for the purpose of making this alter go as quickly and as smoothly as possible?

2. Which method do you think is the best alternative?


texts:
I'm new to SSIS trying to covert data types. I'm pulling data from Google Analytics and loading data into a SQL 2008 database. The date column from Google analytics appears as "DT_WSTR" and the values are in the format "20130402" (year:month:day).  I want to change the format to a date format so i can load it into a date field in my database.  How do I do this?

Thanks


texts:
Following Situation:

You have a Database with very high transactions, the Database is about 4100 GBs
The Recovery Modell from your Database is full
Every week you make a full backup from your database
Every day you make a diff backup from your database
You make hour  a Backup from the log

Now bad things happens:
Your Server is down (lost power connection) , your emergency power supply is far away :/
What should the experience dba do, so that the database is going as fast as possible back online.


texts: How can I corrupt a table in SQL Server?

texts:
I have to write a program that detects corrupted tables and tries to repair them. For this I need to corrupt tables regularly.

Is there anyway to corrupt tables manually?


texts: Problem with Oracle Rollback Segment Session

texts:
I am facing a problem in Oracle, which after performing a procedure that performs INSERT or UPDATE with a millions amount of record in a table, not the next procedure "sees" the data in the table already inserted. My procedure is scheduled to perform commit 100 thousand lines. I also use cursors with bulk collection.

I suspect the problem may be due to the area Rollback segment of the Session of Oracle, anyone know if there is any DML command to clear the area or give a flush on Rollback segment?


texts: Error 1236 From Master After Restored Replication

texts:
I've been trying to restore a broken replication caused by power interruption. upon checking the slaveDB, it was already broken so I setup a new replication. And upon supposedly completing the replication, i'm stuck with this: Got fatal error 1236 from master when reading data from binary log:    'Misconfigured master - server id was not set'

We are running Percona Server 5.5.
Here were the procedures i made:

1.) on the Master Server masterDB (IP = 10.0.0.20, server_id = 1),I ran innobackupex to make a hot backup. checked the xtrabackup_binlog_info and noted slavedb-bin.000609    and 113535960 as the binlog file and position.

2.) Restored the backup to the slaveDB (IP = 10.0.0.40), and setup the replication as follows -- CHANGE MASTER TO MASTER_HOST='10.0.0.20', MASTER_USER='user',MASTER_PASSWORD='password',MASTER_LOG_FILE='slavedb-bin.000609',MASTER_LOG_POS=113535960 (as noted from the xtrabackup_binlog_info from the innobackupex backup.

3.) After Starting the Slave, i get the error 1236 above with a misconfigured master, but the master server_id is 1.

I've read online on an old thread that a possible solution would be to go to the next binlog file since it might have been corrupted with the transactions applied.

But how would i go about moving to the next binlog file? Is it the next binlog from the innobackupex xtrabackup_binlog_info?" or should i check the master log and find out the last binlog written before it crashed?

I tried moving past the binlog from what innobackupex gave me, going to slavedb-bin.000610 and POS=0, but it still got the same error.


texts:
I have followed all the steps described on the Getting Started with DB2 Express v9.7 e-book and installed DB2 Express-C on my Mac. The installation succeeded without a problem.

I did setup OS X according to this blog post: http://programmingzen.com/2009/01/20/installing-db2-on-mac-os-x/

After the installation, I set up the DB2 instance environment as instructed and was able to start DB2 with:
$ db2start

But I was unable to run db2fs. It returns the following error message:

$ db2fs
sh: /Users/ivan/sqllib/java/jdk64/jre/bin/java: No such file or directory DB2JAVIT : RC = 127

What else do I have to setup in order to make it work on my system?


texts: Postgres Full Text Search With Short Word Matching

texts:
I'm using Postgres' full text search to return results, but it seems any query with 3 characters or fewer returns no results.  Is there a way to configure Postgres so that I can query with 2 and 3 character long strings?  My data set is extremely small, so performance would not be an issue.

For reference, here's my table definition:

CREATE TABLE doctokens (
    id serial primary key,
    inner_text TEXT NOT NULL,
    tokens tsvector
)

INSERT INTO doctokens (inner_text) VALUES ('Route builders give you a fluent syntax')

After an insert, I create the token tsvector:

UPDATE doctokens SET tokens = setweight(to_tsvector('english', COALESCE(inner_text, '')), 'A')

And here's how I'm querying the table:

SELECT ts_headline('english', inner_text, query, 'StartSel = <em>, StopSel = </em>, ShortWord = 1') as inner_text_highlights
FROM (SELECT inner_text, ts_rank_cd(tokens, query) AS rank, query
    FROM doctokens, plainto_tsquery('english', 'rou') AS query
    WHERE tokens @@ query
    ORDER BY rank DESC
    LIMIT 5) AS query_results;


texts: How to assign 'findandmodify' privilege to a mongo user?

texts:
I'm running a Mongo 2.6 replica instance where a node application needs to talk to. Basically nothing much was setup except these parameters:

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

# Where and how to store data.
storage:
  dbPath: /var/db/mongodb/
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# how the process runs
processManagement:
  fork: true  # fork and run in background

security:
  keyFile: /usr/local/etc/mongodb-keyfile

replication:
  replSetName: bla

That for the server, and on the specific DB we have defined a user:

db.createUser({user: "the_user", pwd:"blablapassword", roles:["readWrite"]})

So far everything worked fine, except this new application is trying to do something others didn't do yet, and it's failing:

{ [MongoError: not authorized on blablabla to execute command { findandmodify: "identitycounters", query: { field: "_id", model: "source" }, new: true, remove: false, upsert: false, update: { $set: { count: 0 } }, writeConcern: { w: 1 } }]

So I've been going through documentation, trying to figure out how to get that done... and without luck. No matter what I tried, I keep ending up with the same error, so I guess I'm doing something wrong.

Googling this issue, I found some recommendation to just include the role dbAdmin. While I don't like that as a solution, I tried it anyway out of despair and that too didn't work.

All I know is the app works fine on a blank Mongo instance without replication or authorization.

ASK:
Given that we do have replication and authorization in production, how can I authorize the user to do that findandmodify() command?

Your help is appreciated!


texts: converting latin to utf8mb4 causes questionmarks

texts:
The original format of the data is unknown
The new table is in utf8mb4_general_ci

If I do CONVERT(BINARY CONVERT(column USING latin1) USING UTF8) as mentioned here - it fixes all text, but converts something like: © in the original column to ? in the new column.

If it helps to determine what original encoding it was in, the original text renders as e.g. KotaÄiÄ‡i and converts to Kotačići.

Is there a way to both preserve special characters and restore correct utf8 text format?

As requested in the comments an example via hex:

HEX(col):

C398C2A3C398C2BAC399E280A0C399C5A0C398C2A920C398C2B3C399E280A620C398C2A7C399E2809EC399E2809EC399E280A1

CONVERT(BINARY CONVERT(col USING latin1) USING UTF8):

أغنية سم الله

Just raw:

Ø£ØºÙ†ÙŠØ© Ø³Ù… Ø§Ù„Ù„Ù‡

The dump-file starts with:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

Tables get created with: ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


texts: How to join fixed row table against mapping table and reveal unmapped records

texts:
I have two tables and I am having trouble figuring out how to join them to meet my projection needs. Here is a scenario that illustrates the problem

1. The first table has a fixed set of values/rows; in this case Room Types

Room Types

Id | RoomType
--------------
1  | Bed Room
2  | Kitchen
3  | Half-Bath

2. The second table maps the items from the first table to an instance of a Property. One Property can have 1 or more Room Types

Property Rooms

Property | RoomTypeId
---------------------
  ABC    |     1
  ABC    |     3
  EFG    |     1
  XYZ    |     1
  XYZ    |     2
  XYZ    |     3

Note: some of the above properties are not mapped to some Room Types

3. I would like to make a projection that joins the rows in such a way that I can easily account for Room Types that are mapped and not mapped to a Property. Here is a sample result:

Property | RoomTypeId | PropertyHasRoom
---------------------------------------
   ABC   |     1      |      Yes
   ABC   |     2      |   No or NULL
   ABC   |     3      |      Yes
   EFG   |     1      |      Yes
   EFG   |     2      |   No or NULL
   EFG   |     3      |   No or NULL
   XYZ   |     1      |      Yes
   XYZ   |     2      |      Yes
   XYZ   |     3      |      Yes

Can anyone explain how this can be accomplished in T-SQL.


texts:
Based on Northwind schema:

Assume we have a query like this (please disregard the actual usefulness of this, as the real use case is more involved)

SELECT unnest(r.agg) FROM (
  SELECT array_agg(x) AS agg
  FROM (
     SELECT *
     FROM northwind.u_suppliers s LEFT JOIN northwind.u_products p ON s.guid = p.supplier
     ) x
  GROUP BY x.supplier
  ) r LIMIT 1;

the result is

(9Y_03ogA1mOixVtJTqB8zih9Lu5-DB-l--fYvnHhyMQ,22,"Dirk Luchte","Accounting Manager",6j7qZ3Pjad5irKAM1MKX1WU7hvkgIOhZoe9OeRfTX4Q,"(12345) 1210",,northwindSupplier,jp9VvzFBbklHelD_ybS1oDsJbILda1duNhMtCdGBZqg,0,do4_k8zCsOog5tYROlsouF81Uj9I5LbTFawvuPw9Q-0,"2017-10-11 08:45:14.982",47,"Zaanse koeken",9Y_03ogA1mOixVtJTqB8zih9Lu5-DB-l--fYvnHhyMQ,AkOUoKYOpHFWIkNcoPX3CLtTl4n_ACN6TwWxqd3gzYQ,"10 - 4 oz boxes",9.5,36,0,0,f,,northwindProduct)

which is a record.

What would be the way to create a table from this with all columns and unnested records as rows?

So there'd be:

u_suppliers_column1 | ... | u_suppliers.columnN | u_products.column1 | ... | u_products.columnN
------------------------------------------------------------------------------------------------
9Y_03og...          | 22 | "Dirk Luchte" ...

The snippet is for illustrational purpose only - of course, creating aggregate just to unnest it again is non-sensible under these circumstances, but for sake of simplicity & reproducibility I have decided to write it as such,.


texts: PostgreSQL (big data): search by tags and order by timestamp

texts:
We need to add a search feature on a large table (200M+ rows):

item_id | tags                          | created_at          | ...
-------------------------------------------------------------------
1       | ['tag1', 'bar2']              | 2020-01-06 12:43:32 |
2       | ['example5', 'tag9', 'foo2']  | 2020-01-10 10:40:00 |
3       | ['test1', 'tag5']             | 2020-01-11 12:43:32 |
...

The queries would be similar to this one:

SELECT * FROM items
WHERE tags @> ARRAY['t2', 't5']::varchar[]
ORDER BY created_at DESC
LIMIT 100;

Basically it's like searching some logs by tags and ordering them by timestamp. Seems a common scenario...

What index should we use? Have you ever tested something similar in production?

Example 1: create a GIN index on tags. The problem is that the search may return millions of results and in order to apply order / limit you need to make millions of reads from the table on disk (in order to get the created_at value for each row).
Example 2: add the btree_gin extension and create a composite index on created_at and tags. The problem is the same as above: I think that PostgreSQL cannot use ordering since the index is declared as a GIN index and not as a btree.
Example 3: create a btree index on created_at and tags. PostgreSQL needs to scan the whole index, since btree doesn't support array operators. I also fear that due to the SELECT * PostgreSQL will not use an index-only scan, thus resulting in millions of reads from disk (that would be actually useless since it only needs 100 reads from disk).


texts: Joining to same table and updating column on row match for multiple columns

texts:
I have a single table (table_1) of schema below:

row_id      identifier   col1    col2    col3    col4    status
1           A            1       2       3       4
2           A            2       3       4       5
3           B            1       2       3       4
4           B            2       3       4       6
5           C            1       2       3       4

I want to join on identifier = A such that any rows with identifier != A but which match on values for col1, col2, col3 and col4 will update the status column with values for any matched ('B,C'), and another for mismatch (foo).

Note that the status column values need to update with the identifier(s) matched.

row_id      identifier   col1    col2    col3    col4    status
1           A            1       2       3       4       B,C
2           A            2       3       4       5       foo
3           B            1       2       3       4
4           B            2       3       4       6
5           C            1       2       3       4

I've used a single SELECT statement with multiple subqueries for each separate column to match to on 'A', but had to explicitly select other identifiers and resulted in errors.

Corrected row_id to show the primary key.


texts:
At the moment I have around 125 production instances, each with a script-based maintenance plan running as an agent job.

The tasks run are Index Reorg/Rebuild, Stats updates and Checkdb. Backups are looked after by Netbackup so they dont form part of the plans but for a couple of exceptions.

I moved all the instances last year to script-based maintenance plans from plans created with the SSMS wizard (hate those) and they're efficient and effective so overall I'm pleased.

I'm wondering whether it's feasible to take things a little further. I've recently been working on a powershell script that, when pointed at an instance, iterates through the databases on that instance and performs those three tasks on demand.

My question is whether anyone can see any downside by doing this for all instances, I.e. Having a single powershell script on our DBA server that iterates through a list of instances on a windows schedule and executes the maintenance tasks. Any errors would be handled / written out to logs etc.

The main benefit of this in my eyes that we won't be deploying mp jobs to new instances and configuring schedules. We will just be adding the name of any new instance to the instances the script must iterate through.

I'd welcome your thoughts.


texts: Check Free Space in SQL Server (License Limit)

texts:
I have a Microsoft SQL Server 2017 Express Edition those have a size limit of 10240MB (10Gigs) and I am not sure how I can check how much space I have left in this license. I have tried the following:
SELECT DB_NAME(database_id) AS database_name,
    type_desc,
    name AS FileName,
    size/128.0 AS CurrentSizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'DemoDB' AND type IN (0,1);

This outputs the following:

database_name
type_desc
FileName
CurrentSizeMB

DemoDB
ROWS
DemoDB
10229.687500

DemoDB
LOG
DemoDB_log
8217.000000

Is it the upper value CurrentSizeMB 10229.687500 or do I need to use another query?


texts: Use array_position() function to get the most frequent value for column from pg_stats

texts:
I'm trying to execute this simple query in order to check whether some value (1000) belongs to MCV list used by Postgres query optimizer:

SELECT array_position(most_common_vals, 1000)
FROM pg_stats
WHERE tablename = 'tenk1'
AND attname = 'unique1';

But receive the following error message:

ERROR:  function array_position(anyarray, integer) does not exist

How can it be fixed?

array_position() is the standard function as described here and the following statement returns 2 as expected:

SELECT array_position('{1,2,3}', 2);


texts:
I have a scenario where I have to pivot a list of rows to columns dynamically .For a particular order_number there might be several products. I want all those products to be in a separate column, along with its product_id (that too in columns), but we cannot predict how many products will there for a particular order_number.

Attaching a sample input table and desired output. I have tried the crosstab function but the result is not correct.

Table values:

Desired Ouput:


texts: How can I join two tables while filtering the results with a" WHERE IN" clause?

texts:
What I am trying to do is set up a selection filter for a cascading list on my site.  When I say selection filter, I mean I am trying to limit, and sort the results based on what the user selects.

I have set up the ability for my users to click a button and mark an item as a favorite.  That information is stored in "billdata.favoritebills".

Also, for efficiency I have saved vote totals for each bill in billdata.billvotesmeta.  billdata.billvotesmeta contains the column names: billid,totalvotes,yesvotes,presentvotes, and novotes. The primary key is billid.

My database is set up as follows:

billdata.bills

billdata.favoritebills

billdata.billvotesmeta

In this particular filter I am trying to set up, I want to limit the results to only being ones that the user has marked as a favorite and at the same time I want to order them from least to greatest total number of votes.

billdata.favoritebills contains the column names: userid and billid and a combination either exists in marked as a favorite or is deleted if it is unmarked as a favorite.

$testquery = "SELECT * FROM bills INNER JOIN bills ON billdata.billvotesmeta.billid = billdata.bills.id WHERE id IN ( SELECT billid FROM billdata.favoritebills WHERE userid='$_SESSION[userid]' ) ORDER BY DESC billdata.favoritebills.totalvotes";

$result = mysqli_query(dbcxn('bill'),$testquery)
     or mysqli_debug();

$fetched = mysqli_fetch_assoc($result);

The resulting output from PHP is:

Warning: mysqli_debug() expects exactly 1 parameter, 0 given in /opt/lampp/htdocs/unipartisan/includes/billlistcompiler.inc on line 38

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /opt/lampp/htdocs/unipartisan/includes/billlistcompiler.inc on line 39
1NULL

What should happen is that it limits the billdata.bills.* selection to only being favorites and then orders them by the joined table's total vote count.  The data from billdata.bills is the information used to build the page.

I think the problem is in the " WHERE id IN ( SELECT billid FROM billdata.favoritebills WHERE userid='$_SESSION[userid]' ) maybe this is not compatible with a query that has a join as I have not found anywhere else detailing a WHERE IN clause along with a join.
"


texts: Understanding multiple indexes with MySQL (and performance)

texts:
I have a "phones" table with around 120 000 records, it contains phone_id, phone_number, phone_user_name, phone_last_contacted, etc.

The phone_id is index and looks like: 1,2,3,4 ... 120 000.

But I often update different fields like "phone_last_contacted":

UPDATE phones SET phone_last_contacted = '01-01-2013' WHERE phone_number = '+123456789'

The "slow query" log says that query read many records before finding the right one to update;

Would it be a performance upgrade if I set the phone_number to be the index? Considering that all phone_numbers are unique in this table. If the phone_number is the index, does this mean that mysql will know how to go directly to the row to update instead of reading many rows and find the right row ? Is that the purpose of an index or multiple indexes ?

Edit: I just changed the "UPDATE" query to find the row by phone_id (which was primary key and unique), it was a huge difference. Makes sense for me now, it no longer has to look in each row in order to find the phone_number, it can find it faster by phone_id:


texts: Rights needed to see database files in Properties dialog?

texts:
I have a contractor setting up a database application. The database is set up, the login for the app is set as the database owner, but has only 'public' at the server level.

When the contractor logs into SSMS with the database login, he sees no information in the Database Properties > Files page -- the table is empty. However, exec sp_helpdb database; returns all the info that should be showing in Properties.

Are there any rights I can grant that will show the file info in Properties, without granting excessive rights to anything else?

(SQL Server 2008 R2)


texts:
I have log in form with Username and Password.

$con=mysqli_connect(LOGINDETAILS);
/*if (!$con) {
die("Can not connect: " . mysql_error());
}
*/

$Username = $_POST['Username'];

$pswd = $_POST['pswd'];
$pswd = md5($pswd);

$sql="INSERT IGNORE INTO CyberSecurity (pswd, Username)
VALUES('$pswd', '$Username')";

mysqli_query($con,$sql);
echo "The password that you provided has been stored in our database for research purposes.";
mysqli_close($con);
?>

I tried using the INSERT IGNOREbut it stills saves the password even though its the exact same one.


texts: Lockdown database access on certain tables

texts:
I have a dev team who have a number of SQL databases used as a back end for mostly excel front end.
This originally came from an excel spreadsheet until it got made into SQL databases.
At first, they were not too concerned about security and decided to give access to a domain group logon that contains basically all the organisation's users.  Over time they have put more sensitive information into the databases and have realised that anyone in the organisation can view the information if they were to use another tool, power BI or SSMS due to the domain logon.
They say that they still need to use the domain logon as everyone needs access but wants to secure the sensitive information on certain tables but still be accessible to their queries from excel.
They have suggested Signing Stored Procedures with a Certificate but I am concerned they will want to try this with all their code placed in SPs, removing the domain logon, and this would create numerous amounts of admin to maintain all of them.
How am I able to implement this in SQL Server in another way?


texts: Database Collation problem when comparing two databases

texts:
I have changed the collation of test database using the follwoing query:

USE master;
GO
ALTER DATABASE test
COLLATE SQL_Latin1_General_CP1_CI_AI ;
GO

--Verify the collation setting.
SELECT name, collation_name
FROM sys.databases
WHERE name = N'test';
GO

Now when I want to compare a columns of test database against test2 database, I receive the following error:

  Cannot resolve the collation conflict between
  "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in
  the equal to operation.

I have checked that both databases has SQL_Latin1_General_CP1_CI_AI collation in :

Management studio > Database > properties > options > collation.

Is there another option rather than the database itself (e.g. schema) which still holds the old collation?


texts: Service broker stuck infinitely looping using outdated activation stored procedure

texts:
I was debugging and stuck a bunch of log statements into my activation procedure including a statement that logs the value of @@ROWCOUNT that is used in this manner:

INSERT INTO ###Debug(SomeMessage, SomeData)
VALUES ('row count', @@ROWCOUNT)

IF @@ROWCOUNT = 0
BEGIN
    ROLLBACK TRANSACTION
    BREAK
END

However, I made a critical mistake here because saving the value of @@ROWCOUNT in this manner makes it always return 1 on the next call, so the rollback statement never runs.

I modified my activation stored procedure to set @@ROWCOUNT in a variable to fix my problem, but now I keep seeing an error show up every 5 seconds (waitfor receive with timeout of 5 seconds) in SQL Server Profiler from the old version of the stored procedure.

Apparently SQL Server has decided to continue using the old stored procedure even though I altered it!

I have never seen this behavior before of SQL Server caching code, and a google search for "Service broker caching stored procedures" doesn't return anything relevant.

I even tried

DBCC FREEPROCCACHE
GO
DROP PROCEDURE SB_..._Consume
GO
CREATE PROCEDURE SB_..._Consume ...

How do I escape the infinite loop of failed conversation handling?

P.S. The initiating BEGIN DIALOG statement included LIFETIME = 600 but it has been stuck for nearly an hour now, well beyond the 10 minute timeout I gave it!

Using SQL Server 2014 Enterprise (test database is in 2008R2 compatibility mode).

Edit: Using SSDT to publish the updated database from Visual Studio fixed it. There must be a better way though.

Thanks.


texts: Maximum memory for SQL Server 2000 TB when my RAM is just 512 GB

texts:
Maximum allocated for a SQL Server 2012 instance shows as 2000 TB when my RAM for machine is just 512 GB. Why is it displaying like this?

Attached is the screenshot. Please check it and help me to know on this.


texts: Using a Having clause with an outer join

texts:
I want to use a "having" clause with an outer join, using a column that is used in the outer join.

This is my query:

SELECT
  I.CARD_BIN BIN
FROM
  GE_ICA_BIN I
LEFT OUTER JOIN GE_PAYEE_VALIDATION P ON I.CARD_BIN = P.CARD_BIN
LEFT OUTER JOIN GE_PAYEE PP ON PP.PAYEE_CODE = P.PAYEE_CODE
ORDER BY I.CARD_BIN
HAVING COUNT(*) > 1;

But this gives me this error:

ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 36 Column: 19

Line 36 is ORDER BY I.CARD_BIN.

Can someone tell me how I can solve this problem?


texts:
Have an SQL query for DB2 that returns the timestamp of when an event completed. It completes at 2 different date/times, which is the result expected.

That result completes at those 2 different times for the same entity. I desire that the entity be counted one time for the purposes of my query.

How would I get the query to return the result for only the first instance of completion for that entity?


texts: Options for creating a read-only replica of a DB2 database

texts:
We are a dev company that develops and supports an application based on DB2 LUW 10.5 for a customer.  We have a VPN that gives us access to our customer's production environment, and we often connect to the production DB2 server directly to investigate various issues.  We want to avoid accessing the production database in this way, so we'd like to create a copy of the production database on our network.

The production server is currently replicated to a DR site using HADR.  HADR with the read only option would give us what we wanted but we want to avoid the need to purchase another license for our server - we'd like to just use the Express-C edition of DB2.  Log shipping may be possible but from what I read the database is not accessible while in the state to accept logs.

For the solution to work the target database would need to be fairly up to date.  It can be read-only but that is not a requirement.  If it is not read-only we would not want to push changes back to the master.

What options are available for this scenario?  Thanks in advance for any ideas.


texts: Can't use CAST, Convert with UDT in Sql Server 2014

texts:
My tables consist of many decimal columns with different precision.
I wanted to standardize the usage of precision by creating several UDTs with predefined precision and use them as column type instead.
I verified most of the cases that I need can be done without using  CAST or CONVERT.
I only have one question left-
How can I explicitly reduce precision using udt (Last line in the script)

It there any other way a value could be converted into a UDT?
Is there any other approach to enforce using specific precision when defining a new column?

/*
DROP TABLE TestPrices;
DROP TABLE TestQty;
DROP TABLE TestResult;
DROP TYPE udtPrice;
DROP TYPE udtQty;
GO
*/

CREATE TYPE udtPrice
FROM DECIMAL(10, 4) NOT NULL;

CREATE TYPE udtQty
FROM DECIMAL(10, 6) NOT NULL;
GO

CREATE TABLE TestPrices (Price udtPrice)
CREATE TABLE TestQty    (Qty udtQty)
CREATE TABLE TestResult (Cost udtPrice)
GO

INSERT INTO TestPrices VALUES (23.1234)
INSERT INTO TestQty VALUES (2.123456)
INSERT INTO TestQty VALUES ('4.1234564353454354543543543')
select * from TestQty

INSERT INTO TestResult
SELECT p.Price * q.Qty AS res FROM TestPrices p,TestQty q

SELECT p.Price * q.Qty AS res FROM TestPrices p,TestQty q
SELECT * FROM TestResult

--how can I explicitly reduce precision using udt
SELECT TOP 1 CAST(Qty as udtPrice) FROM TestQty

Thank you in advance.


texts: Change server name for SQLLocalDB instance

texts:
Is there any way to change the instance name of LocalDB of SQL Server 2012 from (LocalDB)\xxxx to just xxxx?

I need it for an application that needs SQL Server DB instance name to be MSSQLDEFAULT.


texts: Single table with multiple NULL columns vs multiple tables

texts:
I've read this. But I'm not sure if I understood correctly or my situation is slightly different.

So, Here is my situation: I've a single table with customer's delivery addresses and there are two types of delivery addresses -  main delivery address (every column is required) &  alternative delivery address(optional).

Here's my delivery_info table:

id(PK)
city_id(FK)
area_id(FK)
recipient_name(NULL)
street_address(NULL)
contact_no(NULL)

My orders table:

id(PK)
delivery_info_id(FK)
alt_delivery_info_id(FK)
status
...

The city (city_id) and area (area_id) of both addresses will always be available and must be same that means the alternative delivery address must be in the same city and same area of main delivery address. But other 3 columns - recipient_name, street_address, contact_no is required for main delivery address but optional for alternative delivery address.

So my question is should i separate these two types of delivery addresses in two different tables e.g. delivery_info and a alt_delivery_info OR Keep it as it is???


texts:
I'm just wondering if this syntax is standard or where it comes from?

For example

INSERT INTO gtest3 SET gDateTime='2017-07-16 01:00:00'

You can see it documented in MySQL but PostgreSQL does not support it.


texts: Autoincrement primary key in CREATE TABLE ... AS SELECT

texts:
I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query?

For example:

create table `user_mv` select `user`.`firstname` as
   `firstname`,
   `user`.`lastname` as `lastname`,
   `user`.`lang` as `lang`,
   `user`.`name` as `user_name`,
   `group`.`name` as `group_name`
from `user`
  inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`)
  left  join `group` on (`group`.`group_id`=`user_groups`.`group_id`)
where `user`.`lang`=`group`.`lang`

This query creates a table that contains firstname, lastname, lang, username, group_name columns. I want it to also have an id column that is an autoincrement primary key.

Is there any way to do this by changing this query?
I know I can do that by altering table after executing this query, but if there is any way to do this directly in the create table statement, I'd like to know how to do that.


texts: Handle development issues when dealing with different collation for the instance/database setting

texts:
Looking for some guidance on how we can handle the scripts/schemas when being run on different collation in sql server.
We have a process where multiple stored procs are created lets say with schema sales. Therefore all related tables and procs for that within database are stored as sales.table1, sales.table2 ... etc and stored procs like sales.us_proc1, sales.usp_proc2.... etc.
Now for default collation of database on SQL server instance with default collation the procs runs fine.
However when we deploy same objects/procs on sql server with case sensitive collation instances same scripts starts failing.
What we can do as of now is have 2 sets of scripts. Say 1st for all non case sensitive server and other set for case sensitive.
But problem is these 2 sets of deployment will go on server which adds bunch of unwanted objects/procs etc..
Please suggest how should be handle such scenarios where single set of deployment for such scripts can be done irrespective of what the server/database collation might be?
Edit- Trying to update my question for few questions asked:
Scripts are failing with error message like below on case sensitive databases-

Msg207,Level 16, state1 - Invalid column name Timestamp - say for
proc1
Msg207,Level 16, state1 - Invalid column name state- say for proc2

Yes we did created 2 sets of scripts which ideally does not seem helpful here as lot of objects are just getting deployed twice for case sensitive server
Also we are not creating database as the database would be there and we just need to deploy bunch of these scripts.
Also the collation ends with BIN for TempDB and our database where scripts are deployed.
As mentioned in answers for TempDB, i have gone through most of my scripts and it does not look like we have references to # temp tables in most of the stored procs. HOwever yes we do use local variables and table variables @. Does this also counts for same issues?


texts: How to disable starting MySQL service on Windows7 startup?

texts:
When I turn on my desktop (Windows 7 SP1), I often find 2 or 3 GB of memory is already occupied by MySQL processes. I don't care about how much memory a database requires, if it actually runs some queries, but I do, if it's an app I even don't start myself yet. For that matter, I barely use MySQL barring when I develop some web apps in my local environments.

So is it possible to stop this gluttony? I tried to remove MySQL from my startup group viamsconfig, but couldn't find its name over there.

Here is extra info.

mysql  Ver 14.14 Distrib 5.6.15, for Win64 (x86_64)
MySQL Server 5.6
MySQL Workbench 6.0


texts: Difference between two rows in MySQL Workbench, but LAG is not authorized


texts: Question about postgres commits to table in UNLOGGED mode

texts:
I have three tables: data, processed, and processing_status. The first one has rows coming in quite quickly. Every few minutes, I check the highest ID in data and compare it to processing_status. If enough new datapoints have come in, the new ones are processed and aggregated into processed table, and processing_status is updated to the highest ID. Obviously, both of those happen in one transaction, so that if there's a crash, everything stays sane, and neither table is updated without the other.
I'd like to improve the performance of this, regarding the bloat to the WAL logs. If I set processed and processing_status to UNLOGGED in Postgres, will I retain the property that when I commit the transaction, either both tables are updated, or neither? Even in the event of a crash?
I believe that if I set UNLOGGED, the difference is that in a crash, it will always revert both, whereas in LOGGED mode, the WAL could be used to include a few extra in-progress transactions right during the crash. Is this understanding accurate?


texts:
I'm familiar with Git, I use it every day for web projects. But I'm trying to use it with an Oracle DB, mainly to track different procedures and packages.

This Oracle DB lives on a different server, not my local machine, and I am not the DBA.

Is it still possible for me to configure a solution to track this information, and if so, how? I wouldn't have the first clue as to what folder I would initialize as the repo.


texts: Would elasticsearch or RavenDB be better for fueling a statistics engine/random forest?

texts:
(Note: this question exists on StackOverflow as well but I thought it might have a better reception here.  If it proves this is the better place, I'll close/ask to migrate/link to this.  Also, if it doesn't really belong here, I'd be happy to delete it.)

I've been looking at the following NoSQL databases for the next phase of my project:

elasticsearch
RavenDB

elasticsearch positions itself as primarily serving advanced search scenarios while RavenDB positions itself as a document-oriented-database.

Primarily, the document will be around videos.  Each has a natural id.  That will be the key of the document.

Around that, I add other content in fields which will not necessarily be scalar or flat, as the information will come from a number of different sources with different structures.

For example, there will be content from the video provider's Atom feeds, blog posts that have the video embedded in it, and other pieces of data from a data warehouse project.

There is no set structure across all of the items (each of them will be very domain-specific, actually), the only thing that will relate them is the natural key of the video mentioned above.

That said, once I have this information in one of the above solutions, I'll want to do a number of things with it:

Cull it to help populate variables in a random forest in order to make classifications about the videos
Provide general search on the videos (general free-text, not based on the results of the random forest) through a web-based front end (ASP.NET MVC if you must know)

There are some requirements:

I will more than likely be in a ASP.NET shared web hosting environment.  This means I'll have one machine, and won't have access to set up a service.  Something embeddable will be very helpful.
The ASP.NET environment will be hosted in IIS, so the embeddable aspect will have to survive app-domain recycling.
I'll want to create new indexes based on the results of the statistical analysis which I can easily fascet which will help with the search on the site.
Support for autocomplete functionality (I know this isn't an "out-of-the box" request, but being able to get to that point is important).
Rich synonym support (there's a number of them in the type of videos I'm indexing content around)

I'm also open to services, such as Truffler, although I do have concerns about the cost (and in Truffler's case, a little concerned about latency between the data centers, because the requests will come from the web host on the West coast, or from a back-end process on the East coast).

Additionally, I don't feel that one solution needs to fit all the requirements.  I'm more than fine with having one serve one purpose and having another serve another purpose.  Granted, migrations suck, but migrating between these two document stores is a little easier (and I don't expect them to use the same document structure, necessarily).


texts: How to delete large amount of data from a sql table?

texts:
We ran out of disk space and we have a table with about 10million records.

We'd like to delete the old records which are about 2million.

What are the impact of just deleting these using a simple DELETE command on production?

What's the best way to do this?


texts: ProxySQL equivalent of MaxScale schemarouter

texts:
background

My employer developed a web application we provide on software-as-a-service terms to our customers. To allow for multiple customers with a huge mass of data to be stored in a database, we chose to let the application create a schema per tenant. So if we had 5 customers we had something along the lines of

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tenant_1a          |
| tenant_d2          |
| tenant_yf          |
| tenant_ok          |
| tenant_8n          |
+--------------------+

Edit:
Numbers and letters represent a hash around 32 chars long.

Tenant schema names are in fact not numeric but hashes of certain facts during creation. So not really predictable until creation. This prevents us from preparing query rules in advance.

what we have/do now

For now we are good with this and only run a mariadb galera cluster of three nodes behind a maxscale readconnroute balancer. But we will eventually hit a barrier, where adding nodes to this cluster won't do, because the overall data size won't fit on disk and/or the amount of tables will kill performance.

To keep the complexity of the applications database layer low, our devs would like us to handle the routing transparent from the viewpoint of the application: they want the application to just to talk to one "server" and not care about where which tenant is located physically.

To expand our application cluster to multiple mariadb galera clusters we could use maxscales schemarouter which exposes all schemas on all connected sub-clusters as if there was only one server. This fits perfectly into our devs expectations.

Now, a few months ago ProxySQL entered the scenery of database proxies and claims better performance paired with greater flexibility among other stuff.

actual question

We can route queries based on hard-coded schema names, but would refrain from doing so as this would mean to create/update them each time a tenant is created/deleted.

How could we replicate the dynamic behaviour of maxscales schemarouter with proxysql query rules, if at all?


texts: How do I extract text from string between underscores with mysql?

texts:
I need to derive a column based on text from another column.
For instance, in the 'channel' column I have this text:

"RbkSEM_google_ecom-_-Branded-_-Core-_-Realdock"

I need to pull out "Branded" and put it into a column called 'derived_channel'

The positioning of the text I need to pull out will always be after the 3rd underscore and before the 4th underscore.

Thanks for help!


texts: Does my table qualify for Clustered Columnstore index?

texts:
I have a large table with 30 columns in MSSQL 2014.
The data will be inserted in bulks several times a day and the data should be kept for a month.

The purpose of it is to select aggregated queries. So it seems that Clustered Columnstore index is perfect for this.
Except that one column is varchar and holds URL values that usually don't repeat.
Will it work well with Columnstore? I worked with it , but only on data that hold numbers or repeating strings (like domain).

UPDATE
1. For purpose of importing and purging the table will be partitioned.
2. I don't have the exact DDL right now, because we are at the design stage.
Thanks


texts: Performance hits with MySQL Left Joins and Order By

texts:
There are a lot of questions out there regarding this issue, but most answers seem to be very case-specific. I've tried to make this general so it'll help others too.

The Issue

We have a smallish table 'products_keys' (about 10,000 rows) that contains records of products. We have a second table 'products_ordering' that contains information on how to order these records when displayed; essentially, a method to override standard alphanumeric ordering. If table 'products_ordering' doesn't contain additional ordering information, then it fallsback to standard ordering. In either case, the records from A must be returned, so a left join is used.

On trying to order by the values held in this left joined table, the execution time increases by 2-3 orders of magnitude. While the performance hit is JUST about acceptable at the moment, this will rapidly degrade as the DB grows and is not scalable.

The query

SELECT pk.p_key_id
FROM products_keys pk
LEFT JOIN products_ordering AS po ON pk.p_key_id = po.p_key_id
ORDER BY po.order_priority ASC

Explain

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  pk  index   NULL    activated   1   NULL    12342   Using index; Using temporary; Using filesort
1   SIMPLE  po  ref     p_id,p_key_id,order_priority    p_id    4   plv4_dev.pk.p_key_id    1

I presume that this is caused by the fact MySQL can't find an appropriate index to perform the sort. I tried adding an index on product_ordering (p_key_id, order_priority) but this didn't do much.

Are there methods to allow this type of query to use an index when sorting? Alternatively, is there a better query form I should be using?

Thank you very much in advance


texts:
I'm working on a transition of a web-site, which uses MySQL database as a backend.
The transition involves copying the production database -- into another on the same server. We do this by taking a backup into a file (with mysqldump), then loading it into the new DB. Nothing out of the ordinary here.
The problem is, though it takes under a minute to create the dump, loading it into a newly-created DB takes about 30 hours.
The server is reasonably beefy and uses SSD magnetic drives. While the loading is running, I can see mysqld process becoming quite busy initially, using up to 100% CPU, then dropping to 3-4%. Running iostat 1 shows writes of well under 1Mb/s, indiscernible from regular filesystem activity on a non-idle system.
The relevant section of ~/.my.cnf reads:
[mysqldump]
extended-insert=false
lock-tables=false
single-transaction=false

The way backups are taken, the table-creation statements in the backup look like:
DROP TABLE IF EXISTS `acl_groups`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `acl_groups` (
  `group_id` mediumint(8) unsigned NOT NULL DEFAULT 0,
  `forum_id` mediumint(8) unsigned NOT NULL DEFAULT 0,
  `auth_option_id` mediumint(8) unsigned NOT NULL DEFAULT 0,
  `auth_role_id` mediumint(8) unsigned NOT NULL DEFAULT 0,
  `auth_setting` tinyint(2) NOT NULL DEFAULT 0,
  KEY `group_id` (`group_id`),
  KEY `auth_opt_id` (`auth_option_id`),
  KEY `auth_role_id` (`auth_role_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `acl_groups`
--

LOCK TABLES `acl_groups` WRITE;
/*!40000 ALTER TABLE `acl_groups` DISABLE KEYS */;
INSERT INTO `acl_groups` VALUES (2092,26,1,0,1);
...

If I run the loading mysql command with -v flag, I can see -- after the initial very fast progress -- it beginning to crawl through each INSERT INTO line at the rate of only about 1-2 per second...
With "keys" disabled before loading each table, why does it become this slow?


texts: Database design for application with multiple useres

texts:
We have a task to design a software using entity frame core and a Central SQL DB. The software will have multiple users with RBAC(Role Based Access Control). DB design shall implement user group table and users with many to many relationship.

I've read about RBAC and RLS (Row Level Security) in SQL but I can't link it to code. It will take alot of effort to create secured layer in the Database but I don't know how to use this in the code. I mean putting effort in the database design tables will be implemented in the code using the poco-class but putting all this effort in data security will affect the performance and will make the software very dependent on the the database

How can I implement RBAC in the code?
Where to start searching?
Should I just but my restrictions in the code using the queries and GUI?
Should I depend on the DBMS to manage my security or use it just as a backup layer?
What is the best practice or design pattern for the application and the Database? Should I map users in the database with RBAC and RLS and then just using them to sign in the application?

Note: it is a limited scale project with less than 100 user in the Database using a company intranet


texts: Sort table so that linked rows show up as subsets in overall alpha sort

texts:
I have this member table where memberships are either single or family, and if family they can be family head or dependent. When outputting a view of the membership, it currently sorts alpha ascending, but I'd like to find a way to put all of the dependents of a family head below the head.

The relevant columns are:

isFam ( tinyint(1) )
isFamHead ( tinyint(1) )
memKey ( int(11) )

Is there some way, in a mysql query, to sort the table alphabetically for single members and family heads, but put the dependents of the family head directly beneath the family head?

(This sort of works right now if all family members have the same last name, but in many cases I find they don't. And different families can have the same last name as well, so ...)

From the comments:

memKey identifies a membership. For a single membership, there will be only one row with that memKey; for a family membership, all members of a given family will have the same memKey.
For a single membership, isFam is 0; for all members of family memberships, isFam = 1.
The "family head" of each family membership has isFamHead = 1; there should only be one row where isFamHead = 1 per memKey. (Ignore value of isFamHead for single members)


texts: How is the new system stored procedure sys.xp_delete_files different from sys.xp_delete_file?

texts:
SQL Server 2019 introduced a new (and undocumented) system stored procedure:

sys.xp_delete_files

What, exactly, does this new extended stored procedure do? How does it differ from xp_delete_file that has been around for years? Does it replace xp_delete_file?


texts: I need to put some random values into database, but I don't want to end up with completely randomized text (like 7hfg43d3). Instead I would like to randomly pick one of values supplied by myself.


texts: RDLC report not working after upgrading from Visual Studio 2008 to Visual Studio 2019 with SQL Server 2016

texts:
After RDLC report updated with Visual Studio 2019 is not working with SQL Server 2016:
Microsoft.Reporting.WinForms.LocalProcessingException
  HResult=0x80131500
  Message=An error occurred during local report processing.
  Source=Microsoft.ReportViewer.WinForms
  …..
…

Inner Exception 1:
DefinitionInvalidException: The definition of the report '' is invalid.

Inner Exception 2:
ReportProcessingException: The definition of this report is not valid or
supported by this version of Reporting Services. The report definition may
have been created with a later version of Reporting Services, or contain
content that is not well-formed or not valid based on Reporting Services schemas.

Details: The report definition has an invalid target namespace
'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition'
which cannot be upgraded.

From How to change "deployment target version" on SSIS 2019? I went to project properties in VS 2019 but still can't find "Target Server Version". I was told to update the "SQL Server Integration Services Projects" extension to the latest version.
I currently have SSIS version 13 installed on SQL Server 2016 and I run local rdlc reports, not the server versions. Do I still need to update SSIS? The reason I am hesitant is several other DB in the server and trying not to create issue for some other legacy software.


texts: mysql information schema database last used datetime

texts:
I have a lot of tables in my database, almost 1,000. Most of them I am not using now.

Is there any way to know from the information schema tables when I have last used my tables?


texts:
I have several tables that I'm trying to combine with the below query. I'm using the following tables:

LoanOrigination: This contains loan characteristics such as asset value, loan term, etc. There is a single unique observation per loan. This table is indexed on LOAN_ID,SUB_SAMPLE and COLLATERAL_TYPE.
LoanPerformance: This contains the performance of all loans in LoanOrigination. Each row is a unique MONTHLY_REPORTING_PERIOD and LOAN_ID combination, and the table has been indexed on both.
CollateralData: This contains historical values based on the COLLATERAL_TYPE. The purpose of this is to estimate what loan's current value to the collateral.

The purpose of the query below is to combine these tables so that each row contains loan characteristics along with the delinquency statuses in the current and following month. However, the query is extremely slow. Is there anything that can be done to speed it up?

with

COLLATERAL_VALUES as (
    select
        COLLATERAL_TYPE,
        dateadd( day, 1-day(AsOfDate), AsOfDate) as ASOFDATE,
        Value as INDEX
    from LoanData.CollateralData
),

SAMPLE_LOANS as (
    select
        a.*,
        b.INDEX as INDEX_T0
    from LoanData.LoanOrigination a
    join COLLATERAL_VALUES b on b.ASOFDATE = a.ORIG_DATE and b.COLLATERAL_TYPE = a.COLLATERAL_TYPE
    where SUB_SAMPLE = 0
),

LOAN_STATE as (
    select
        a.LOAN_ID,
        MONTHLY_REPORTING_PERIOD AS CUR_DATE,
        CURRENT_ACTUAL_UPB as CUR_UPB,
        LOAN_AGE,
        cast(CURRENT_LOAN_DELINQUENCY as smallint) AS CUR_DLQ_STATUS
    from LoanData.LoanPerformance a
    where
        CURRENT_LOAN_DELINQUENCY <> 'XX' and
        exists ( select LOAN_ID from SAMPLE_LOANS )
),

LOAN_TRANSITION as (
    select
        c.*,
        a.CUR_DATE,
        a.CUR_DLQ_STATUS,
        a.CUR_UPB,
        a.LOAN_AGE,
        b.NEXT_DLQ_STATUS
    from LOAN_STATE a
    join (
        -- adding next state transition
        select
            LOAN_ID,
            DATEADD( month, -1, CUR_DATE ) as PRIOR_DATE,
            CUR_DLQ_STATUS as NEXT_DLQ_STATUS
        from LOAN_STATE
    ) b on a.LOAN_ID = b.LOAN_ID and a.CUR_DATE = b.PRIOR_DATE
    join SAMPLE_LOANS c on a.LOAN_ID = c.LOAN_ID
)

select
    a.*,
    CUR_UPB / ( ASSET_VALUE * ( b.INDEX / a.INDEX_T0 )) AS LTV
from LOAN_TRANSITION a
join COLLATERAL_VALUES b on a.CUR_DATE = b.ASOFDATE and a.COLLATERAL_TYPE = b.COLLATERAL_TYPE


texts: Position of a race result using database query?

texts:
The scenario is based around a race event (running/jogging)

Is there a way of getting a position of a person by using their race results. For example the quickest person to finish a race would be in a seperate table of race position with the value of 1. The second quickest person would get a result of 2, and so on. Just by using the race result of that individual (Data Type - Time).


texts: mongoaudit: Security through obscurity and how to limit connections from a specific subnet?

texts:
I run mongoaudit on a server. Those are the failed results:

I don't wish to hide my version number. In my opinion this is Security through obscurity

  In security engineering, security through obscurity (or security by
  obscurity) is the reliance on the secrecy of the design or
  implementation as the main method of providing security for a system
  or component of a system. A system or component relying on obscurity
  may have theoretical or actual security vulnerabilities, but its
  owners or designers believe that if the flaws are not known, that will
  be sufficient to prevent a successful attack. Security experts have
  rejected this view as far back as 1851, and advise that obscurity
  should never be the only security mechanism.

what other MongoDB experts do?

Howto whitelist networks? I wish to do that, but only know about net.bindIp. This only say on which virtual NIC mongod listens. Are there options to restrict a subnet?