texts: Aggregating rows from a condition in a linking table in SQL

texts:
I am having trouble wrapping my head around a query that gives me these results
I have a table that gives me Devices uses per day.
dbo.DailyUses
Uses |   Date   |  Device
3    | 2020-3-1 |    A2
5    | 2020-3-1 |    B3
1    | 2020-3-2 |    A2
12   | 2020-3-3 |    C4

I have another table gives me the possible ErrorCodes of each devices.
dbo.Code_SKU_Combos
ErrorCode |  Device
   12.24  |    A2
   12.25  |    A2
   12.26  |    A2
   12.24  |    B3
   12.26  |    B3
   12.25  |    C4

With those two tables, I want to get how much uses per day did each "errorcode cable devices" where used
Example Output
Uses |   Date   |  ErrorCode
8    | 2020-3-1 |    12.24
3    | 2020-3-1 |    12.25
8    | 2020-3-1 |    12.26
5    | 2020-3-2 |    12.24
5    | 2020-3-2 |    12.25
5    | 2020-3-2 |    12.26
0    | 2020-3-3 |    12.24
12   | 2020-3-3 |    12.25
0    | 2020-3-3 |    12.26

I was thinking of using window functions or join, but the fact that the two tables will be using repeating values just confuses me on creating Partitions and Group By. I feel like I am over thinking it.
I created a cross table of my date range and Error Codes, now I just need to create a SUM() function that looks at dbo.DailyUses based on a filter created from dbo.Code_Sku_Combos
Also I am using Microsoft SQL


texts: MySQL: SELECT the n-th item from an ordered result

texts:
SELECTing the top n elements from a table is easy:

SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id DESC LIMIT 1000;

Finding the n-th element with a subquery is quite straightforward:

SELECT MIN(id) FROM
    (SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id DESC LIMIT 1000) s;

Assuming that there is a BTREE index on the relevant columns (id), is this approach efficient? Are there any better alternatives?


texts: Find users having grants on a table in MySQL

texts:
We grant access to users with the following
grant select, insert, update, delete on product_events to 'product_admin'@'%' ;

Now, like this there are a lot of tables, and a lot of users.
I want to find all users having any grants on a particular table
That is, given product_events table, I would like to get 'product_admin'@'%'


texts: SQL Maintenance Tasks on Azure [Ola Hallengren Scripts]

texts:
I am trying to implement the SQL maintenance scripts from Ola on my Azure elastic pool databases. I did the steps as provided in this link.

I could create the job, but I always get this error after running the job manually:

Does anybody have some experience with these maintenance tasks on Azure?


texts: Does the "don't use errors for flow control" axiom apply to postgres?

texts:
In traditional programming there is an axiom that states, "do not use errors for flow control". A general example is to throw an error then catch the error instead of using an ordinary conditional statement or break statement. This is harmful because the application has to unwind the call stack and invoke some relatively expensive exception handling logic versus simply handling a conditional statement.

I am working with a postgres system where a user is calling a function in postgres that throws an error instead of returning no rows when a condition is not met. The condition is roughly a "this input value does not exist, nothing to do" as opposed to a truly exceptional case.

Does postgres incur runtime costs when an error is thrown in a similar manner traditional programming? In other words is using exceptions as flow control in postgres harmful to performance or just sloppy?


texts:
Looking over the docs, I didn't see any mention of row-level encryption.

Lets assume I have a database of highly classified information that needs to get locked down. How can I implement a table with row-level encryption in postgres? Failing row-level, what's the next best thing I can do, and what are the drawbacks?


texts: Run SQL query with python in SQL Server raises error: SqlSatelliteCall error: Unsupported input data type in column

texts:
I am trying to run Python in SQL Server and want to define a variable which contains the result of a simple select query.

exec sp_execute_external_script
@language = N'python', @script = N'

import pandas as pd
inventory = pd.DataFrame(sql_inventory)

',
@input_data_1 = N'SELECT top 10 * from inventory',
@input_data_1_name = N'sql_inventory'

The structure of the SQL server table (inventory) is as below:

ProductID numeric(18,3)
RegionID numeric(18,3)
ShopCode int
QTY float
Date varchar(6)
Amount float
ID bigint

But I get the below error:
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. Msg 39019, Level 16, State 2, Line 1 An external script error occurred:

  Unsupported input data type in column 'ProductID'. Supported types:
  bit, tinyint, smallint, int, bigint, uniqueidentifier, real, float,
  char, varchar, nchar, nvarchar, varbinary. SqlSatelliteCall error:
  Unsupported input data type in column 'ProductID'. Supported types:
  bit, tinyint, smallint, int, bigint, uniqueidentifier, real, float,
  char, varchar, nchar, nvarchar, varbinary. STDOUT message(s) from
  external script: SqlSatelliteCall function failed. Please see the
  console output for more information. Traceback (most recent call
  last): File "C:\Program Files\Microsoft SQL
  Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py",
  line 406, in rx_sql_satellite_call rx_native_call("SqlSatelliteCall",
  params) File "C:\Program Files\Microsoft SQL
  Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py",
  line 291, in rx_native_call ret = px_call(functionname, params)
  RuntimeError: revoscalepy function failed.

Should I specify data types every time I want to read from a query?


texts: Maintainability of schema changes during runtime of an app

texts:
We have an application which accepts JSON objects from X number of third party services and unfortunately we're locked into using SQL to store it. The objects must be flat (no nesting), but each third party can have a different set of properties.
I feel like it would be a better approach to have a static schema - for example, there would be a "Definitions" table that contained an entry for each unique property, then for each object there would be a row for each property that references the definition, for example - given:
{
  "prop_name": "John Doe",
  "prop_tel": "+15555555555"
}

There would be the following in SQL:
Definitions table -
  | Id | Ref       | ...
  | 2  | prop_name |
  | 3  | prop_tel  |

Entities table -
  | Id | ...
  | 6  |

Values table -
  | Id | FK_DEF | FK_ENTITY | Value        | ...
  | 9  | 2      | 6         | John Doe     |
  | 10 | 3      | 6         | +15555555555 |

This is slightly simplified from what might be a final design, but in practice this would let you store any format of data in SQL. The expense here is that lookups on this data is hard, you need to use a pivot or nested selects. (and we do need to do lookups on this data, sorting the entities by the values table, etc)
My colleague is convinced that this is ridiculous and we should just be dynamically modifying the schema as we go - either:

Adding a new table for each unique json object we need to store
Adding a new column to a single table for each unique property. Note that properties named the same coming from different third parties or data sources still need to be treated as separate columns.

We can never delete data and hope for this to be a very scalable application - containing hundreds of thousands or potentially millions of records.

Keeping in mind we know both of these options are crap but don't have a choice: Can anyone give me an idea of what the best practices are, and pros/cons of each approach? None of us have had to do this before and we are worried we will get locked into a path and hit road blocks later when trying to scale this. Off the top of my head having absolutely no experience, here's the list I'd make:
Static Schema
Pros:

Predictable
Maintainable
Not difficult to add new data format or perform inserts

Cons:

Slower / complicated lookups (pivot or nested select)
More complicated inserts / updates (for entities that exist)

Mutating Schema
Pros:

Fast lookups
Easy updates (for entities that exist)

Cons:

Schema out of our control, can get massive and hard to maintain over time
Database migrations to new schema gets harder and harder as time passes
Its not recommended for production app to have access to change schema

What am I missing and what would be the best path to take considering long-term scalability and maintenance?


texts:
I have two tables tb1 and tb2, tb1 doesn't have any indexes on it. I populated tb1 with 1000000 rows and tb2 has 500 rows in it and with a clustered index on ID column.

To understand nested loop join, I have used below query:

SELECT *
FROM tb1
INNER JOIN tb2 ON tb1.id=tb2.id
OPTION(loop join);

I got below execution plan for this:

Tb1 which doesn't have index is scanned and cost is 2 %, whereas index is being used on tb2 and cost is 98%.

My question is:

How to understand estimated operator cost shown for above snip (138.236)

From the above snip of table scan (whose cost is 2%), number of executions are 24, will that mean sql reads rows in batches stored in memory, and for each row it did a seek operation from tbl2?

Can someone please explain to me the execution plan and also any pointers to know more about force scan, force index when I pressed F4 after clicking an operator.


texts: DELETE/TRUNCATE from a large table makes MariaDB crashing

texts:
I try to execute the two following queries in MariaDB :

DELETE FROM `db_test`.`table1`;
DELETE FROM `db_test`.`table2`;

The problem is : deleting from the first table is ok (it takes a few seconds, nothing not usual), but deleting from the second table seems impossible. The query runs for a long time (more than 12 hours), then MariaDB crashes. The only solution here is to reinstall completely MariaDB, because MariaDB service won't launch anymore.

I think that's because table2 is very large, at the opposite of table1 :

table1 contains 135k lines, 18 MiB
table2 contains 6,2 millions of lines, 1,1 GiB

The same goes with TRUNCATE. The software I use is HeidiSQL.

Do you happen to know how to handle tables like this one ?


texts:
Can you assume that the result of the string_split function returns elements in order? In short, is the following code correct?

DECLARE @tags NVARCHAR(400) = 'clothing,road,touring,bike'
DECLARE @FIRST nvarchar(100);

SELECT TOP 1 @FIRST = value
FROM STRING_SPLIT(@tags, ',');


texts: Inconsistent presence of view in Postgres system tables

texts:
I am perplexed here. I have two environments, let's say TEST and PROD.

In both environments' Postgres DBs there is a view foo_view visible in my DB client (DBeaver). However, only in PROD can I find that view in the system tables:

select * from information_schema.tables WHERE table_name = 'foo_view';
select * from information_schema.views WHERE table_name = 'foo_view';

When executed in PROD, the above SQL returns data that corresponds to the client rendering but not in TEST. I was not the DBA who introduced that view in either of the instances.

I am looking for an explanation as to which possible scenarios could lead to that view not being in the system tables but still visible in the client UI.


texts: Postgres Performance Over Group by with MAX and MIN

texts:
I just want to retrieve the data for specific location of customers with their ever first and last purchases made in the system.

Table details :

CREATE TABLE customer_location ( id UUID, location_id UUID, customer_id UUID );
CREATE TABLE customer_receipt  ( id UUID, receipt_id UUID, customer_id UUID );
CREATE TABLE receipt_product ( id UUID, receipt_id UUID, product_id UUID, txn_dt_tm Timestamp, status TEXT, is_active boolean);

Query :

SELECT cr.customer_id, min(txn_dt_tm), max(txn_dt_tm)
FROM customer_location cl
JOIN customer_receipt cr ON cl.customer_id = cr.customer_id AND cl.location_id = 'UUID here'
JOIN receipt_product rp ON rp.receipt_id = cr.receipt_id AND status = 'PURCHASED'
    AND is_active = true
GROUP BY cr.customer_id

  INDEX :

  receipt_product : Index applied on (status and is_active).

  Other tables are implemented with the primary key index.

Though the query engine performs the SEQ scan rather index. Since I see this could be the huge load for index search and thus the engine picked up the Sequential scan. The time taken to load the data is approximately 36 Seconds which is a huge load time.

Here is the query plan http://explain.depesz.com/s/5hV.

HashAggregate  (cost=1023419.340..1023425.980 rows=664 width=24) (actual time=42811.045..42811.152 rows=142 loops=1)   ->  Hash Right Join  (cost=464863.230..1011575.600 rows=1579165 width=24) (actual time=17306.921..41755.433 rows=1101749 loops=1)
          Hash Cond: (receipt_product.receipt_id = customer_receipt.receipt_id)
        ->  Seq Scan on receipt_product  (cost=0.000..405877.220 rows=6981890 width=24) (actual time=0.051..14179.158 rows=6890896 loops=1)
                Filter: (is_active AND (status <> 'PURCHASED'::uuid))
                Rows Removed by Filter: 2909925
        ->  Hash  (cost=434327.670..434327.670 rows=1579165 width=32) (actual time=17306.658..17306.658 rows=1101749 loops=1)
                Buckets: 65536  Batches: 4  Memory Usage: 17257kB
              ->  Hash Right Join  (cost=44.660..434327.670 rows=1579165 width=32) (actual time=2345.366..16229.130 rows=1101749 loops=1)
                      Hash Cond: (customer_receipt.customer_id = customer_location.customer_id)
                      Join Filter: (customer_location.location_id = 'location_id'::uuid)
                      Rows Removed by Join Filter: 7182859
                    ->  Seq Scan on customer_receipt  (cost=0.000..299670.480 rows=8283848 width=32) (actual time=0.004..7416.387 rows=8283831 loops=1)
                    ->  Hash  (cost=33.180..33.180 rows=918 width=32) (actual time=1.551..1.551 rows=918 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 58kB
                          ->  Seq Scan on customer_location  (cost=0.000..33.180 rows=918 width=32) (actual time=0.006..0.759 rows=918 loops=1)

By re-writing the same query as postgres function which would iterate every customer of location and would fetch the max and min date of that customer. Which performs 6 times faster ( 6 seconds aprx. ) but when data grows huge, that would take time.

I strongly hope to see the false bucket here also like to have the experts' Suggestions.

UPDATE

I tried disabled the seq scan. still that takes same amount of time with index scans. So Query plan picked up the right way to execute. What are all the possibilities to reduce the execution time here. Is there any settings which would deal with the execution speed.

I also adding the query tuning setting parameters here from my DB.

cpu_index_tuple_cost        0.005
cpu_operator_cost           0.0025
cpu_tuple_cost              0.01
effective_cache_size        464300
random_page_cost            1
seq_page_cost               1

Thank you.


texts:
I need to store binary data files in a PostgreSQL database which runs on an Ubuntu server. Initially there will be a few dozen files of approximately 250kb in size each. However, the number of files will increase over time. I may at times need to extract data from the files for other downstream analyses.

I've done some research regarding the age-old question of storing binary data as BLOBs or references. Both obviously have their pros and cons. Are there any specific issues relating to PostgreSQL that I should be aware of? Is one method or the other preferable if I want to extract data from the files, either via a PostgreSQL function or via an external Python program?

If I were to store the data files directly in the database, would it be better to store them in a separate table with a foreign key referencing the "main" table, rather than in the table containing all the other fields?

I have read the question and answers here; a comment there suggests that storing binary files by reference (in the filesystem) on Linux is better. My questions here relate to PostgreSQL specifically and to extracting data from the files for various analyses.

Update: Similar question.


texts: How to store values of all columns in an index in PostgreSQL?

texts: As described here: https://devcenter.heroku.com/articles/postgresql-indexes#managing-and-maintaining-indexes  indexes do not store the entire row data themselves. Is it possible to store the whole row in the index for fast retrieve? I don't care about space.


texts: Find database user name from sysprocesses

texts: I know that this is a strange requirement but I need to find out database user name(not the login name) from sys.sysprocesses for all the currently running processes. For e.g. select spid,dbid, uid from sysprocesses where uid corresponds to database user in that particular database. So what I want is one more column which can get me database user name from uid. Please let me know if this is possible with just a simple select query.


texts: How to dump an advanced revelation database?

texts:
Retail store I work with has an ancient inventory/accounting/receiving system that is based off Advanced Revelation.  They run the DOS and Windows clients.  I'd like to get a dump of their data to work on migration or at least create other views of their data, without paying $$$ for the vendor do to this service.

I can find very little documentation of Advanced revelation.  Can anyone help?


texts: How to get Time in milliseconds & How to add 5 minutes to the milliseconds in mySql

texts: How to get current time in milliseconds & how to add 5 minutes to it in mySql


texts: restore mongodb sharded cluster to another server

texts:
MongoDB version is 2.4.12
Production Server has 3 shards and each shard have another 2 replica sets.
Config server & router also runs on these servers.

Backup procedure :
1. Shutdown one of replica set on each shard.
2. We use mongodump to backup config server.
3. Cold backup od /data & /log folders.

Restore Procedure :
Restore will happen on only 3 servers so there will not be any replica sets.
Server name where it is getting restored, is different than where backup happend so server name is going to change.

Can someone put high level steps how do I restore ?
** Restore Config server from dump ( Is this 1st step)
** Copy all /data and logs to these three servers.
** Is there anything I need to change in config server or any other settings since Production server did have replica set configuration but this new server will not have replica set.

Thanks


texts: How do I rewrite the below Oracle trigger so it will work on SQL Server?

texts:
I am migrating from Oracle to SQL Server using SSMA and I get thrown an unparsed SQL error on the below code. Can anyone help me convert this to T-SQL? Thanks in advance.

CREATE OR REPLACE TRIGGER "ISTRAINING"."INSERT_COURSE_DELEGATES" BEFORE
INSERT ON "COURSE_DELEGATES" FOR EACH ROW declare
 row_locked exception;
 pragma exception_init (row_locked, -54);
begin
 begin
  select next
  into :new.COURSE_DELE_ID
  from ISTRAINING.sequence
  where tname='COURSE_DELEGATES' and tcolname='COURSE_DELE_ID'
  for update of next nowait;
  exception
  when row_locked then
      raise_application_error (-20002,'Database temporarily locked');
 end;
 update ISTRAINING.sequence
 set next=next+1
 where tname='COURSE_DELEGATES' and tcolname='COURSE_DELE_ID';
end;


texts: Is there anyway to change the standard SQL SERVER reports? And/OR See the SQL Behind it?

texts:
SQL SERVER (through Management Studio) offers quite a few standard reports, but I don't see how you could change the reports. I don't even see an option for extracting/obtaining the SQL Code behind it.

Is there any way to achieve this? Specifically, I am more interested in security reports (although it'd be nice to get code for all of them).

Thank you in advance


texts:
I am trying to maintain a database that contains a table with 40 billion rows (7.2 terabytes) on a server using InnoDB as a storage engine and MariaDB with MySQL 5.5.

When my database reaches about 2.5 terabytes, I can no longer insert data into the table at the rate required in production. Data in the table is rarely queried after 24 hours. The table has a primary key and one secondary index. After doing quite a bit of research, it seems like understanding the InnoDB buffer pool will be critical if I'm going to solve this problem. This is obviously too much data to fit into the buffer pool. I have a few ideas about how I can improve performance by increasing the probability data from the last 24 hours will be in the buffer pool, but it's difficult to test them all with such a large amount of data. How will the InnoDB buffer pool behave in each of the following situations? Is one idea obviously better? Or are they all bad?

Split the large table into partitions by time such that each partition's data and index fit into the buffer pool. - https://mariadb.com/kb/en/partition-maintenance/ suggests that this should improve performance, but I've seen conflicting information about how indexing works for partitioned tables. Is it one massive index? Or several smaller indexes that will fit into the buffer pool? If it's one large index, it's hard to see how this would help.
Create 2 time-partitioned tables. One table will be a large table of archived partitions, and one table will hold only one "active" partition with data that is likely to be queried (maybe a week). When I transition to the next partition in the active table (next week's data), exchange the recently active partition (last week's data) into the archive table. - This seems advantageous because the active table is guaranteed to fit into the buffer pool and queries that might execute a full table scan will not read data that will purge the active data from the buffer pool because the old data is in a different table. However, I'm assuming that when I exchange the recently active partition into the archive table, everything will come to a halt while the index for the large table is read from disk into the buffer pool and recalculated. Then there will be some time where performance suffers after that until the active data makes its way back into RAM.
Create 1 time-partitioned table that holds archived data, and one small table that is minimal in size (probably 24 hours worth of data). Then copy the data more than 24 hours old out of the small table into the partitioned archive table. - It's hard for me to imagine how this could be a good option unless copying data is somehow faster than moving an entire partition.

Any insight is greatly appreciated!


texts: Very Simple MySQL Inner Join Extremely Slow

texts:
I have setup our first MySQL instance and am testing some simple queries. I am attempting to join two tables based on a 9 character string. The field is indexed on both tables and yet the query is taking longer than 10 minutes. When I run the same query on our MSSQL server it runs in under 1 second. I must be doing something wrong.

table_a
1,000,000 rows

table_b
5,724,842 rows

query:

select count(*)
from table_a
inner join table_b on table_a.field = table_b.field

When I run EXPLAIN I see:

select_type | table | type | possible_keys | key | key_len | ref | rows| extra
SIMPLE | table_a | index | ix_name | ix_name | 30 | NULL | 962446 | Using index
SIMPLE | table_b | ref | ix_name | ix_name | 30 | table_a.field | 1 | Using where; Using Index

When the query is running the server pegs 1 CPU Core at 100% (the other 7 remain idle). The tables are using InnoDB and I do have InnoDB set to 1 file per table.

Any ideas what the heck I am doing wrong?

UPDATE

I was on MySQL 5.5, I have updated to MariaDB 10.1.7 to give that a shot.  No change.  The fields in both tables are defined as varchar(9) utf8_general_ci NULLABLE.  The indexes are BTREE, not unique, not packed, NULL yes, Collation A. I moved the two tables in question to ARIA storage engine just to give that a shot as well, no change.

I also noticed mysqld is using almost no memory (thanks @Uueerdo).  I am wondering if this has something to do with it.  I am not sure what settings I need to change exactly.


texts: Reporting Services TempDB keeps going into restoring mode

texts:
I am looking into an issue with SQL Server 2008 Reporting Services. It has been working fine until yesterday morning. Now I notice that ReportServerTempDB keeps on going into Restoring... mode. I restored it to online mode with RESTORE DATABASE [ReportServerQ4TempDB] WITH RECOVERY.

The reports work fine after this and then after 10-15 minutes, then ReportServerTempDB goes back into Restoring mode. Report execution gives the following error:

  An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError)
  For more information about this error navigate to the report server on the local server machine, or enable remote errors...

Any suggestions why it is doing that.

I have checked the following

Memory - 50% available
CPU - only 10% utilized
Storage - 30% space remaining

There are multiple instance of SSRS running and SQL instance hosting the database for SSRS on the same server.

I have checked that AutoClose is not enabled for this database, and I have discovered that the database goes into Restoring... mode every hour, when our log backup job runs.


texts: mysql - order by first condition in where clause

texts:
I have a table consists of the following fields

id, name

with the following values

1, ciro
2, test ciro
3, ciprox
4, other
5, other

i would like to get all the values that begin with "ci" or contain "ci" but show me the results before they start with ci and then the rest.

the query

select * FROM table WHERE name like 'ci%' or name like '%ci%' order by name;

I returns

1, ciro
2, test ciro
3, ciprox

I want

1, ciro
3, ciprox
2, test ciro

therefore before the rows in which name begins with "ci" and then those that contain

is there a way without using two queries to obtains the result?


texts: auto populate common columns of all tables using triggers

texts:
I have the following 4 tables:

course
section
student
enrollment

All of them have 4 columns in common that I want to auto-populate before an insert or update.
At first the simple insert trigger was giving me an infinite recursion error.
After researching for a while I found out that using views and an instead of trigger could be a better solution. Now when I insert a row with course_no, description, cost and prerequisite it gives an error that CREATED_BY can't be null. However, the values should have been automatically inserted by the trigger.
I've been working on it for a few hours now, but had no luck.
The course table is defined as
CREATE TABLE COURSE
(
COURSE_NO NUMBER(8, 0) NOT NULL
, DESCRIPTION VARCHAR2(50 BYTE) NOT NULL
, COST NUMBER(9, 2)
, PREREQUISITE NUMBER(8, 0)
, CREATED_BY VARCHAR2(30 BYTE) NOT NULL
, CREATED_DATE DATE NOT NULL
, MODIFIED_BY VARCHAR2(30 BYTE) NOT NULL
, MODIFIED_DATE DATE NOT NULL
)

The view for course is defined as
CREATE VIEW v_course
AS SELECT CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE
FROM COURSE

The instead of trigger is as follows
create or replace TRIGGER course_trRg INSTEAD OF
INSERT ON v_course
FOR EACH ROW
BEGIN
INSERT INTO course (
    created_by,
    created_date,
    modified_by,
    modified_date
) VALUES (
    user,
    sysdate,
    user,
    sysdate
);
END;

This is how I'm trying to insert
INSERT INTO v_course (
course_no,
description,
cost,
prerequisite
) VALUES (
1231,
'desc',
1,
1
);

All the tables have the columns created_by, created_date, modified_by, modified_date in common.


texts: How to avoid multiple SELECTs from a bag of properties table?￼

texts:
I have a table with key-value pairs for managing user settings. This approach is known as the “bag of properties” and one of its benefits is you don’t need schema changes when a new property is required. You simply add a record to the bag of properties table instead.

Now, I need to retrieve multiple properties inside a function (or stored procedure). But since the results are returned as multiple rows, with this approach I cannot do a simple:

SELECT prop1, prop2 FROM settings;

The query returns multiple rows so the results cannot be saved in local variables with the SELECT col INTO var statement either. Yes, I can run two or more consecutive SELECTs but this doesn’t seem like a particularly efficient and elegant solution:

SELECT value FROM settings WHERE key = ‘prop1’;
SELECT value FROM settings WHERE key = ‘prop2’;

A couple of ideas I have considered:

FOR..IN loop

The function has OUT parameters, in this case called prop1 (VARCHAR) and prop2 (BOOLEAN):

DECLARE
  settings_row RECORD;
BEGIN
  FOR settings_row IN
    SELECT key, value FROM settings
  LOOP
    CASE
      WHEN settings_row.key = 'prop1' THEN
        prop1 := settings_row.value::VARCHAR;
      WHEN settings_row.key = 'prop2' THEN
        prop2 := settings_row.value::BOOLEAN;
      -- more cases handled here in case you needed other properties
    END CASE;
  END LOOP;

  RETURN;
END;

Cross join

Cross joining the settings table with itself, forcing the two properties to end up in columns. You could perhaps live with that if you needed just two properties but with any other value requiring yet another cross join this doesn't seem like a particularly scalable solution.

SELECT
  s1.value AS prop1, s2.value AS prop2
FROM settings s1
CROSS JOIN settings s2
WHERE
  s1.key = 'prop1' and s2.key = 'prop2';


texts: Having issues in assigning mysql datatypes

texts:
I am working first time with database. I had never inserted data into database.  I want to insert below csv into mysql database. But I facing issues with data types. What type of data type I should assign to the variables.

   "cell","id","too","Issue","Valid","DPT","RH","TMP","U","V"
"1",1,22383,"2015-01-15 00:00:00 GMT","2015-01-15 12:00:00 GMT",267.800018310547,91.5999984741211,269.040008544922,-0.529999971389771,-1.78999996185303
"2",2,24532,"2015-01-15 00:00:00 GMT","2015-01-15 12:00:00 GMT",272.600006103516,88.5,274.259979248047,-1.58999991416931,-0.239999994635582
"3",3,23936,"2015-01-15 00:00:00 GMT","2015-01-15 12:00:00 GMT",271,88.2000045776367,272.72998046875,-0.429999977350235,-1.01999998092651

Could anyone help me in figuring out the data types of the columns?

I have assigned datatypes below but I am getting so many values while inserting data:

CREATE TABLE `raster_cell_data` (
  `cell` varchar(100) NOT NULL,
  `id` varchar(100) NOT NULL,
  `too` int(14) NOT NULL,
  `Issue` datetime DEFAULT NULL,
  `Valid` datetime DEFAULT NULL,
  `DPT` varchar(100) DEFAULT NULL,
  `RH` varchar(100) DEFAULT NULL,
  `TMP` varchar(100) DEFAULT NULL,
  `U` varchar(100) DEFAULT NULL,
  `V` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I am loading data using the below command:

LOAD DATA LOCAL INFILE 'C:\\Users\\vkaushik\\Desktop\\Temp.csv'
    INTO TABLE raster_cell_data
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

Getting Error 1234: out of the range for many variables while loading. And only 101 rows are getting inserted out of 901.

Thanks.


texts:
I have many partitions to analyze and don't want to paste every single line to terminal so I'm trying to run a procedure for it, but I got complitaion error:

CREATE OR REPLACE PROCEDURE analyze_table
  BEGIN
    analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows;
    analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows;
    analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows;
    END;
    /

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE ANALYZE_TABLE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3      PLS-00103: Encountered the symbol "BEGIN" when expecting one of
         the following:
         ( ; is with default authid as cluster compress order using
         compiled wrapped external deterministic parallel_enable
         pipelined result_cache accessible rewrite

What is reason for that?


texts:
I have created a joining table for many-to-many relationship.

The table only has 2 cols in it, ticketid and groupid

typical data would be

groupid    ticketid
20         56
20         87
20         96
24         13
24         87
25         5

My question is when creating the composite key should I have ticketid followed by groupid

CONSTRAINT [PK_ticketgroup] PRIMARY KEY CLUSTERED
    (
        [ticketid] ASC,
        [groupid] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

Or the other way, groupid followed by ticketid

CONSTRAINT [PK_ticketgroup] PRIMARY KEY CLUSTERED
        (
            [groupid] ASC,
                    [ticketid] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
        ) ON [PRIMARY]

Would searching the index be quicker in option 1 as the ticketid has more chance of being unique then the groupid and they would be at the start of the composite key? Or is this negligible?


texts:
I ran sp_Blitzfirst, and it threw back CXPACKET and CLR_SEMAPHORE waits as the top waits for my SQL server.

I know how to fix CXPACKET waits, but how do I go about fixing CLR_semaphore waits?


texts: I need to connect to remote DB over Hamachi, the tutorial states that I need to set tcp/ip to enable (which I did, in SQL Server Configuration Management tool) and add Hamachi IP to ipadresses, but which one? ip4,ip6...


texts:
I have a postgresql table with a jsonb column holding a lot of different data. This data needs to be moved into a DB2 database, but I need to know the max length of each individual "node" within the jsonb data to be sure I don't exceed the limitations of the individual DB2 column they get inserted into.

For example, {'city':'Mesa','county':'Maricopa','mayor':'John Smith'}

Is there a way to get the max length of each value in a jsonb column via SQL, resulting in something like city: 15, county: 22, mayor: 28 short of looping over every record?


texts: Shoud I uninstall mongodb 2.6 before upgrade to 3.0?

texts: I installed mongodb 2.6 in my machine. This week, I followed the instructions in the subsection of Install MongoDB on http://docs.mongodb.org/manual/tutorial/install-mongodb-on-ubuntu/. I installed MongoDB 3.0/. But I found that the date of some files in the mongodb package is in Feb. Why? Should I un-install mongodb 2.6 before upgrade to 3.0? my cluster is running all the time....


texts: Why will my ALTER and UPDATE not work in the same query in this specific instance?

texts:
I have a problem with a query where i am trying to ALTER a table by adding 3 columns to it, then updating each column with 1 specific piece of data.

I am aware there are several posts like this but every answer for them has not helped my specific situation e.g. "IF NOT EXISTS" or "COL_LENGTH" or adding "GO" in between the ALTER and UPDATE etc.

I still keep getting "Invalid Column Name" errors. Please see my code below:

CREATE PROCEDURE [dbo].[p_app_2_24_01]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
UPDATE [dbo].[app_Config] SET CFG_Value = '2.24.01'  WHERE CFG_KEY = 'VER'

ALTER TABLE [dbo].[app_table1] ADD [Bonus] int
ALTER TABLE [dbo].[app_table1] ADD [Allow] bit
ALTER TABLE [dbo].[app_table1] ADD [Discount] int

ALTER TABLE [dbo].[app_table2] ADD [Bonus] int
ALTER TABLE [dbo].[app_table2] ADD [Allow] bit
ALTER TABLE [dbo].[app_table2] ADD [Discount] int

Update [dbo].[app_table1]
                set [Bonus]  = 999,
                Allow = 1,
                Discount = 100

Update [dbo].[app_table2]
                set [Bonus]  = 999,
                Allow = 1,
                Discount = 100

Would anyone happen to have a great revelation to my increasingly frustrating problem? :)

Many thanks in advance,


texts: Error when running SQL Server 2014 64-bit Import/Export Wizard

texts:
I have previously asked this question on Stack Overflow, but I am not sure if it should be migrated to DBA Stack Exchange.

https://stackoverflow.com/questions/55186094/error-when-running-sql-server-2014-64-bit-import-export-wizard

I tried rebooting my computer without success. Additionally, a DBA was able to migrate the table from his computer using the same method. I suspect there may be a problem with my computer, but I am unaware of the troubleshooting steps I should take. The only thing I can think of is to uninstall and reinstall SSMS, which seems a bit extreme.

With that being said, I did perform a Windows Update yesterday. Additionally, I performed an update to VS2017 earlier this week and now the VSTA editor for SSIS script tasks/components will not open. I do know that when reinstalling MS products, I need to ask the security admin to disable the virus scanner as that blocks my ability to reinstall. So, I am wondering if the virus scanner interferes with Windows updates.


texts: Better ways than rownum limits on a union all for fallback-style queries?

texts:
I have three queries that generates some "reference" statistics and should be tried in order until one returns a result. These are currently coded as three separate calls to the DB, but I want to merge them as one.

I am considering a construct like this:

select * from (
 <query 1>
 union all
 <query 2>
 union all
 <query 3>
) where rownum = 1

The explain plan cost for this seems to be the sum of the three queries, but actually running the construct seems to only incur the cost of the query(/-ies) necessary to produce the row (as explained on AskTom).

Is this a good way to do this? Is there any better alternative?


texts: How to load geometry point tables in sql spatial

texts:
Need help loading point data in sql spatial.
Similar to this post but I really dont know how to get XY data to be accessible by sql
I want to do what was done in the initial part of this post (Identify points that plot inside the polygon):
https://gis.stackexchange.com/questions/166399/how-do-i-find-points-that-are-not-within-a-list-of-polygons-using-ms-sql
SELECT points.name, polygons.name
    FROM points CROSS JOIN
    polygons
    WHERE  (polygons.shape.STContains(points.shape) = 1)

How do you get the points loaded into your table and sql to recognize the points with out having to define them like the below?
insert into @points values
(geometry::Point(1,1,0)),
(geometry::Point(2,3,0)),
(geometry::Point(5,4,0)),
(geometry::Point(4,2,0))

I am having a hard time defining points without doing it manually like above
Ideally I could build a table like this by selecting the xy data I have in a table already


texts: How to transition from a statement based log to row based log for Galera replication?

texts:
I'm new to clustering and I'm trying to implement a Galera cluster. I'm transitioning from a standalone MySQL instance to a MariaDB Galera Cluster consisting of (at the moment) 2 nodes.

I have installed MariaDB-Galera-server.x86_64 10.0.13-1.el6 & MariaDB-client.x86_64 10.0.14-1.el6 on a CentOS 6 server.

I have also converted all the MyISAM tables to InnoDB.

My question is how do I make the switch? I was reading the (old?) codership notes on how to set up the my.cnf (https://web.archive.org/web/20131021065420/http://www.codership.com/wiki/doku.php?id=mysql_galera_configuration) and they say that these parameters are Required:

binlog_format=ROW
innodb_autoinc_lock_mode=2

My current log file (even though it's a standalone instance and not a cluster) is statement based and my database has a lot of data. How does making this change affect my current logs and current data?

Should I just make changes as per the my.cnf listed in the codership example file and play around until I get it right? Any other advice before I get started?


texts: Are three equalities on a join operation legal in relational algebra?

texts:
Let be the following database:

Film(Title,Director,Actor)
Produce(Producer,Title)

Express in relational algebra the following questions:

  Which actors produce at least a Film they directed?
  Which actors produce every film they directed?

For the first question, it leads me to an issue as far as I have three equality on the join operation and I don't know if it is legal.

If it were I would have tried the following:

ΠActor(Film)⋈(Producer=Actor=Director)ΠProducer(Produce)

But I'm not sure that's a true join operation in relational Algebra.

For the second one I tried something totally different:

ΠActor(Film)÷ΠDirector(Film)÷ΠProducer(Produce)

Responses to comments:

A film may have several actors.
For a film with several actors, there are multiple rows in the relation Film
A Director may, or may not, also be an Actor in the same Film


texts:
I have what I believe is a time series data set (please correct me if I'm wrong) that has a bunch of associated values.

An example would be modeling a car and tracking its various attributes during a trip. For example:

timestamp | speed | distance traveled | temperature | etc

What would be the best way to store this data so that a web application can efficiently query the fields to find max, mins, and plot each data set over time?

I started a naive approach of parsing the data dump and caching the results so that they would never have to be stored. After playing with it a bit, however, it appears this solution would not scale long term due to memory constraints and if the cache were to be cleared, then all data would need to be re-parsed and re-cached.

Also, assuming that data is tracked every second with the rare possibility of 10+ hour data sets, is it generally advised to truncate the data set by sampling every N seconds?


texts: Monitor disk usage with MongoDB Ops Manager

texts:
We use MongoDB Ops Manager 1.6.2.251.

I searched Activity -> Alert Settings. Is there a way to monitor disk usage?

To get an alert if this changes to 90%:

# df -h /data/
Filesystem                Size  Used Avail Use% Mounted on
/dev/mapper/vdatavg-data   99G  295M   99G   1% /data


texts: Is there any benefit to having one sql server login for multiple users?

texts:
I just entered a development project where the developers use the same SQL Server login to connect to a central database for their work. I have never come across this before, as each dev usually has their own login.

Are there any benefits to using this model?
Is there a limit to the number of simultaneous connections for the same SQL Server login?


texts: I need to do migrate or copy the oracle 10g database from sun solaris to another sun solaris server (oracle 11g). I was tried with SQL Developer tool. But It is not able copy all the objects in the particular schema. By the same time I should copy objects only, Don't need for data. Is there any procedure or scripts to copy the database or else any Oracle tool ?


texts: Is SQL Server 2012 predicate precedence overridden by all-at-once concept?

texts:
Is SQL Server 2012 predicate precedence overridden by the all-at-once concept?

I have been reading Querying Microsoft SQL Server 2012 by Itzik Ben-Gan, Dejan Sarka, and Ron Talmage. Chapter 3 lesson 1 Page 67 describes predicate precedence. Towards the bottom of the page it is written that the SQL Server all-at-once concept will override predicate precedence based on cost.

Below is a cut and paste from the book in PDF format:

  The reality, though, is different. SQL Server does internally support a short-circuit concept; however, due to the all-at-once concept in the language, it is not necessarily going to evaluate the expressions in left-to-right order. It could decide, based on cost-related reasons, to start with the second expression, and then if the second expression evaluates to true, to evaluate the first expression as well.

If this is correct then predicate precedence as described in this book and an article by Itzik Ben-Gan that he posted to SQLmag http://sqlmag.com/t-sql/short-circuit are irrelevant. The all-at-once concept will always choose the predicate precedence.

I believe that if the expressions are not evaluated according to precedence, left to right, then precedence would in fact be superseded by the all-at-once concept.


texts: Query against partitioned table accessing more partitions than necessary?

texts:
I first posted this on SO but got no replies, this is the reason for why I'm trying here now and I hope I'm not breaking any rules in doing so (fingers crossed).
I'm trying to understand how to use table partitioning and partition elimination correctly so that I can decide if I want to use it or not in a new database (sql server 17 express). I'm leaning towards not using it but that's a different story so this question is more that I wan't to know how it works now.
In the following script I create a partitioning function and scheme, a table using the scheme, insert some data and then query it in 3 different ways.

The first query is correctly only accessing the first partition (actual partition count = 1, actual partitions accessed = 1).

I expect the second query to only access the first partition as well given that I've created the partition function as range right on the value 20200101 but it is accessing partition 1 and 2 (actual partition count = 2, actual partitions accessed = 1..2).

I expect the third query to only access partition 2 but it is accessing partition 2 and 3 (actual partition count = 2, actual partitions accessed = 2..3).

I'm aware that I need to have exact type matching for partition elimination to occur but I think I have that here?
What am I missing?
create partition function partfun (datetime2(0))
as range right for values ('20200101', '20210101')

create partition scheme ps
as partition partfun all to ([primary])

create table t
(
    id int not null identity(1,1),
    logdate datetime2(0)
) on ps(logdate)
insert into t values ('20190101'),('20200102')

select * from t where logdate < cast('20191231' as datetime2(0))
select * from t where logdate < cast('20200101' as datetime2(0))
select * from t where logdate >= cast('20200101' as datetime2(0)) and t.logdate < cast('20210101' as datetime2(0))


texts: Update dynamic column names using keys from jsonb argument

texts:
I'd like to write a generic procedure for Postgres 12 that updates a table's columns depending on what data is supplied in a jsonb argument. It could be done in application logic instead of course, but I'm trying to push as much code down into the db layer as possible.

This is the kind of thing I naively hoped might work:

CREATE PROCEDURE record_event(
  foo_arg integer,
  name_arg text,
  data_arg jsonb,
  occurred_at_arg timestamptz
)
AS $$
DECLARE column_name text;
BEGIN
  -- This part is incidental:
  INSERT INTO foo_event(foo, name, data, occurred_at)
    VALUES(foo_arg, name_arg, data_arg, occurred_at_arg);

  -- This is the part I'm struggling with:
  FOR column_name IN (SELECT * FROM jsonb_object_keys(data_arg)) LOOP
    PREPARE update_query(text, text, integer) AS
      UPDATE foo SET $1 = $2 WHERE id = $3;
    EXECUTE update_query(column_name, data_arg->>column_name, foo_arg);
  END LOOP;
END
$$ LANGUAGE plpgsql;

But pg doesn't like $1 in that position:

psql:src/db/migrations/foo/up.sql:43: ERROR:  syntax error at or near "$1"
LINE 15:       UPDATE foo SET $1 = $2 WHERE id = $3;

I also noticed there is a json_populate_record() function which seems like it should be helpful here, but I don't really understand how to apply it to my situation from the documentation.

Does what I'm trying to do make sense and is something like it possible?


texts:
I cannot get this statement running on MySQL server:

DECLARE @tabl varchar(40)
SET @tabl = :uId;
SET @location = CONCAT('data_',@tabl);
SET @b = 'INSERT INTO' + @location + '(Id, uId , name , address, phone)
VALUES (:Id, :uId, :name, :address)';
Prepare stmt FROM @b;
Execute stmt;

I get this error:

  No value specified for parameter 1 - SQLException

Bindings will be done in another function.

How can I fix it?


texts: MySQL Design - What is best primary key for our needs?

texts:
The site I am designing for is in the final stages of development. It is a forum where, obviously, users ask and answer questions.

The owners want id values to be of no value to competitors. These would be the id fields such as question_id, user_id, answer_id, and a couple of others. In other words, they dont want the value of ids in the site to be able to be used by a competitor to determine how many users, or questions, or answers exist. This would also include a private message section since you could sign up, message me, see message_id=3189 in the url or in the html in case of post, and start to piece together how active the site is.

Ok. So, the expectation is for ids to get into the millions (whether it will or not, we are planning for 5 million for each id)

Currently, we are using auto-increment for each table (type is INT unsigned)(ex.questions table has question_id as auto, answers table has answer_id as autoincrement, etc.) and of course there are many joins, and all works well. Even when I insert millions of test records. But, to be honest, I am skeptical of this testing--just adding records--since I am unsure of if there is an impact with multiple users.

We are considering three options to convert the ids so that we make them not usefull to the outside world. And I would love some thoughts from you guys.

1) Add a 4char hash column next to each id column, and index it. Show this hash in the urls. Then whenever a something needs to be processed, lookup the hashes, gete the autoid, and use that in joins and inserts. The con here is the added code and added db calls--sometimes will need two or three hashes to be resolved. Not sure of performance with the lookups.

2) Add the 4char hash column IN PLACE OF the autoid column. And in joining tables make, obviously, the same change. So, the 4char is the primary key. The pro here is that there is very little change to code, just change my db column types. But not sure of performance with the joins or lookups.

3) Generate random numbers for the id field instead of autoincrementing. This gives billions of random possibiilities which would be of no value to others. I would probably opt for only using numbers above 1 billion so all ids are the same length. The pro here is that it is the easiest, but not sure of lookups and joins with fragmented ids.

Well, what do you guys think? Do any of these make more sense than the others? Is this a case of "I'd never notice a difference anyway, even with millions of records?"


texts: Best way to evenly distribute data over partitioned tables

texts:
I am working with a database with roughly around 500MM records. We currently use a static partitioning method that partitions the data by an int identifier such at < 150MM goes on partition 1, > 150 && < 350 goes on 2 etc.

What dynamic partitioning schemes are available to better evenly distribute the data over the available partitions? Is there a way to allow for additional partitions to a dynamic scheme with minimal maintenance overhead?


texts:
We are running a financial application using MySQL as database.

For  performance reasons  we are using memory tables for a few of our live tables where the data is populated ever few milli seconds.

To ensure data consistency in all scenarios (planned and unplanned outages)  we need to ensure that the data in the memory tables are always persisted for that  we are planning to implement shutdown hooks, wherein n case of any shutdown the data would be written in normal tables  and restored during start-up.

I am unfortunately not able to find a way to do it.

Any suggestions would be appreciated.

We are using MySQL ver 5.7


texts:
i got two tables a and b
table a contains some names like cobol, php, cmd and a refnumber

name ref
Cobol 1
php   2
cmd   3
...

table b contains multiple projekts where i used one of table a
like year, mm, and a reference to table a
that means f.e

year mm ref
1982 8  1
1982 4  2
1983 5  3
1983 1  1
1982 6  3
1994 12 1
1995 12 2
1996 6  1
1996 6  2

now i want to know how many month in total (from b) i used one of table a to get a list like

Cobol 27
php   20
cmd   11

Note :sums might be not corret in this example.
An answer provided below made me realize I need to clarify the situation further.
Table a has the ref in a so far not mentioned table c with the content:

id number uses
1   1      1
2   1      2
3   2      1
4   2      3
5   2      4

So table a (pgojekt) contains each project I've worked on and each entry there has a year and number of month in it. It references an other table for example reflanguages.  In Languages there are the possible languages while reflanguages contains the reference to the language.
I hope this is understandable.
For example in 1982 i did 2 projects (1 and 2)  each with 6 month duration. While Project 1 uses languages 1 and 2, project 2 uses language 3 and 4. In 1983, I did another 12 month project that uses languages 1 and 2 and in 1984 one with 3 month 1 and 2 and one with 4 and 5 and 6.
Now the problem seems that a select name, sum(mm) as you suggested shows all but not the sum for Cobol in all those years, followed by php in all those years, followed by....
if this gets to complicated i can add a db dump.
what i want is a list like written above.
I got it working when specifying the name in a where clause, however I want it for all names in table a as shown above
How can this be achieved with MySQL?


texts: Oracle: Output not shown even if SERVEROUTPUT variable set to ON

texts:
I was wondering why the output is not being shown even if I set the SERVEROUPUT variable to ON.

I have the following annonymous block code:

DECLARE
    v_rec_count number:=0;
BEGIN
    for rec in (SELECT T_A.ROWID, T_A.OPEN_COUNT, T_B.OPEN_COUNT as NEW_OPEN_COUNT
            FROM T_A
            INNER JOIN T_B ON
                T_A.KEYA = T_B.KEYA
            AND T_A.KEYB = T_B.KEYB
            AND T_A.OPEN_COUNT <> T_B.OPEN_COUNT)
    loop
        v_rec_count := v_rec_count + 1;

        update T_A t1
        set t1.OPEN_COUNT = rec.NEW_OPEN_COUNT,
            t1.UPD_USER_ID = 'batch',
            t1.UPD_DATE = SYSDATE
        where t1.rowid = rec.rowid;
    end loop;

    dbms_output.put_line ('T_A UPDATE_TOTAL: ' || v_rec_count);
END;
/

When I execute the following code, I would like to see the output of dbms_output.put_line to the sql prompt and eventually to the log file when I run the sql file via sqlplus. The output that the block does is just the "PL/SQL Procedure has successfully run".

I already set SET SERVEROUPUT ON before the block but it does not work. Any ideas?


texts: Multiple types of parts with multiple types of information (unspecified amount)

texts:
I'm having trouble designing a database for a project I was working on recently. It's to do with a garage management system.

So I've realised I need to store multiple types of parts (Tyres, engines, wheel bases, this list needs to grow), with multiple types of details, some that each part shares (name, part number, condition, quantity) and some that are unique to each part (engines have weight and power, tyres have size and alloy type) and that also needs to grow too. This can't be limited to just 2 kinds of unique information, it needs to be infinite, so that more can be added at a later date if required

How can I design a database that is capable of storing all of this information without having tables for each specific part, so a table for tyres, a table for engines etc? I want to keep it so the least amount of tables as possible is used, so once designed, no new tables are added as new data is added.

Any help is appreciated.


texts: Does H2 Database support pessimistic locking?

texts:
Does the H2 Database support pessimistic locking?

If so, how does its use interact with H2’s MVCC implementation?

I'm not looking for advice on whether or not to use pessimistic locking. I have a specific need to emulate the behavior of another database that defaults to row-level pessimistic locking. I need to be able to test if a row is locked, and if not locked, lock it out from writes by any other database connection.


texts: MySQL query r.e covering index with Primary Key

texts:
I have the following table (there are more columns in the real one).

CREATE TABLE `test`.`j_data` (
`j_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`j_name` VARCHAR(80) NULL DEFAULT NULL
PRIMARY KEY (`j_id`));

We regularly run queries where the main column used is the PK, e.g.

SELECT * FROM j_data WHERE j_id = 123;

But we are now looking to run a lot of queries like:

SELECT j_id FROM j_data WHERE j_name = xxx;

Would this benefit from a covering index on j_name and j_id.

ADD INDEX `j_name_id` (`j_name` ASC, `j_id` ASC);

Or would an index on j_name only suffice? It's quite a large table so I don't want to spend time adding the index if it is unlikely to help.


texts: Moving MySQL files from cloned disk to new server

texts: I am currently trying to move MySQL files from /var/lib/mysql to an active server (Ubuntu). Unfortunately, my old server broke and I did not have time run mysqldump. When I moved the whole directory with the different db directories, MySQL refuses to restart. The version I am copying from used to be 5.5.26 and the one installed on the server is 5.5.58, could this be culprit? I am running Ubuntu, and the same was running on the the old server. Many thanks for your help.


texts: Cap on number of rows matching a condition in Postgres

texts:
Say I have a db schema like this

user_id text
photo_id text
date_created timestamp

and I want to enforce a constraint that says "a user can only have 30 photos". A naive way to implement this would be to do

SELECT COUNT(*) FROM user_photos WHERE user_id='usr_123'

compare the result to 30, if it's lower, then do the insert. Naive because if you can trigger multiple requests in a short amount of time, you can win a race and end up writing more than 30 records.

Is there a way to enforce this constraint in the database? eg create some kind of index that says "only 30 rows can be inserted", or something? Is there a way to have a count column that increments separately for each userId, and you can limit its range to 0-29?

If you used a transaction, would that lock the rows you read?


texts:
I have a database with Turkish names and surnames. Here is some information about my table:

CREATE TABLE `sorted_tc` (
  `TCKIMLIKNO` double NOT NULL,
  `ADI` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `SOYADI` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ANAADI` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `BABAADI` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `DOGUMYERI` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `DOGUMTARIHI` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `NUFUSILI` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `NUFUSILCESI` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ADRESILI` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ADRESILCESI` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`TCKIMLIKNO`),
  KEY `anababa` (`BABAADI`,`ANAADI`),
  KEY `adsoyad` (`SOYADI`,`ADI`),
  KEY `adresili` (`ADRESILI`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

select hex(ADI), hex(SOYADI) from sorted_tc WHERE TCKIMLIKNO=12161429422;

5941C59E4152 41524142414349

5941C59E4152 is hex value for utf8 encoded 'YAŞAR'.

When I search for 'yasar', I want to get both 'yasar' and 'yaşar'. Similiarly, when I search for 'yaşar', I should get both 'yasar' and 'yaşar'.

However, when I search for 'yaşar', I am not getting any rows. Here is hex dump of relevant query from mysql query log:

0004-15a0:  51 75 65 72-79 09 73 65-6c 65 63 74-20 2a 20 66  Query.se lect.*.f
0004-15b0:  72 6f 6d 20-73 6f 72 74-65 64 5f 74-63 20 57 48  rom.sort ed_tc.WH
0004-15c0:  45 52 45 20-41 44 49 3d-22 79 61 c5-9f 61 72 22  ERE.ADI= "ya..ar"
0004-15d0:  20 41 4e 44-20 53 4f 59-41 44 49 3d-22 61 72 61  .AND.SOY ADI="ara
0004-15e0:  62 61 63 c4-b1 22 0a 31-35 31 32 32-36 20 31 34  bac..".1 51226.14

My table orignally used collation utf-8 general ci, and I could search for 'yaşar' with success.

I am using Mariadb 5.3


texts: MariaDB LOAD DATA INFO can't import single column file to single column table?

texts:
I am trying to import the 10,000 common password list into a table to ban those passwords from being used in a given system.

The file is a single column TXT file. Trying to LOAD DATA INFILE that into a table with a single column gives me a primary key error. So, I thought, I'll set an id column with autoincrement, and then pass null for each row to trigger the keys to be indexed. That doesn't work either (or I have a syntax problem) because that gives me 10,000 rows with numbers and null password fields.

Can this be done, or should I just write a python script to add an index to each text line, and then import it that way?

Here's what I am trying to do:

LOAD DATA INFILE '/tmp/passwords.txt' INTO TABLE `common_passwords` (password,id) SET id=@id+1,password=@password;

And here's the table:

CREATE TABLE `common_passwords` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `password` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16384 DEFAULT CHARSET=latin1;

It seems like we should be able to do this rather easily in SQL...


texts: Dockerized Sysbench could not connect to Mysql

texts:
I'm trying to follow this tutorial.

It does not give Mysql version requirements. So I'd tried to use Mysql 8.0.18 Community Server to connect to using those commands.

I've created what have been required inside of Mysql 8:

mysql> CREATE SCHEMA sbtest;
mysql> CREATE USER sbtest@'%' IDENTIFIED BY 'MyPassword+1';
mysql> GRANT ALL PRIVILEGES ON sbtest.* to sbtest@'%';

Than created script file with the first command:

[root@host ~]# cat sbpreparing
docker run \
--rm=true \
--name=sb-prepare \
severalnines/sysbench \
sysbench \
--db-driver=mysql \
--oltp-table-size=100000 \
--oltp-tables-count=24 \
--threads=1 \
--mysql-host=192.168.1.200 \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=MyPassword+1 \
/usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua \
run

and launched it:

[root@host ~]# ./sbpreparing
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time

Initializing worker threads...

Threads started!

thread prepare0
Creating table 'sbtest1'...
FATAL: unable to connect to MySQL server on host '192.168.1.200', port 3306, aborting...
FATAL: error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/x86_64-linux-gnu/mariadb18/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
FATAL: `thread_run' function failed: /usr/share/sysbench/tests/include/oltp_legacy/common.lua:66: Failed to connect to the database
Error in my_thread_global_end(): 1 threads didn't exit

I could connect to the MySQL server from the same machine using command:

mysql -h 192.168.1.200 -u sbtest -pMyPassword+1

But sysbench couldn't. How could I fix it? Thank you!


texts:
I am trying to create a view that provides all quarter starts from a date dimension. Here it is:

CREATE OR REPLACE VIEW quarter_starts AS
SELECT min(day_number_in_epoch) AS day_num FROM date_dimension dd
GROUP BY dd.calendar_year_quarter;

And postgres gives the error:

ERROR:  cannot change name of view column "min" to "day_num"


texts: From freelancer to team of two : best way to share/sync db?

texts:
I am a webdev. I have just hired a dev. Before that I was alone. Thus, I have to change a bit my organization.

I am looking for the best way to develop on the same database (MySQL). I am considering different options:

Get a small server in the office and set an access to this database (actually, this is what we are doing. Performance are poor)
Use a big platform like Amazon RDS or Google Cloud Platform
Use a small VPS like Linode or Digital Ocean
Keep a db on  each dev machine and use a kind of syncing/replication

What would be the best way for you?


texts: Common term for a table (heap) without any supporting structures

texts:
Maybe this is off topic, and please VTC if so, but I couldn't think of a better place to ask.

Is there a common term for a table without any other structures (indexes, statistics, etc) - something like a "naked" table? I use the term heap but understand it just to apply to nonclustered tables, rather than to tables without other support - a heap can still have other indexes associated with it.

I had copied some tables into a new database but didn't bring over the partition scheme, indexes, triggers, or anything else - it made me wonder if there's a term for just the data itself (and minimal schema needed to hold it).


texts: Getting multiplication of column values on a joined table based on condition

texts:
The title was best I could find to explain my question, I don't think it helps though. Anyways, I have two tables:

 CREATE TABLE YEARLY_RATES
 (
   RATE_YEAR NUMBER(4,0),
   RATE NUMBER(6,4)
 );

 CREATE TABLE PERSON_ACCOUNT
(
   PAID_YEAR NUMBER(4,0),
   PAID_AMOUNT(10,2)
);

I need to select PERSON_ACCOUNT.PAID_YEAR, PERSON_ACCOUNT.PAID_AMOUNT,PERSON_ACCOUNT.PAID_AMOUNT * multiplication of the YEARLY_RATES.RATE WHERE YEARLY_RATES.RATE_YEAR >= PERSON_ACCOUNT.PAID_YEAR. So for instance if I have the following data in PERSON_ACCOUNT

PAID_YEAR|PAID_AMOUNT
---------------------
   2006    2150.69
   2007    3290.40
   2008    3000.35

and YEARLY_RATES tables

RATE_YEAR|RATE
---------------------
   2006    1.067
   2007    1.208
   2008    1.057

then I should be able to have the following output:

PAID_YEAR|PAID_AMOUNT|INDEXED_AMOUNT
----------------------------------------------------
   2006    2150.69      2150.69*1.067*1.208*1.057
   2007    3290.40      3290.40*1.208*1.057
   2008    3000.35      3000.35*1.057


texts: What does "Heap Block" in "Bitmap Heap Scan" mean?

texts:
I have this query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM test
WHERE
    timestamp_range @> '2015-01-22 23:00:00'::timestamp
    AND data_int_array @> '{49, 61}'::integer[];

Which outputs:

Bitmap Heap Scan on test  (cost=16.74..20.75 rows=1 width=113) (actual time=0.364..0.367 rows=2 loops=1)
  Recheck Cond: ((timestamp_range @> '2015-01-22 23:00:00'::timestamp without time zone) AND (data_int_array @> '{49,61}'::integer[]))
  Heap Blocks: exact=1
  Buffers: shared hit=8
  ->  BitmapAnd  (cost=16.74..16.74 rows=1 width=0) (actual time=0.351..0.351 rows=0 loops=1)
        Buffers: shared hit=7
        ->  Bitmap Index Scan on ix_test_interval  (cost=0.00..4.40 rows=17 width=0) (actual time=0.130..0.130 rows=12 loops=1)
              Index Cond: (timestamp_range @> '2015-01-22 23:00:00'::timestamp without time zone)
              Buffers: shared hit=2
        ->  Bitmap Index Scan on ix_test_data_int_array_data_json  (cost=0.00..12.08 rows=11 width=0) (actual time=0.211..0.211 rows=6 loops=1)
              Index Cond: (data_int_array @> '{49,61}'::integer[])
              Buffers: shared hit=5
Planning time: 0.396 ms
Execution time: 0.484 ms

I have read the documentation "Using Explain", but I didn't find any reference to Heap Block.

Please, could you tell me what Heap Bock means and how it relates to Buffers in a Bitmap Heap Scan?

I'm running: "PostgreSQL 9.4.5, compiled by Visual C++ build 1800, 64-bit"


texts: Convert OLTP database to OLAP Datawarehouse

texts:
(http://imgur.com/p8Gy67E).  Basically I need help to convert that OLTP Model to Data Warehouse in order to be used for further OLAP Cubes. I understand well the difference between the Star Schema and the Snowflake Schema in design , but can't figure out what tables to keep in and what tables to exclude . *Note: This OLTP Model is devoted in the Retail field and it must answer questions like the following:

How many customers are spending more over time? Less over time? Describe these customers.

Of those customers who are spending more over time, which categories are growing at a faster rate?
Of those customers who are spending less over time, with which categories are they becoming less engaged?
Which demographic factors (e.g. household size, presence of children, income) appear to affect customer spend? Engagement with certain categories?
Is there evidence to suggest that direct marketing improves overall engagement?

So , based on the above questions as I read on various data-warehouse sites and blogs , I must keep some tables that will be useful and merge other tables as possible . If anyone can help I can share the dataset description for each table and the function of each column , I would be thankful for advices and participation .


texts: dynamically get last x months and output as a format

texts:
Using today's

date, I need to be able to go back x months and output in the format of YYYY_MM

At the moment, I am using:

select
right('0000' + cast(datepart(year, getdate()) as varchar(4)), 4)
+ '_'
+ right('00' + cast(datepart(month, DATEADD(month, -3, GETDATE())) as varchar(2)), 2)

Which will give me a output of:

  2015_08

The issue I am having is that if I were to go back 12 months using:

select
right('0000' + cast(datepart(year, getdate()) as varchar(4)), 4)
+ '_'
+ right('00' + cast(datepart(month, DATEADD(month, -12, GETDATE())) as varchar(2)), 2)

The output would still be:

  2015_11 (notice the year is still the same)

I am using SQL 2008

EDIT: Solution

DECLARE @table12 AS varchar(50)
SET @table12 = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -12, GETDATE()),102), '.', '_')


texts: Multi Server Query generating Login failures

texts:
My problem is that when using multi server queries using SSMS from SQL Server 2008 R2 against SQL Server 2008 R2, 2005 and 2000 I occasionally get error messages such as:

SQL Server 2008R2

  Login failed for user 'DomainName\ADName'. Reason: Failed to open the
  explicitly specified database. [CLIENT: 21.12.0.0]
  Error: 18456, Severity: 14, State: 38.

SQL Server 2005

  2013-10-10 15:55:34.28 logon     Login failed for user
  'DomainName\ADName'.
  Error: 18456, Severity: 14, State: 16.

SQL Server 2000

  Login failed for user 'DomainName\ADName'. [CLIENT: 21.12.0.0]

Now my query works fine but given what my query does is search for errors in logs it is not ideal that it should also generate such errors.

My methodology may be flawed, what I have done is create a group under Local Server Groups and registered a few dozen servers of various versions. Then I right click on this group, select New Query to open a new batch window. Lastly I open my saved script and run it. In rough outline this is what it does

use master
go
determine version of SQL as like '%8.0%' or not
create temporary table based on version and pump results of xp_readerrorlog 0-6 into it
translate temporary table to another and insert to results temporary table
select on results table
drop temporary tables
go

I am a sysadmin on all machines. Two things I tried was the 'use master' statement at the top of the script and changing the registered servers to have a "Connect to database" of master. Neither of these has eliminated the problem. Does anyone have any further suggestions?


texts:
I am looking for advice on managing backups using Veeam and optimizing storage space for backups.

We have about 2TB across 20 DB

My current backups are

Image Level  (Veeam)

Replication to DR every 1hr (excluding native backup volume)
Backup to DR nightly (excluding native backup volume) (different disk array to replications), 45D, 4W, 12M, 7Y  retention
Push to offsite repository nightly 7D

SQL Native

Nightly backup to local server (different re disk array), 1D Retention
15 min transaction log backups to same folder as nightly
10min FTP push native SQL backup to DR with 7D retention

I am pretty sure there is some redundant duplication here, I have been looking into Veeam and it  looks like it can handle the transaction log backups as well. If this is the case, are native backups still required? I would feel uneasy not having them but that could just be my lack of knowledge in that side of Veeam.  Or is there something else that stands out that i should change?

Veeam documentation https://helpcenter.veeam.com/evaluation/backup/vsphere/en/sql_backup_and_restore.html

EDIT

Our RPO is 30 minutes and our RTO is 2 hours


texts: Foreign key violations detected upon UPDATE on another column

texts:
I have a table country that is referenced by many tables like so:

db=# \d client
                          Table "public.client"
      Column              |            Type             | Modifiers
--------------------------+-----------------------------+-----------
 id                       | character varying(32)       | not null
 name                     | character varying(64)       |
 country_id               | character varying(32)       | not null
 insert_date              | timestamp without time zone |
 update_date              | timestamp without time zone |
Foreign-key constraints:
   "client_country_fk" FOREIGN KEY (country_id) REFERENCES country(id)
Triggers:
    update_client_insert_date BEFORE INSERT ON client FOR EACH ROW EXECUTE PROCEDURE update_insert_column()
    update_client_update_date BEFORE UPDATE ON client FOR EACH ROW EXECUTE PROCEDURE update_update_column()

The referenced table:

db=# \d country
                              Table "public.country"
       Column             |              Type           |  Modifiers
--------------------------+-----------------------------+--------------
 id                       | character varying(32)       | not null
 name                     | character varying(256)      |
 insert_date              | timestamp without time zone |
 update_date              | timestamp without time zone |
 Indexes:
"country_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "client" CONSTRAINT "client_country_fk" FOREIGN KEY (country_id) REFERENCES country(id)
Triggers:
    update_client_insert_date BEFORE INSERT ON client FOR EACH ROW EXECUTE PROCEDURE update_insert_column()
    update_client_update_date BEFORE UPDATE ON client FOR EACH ROW EXECUTE PROCEDURE update_update_column()

And the trigger :

db=# SELECT proname, prosrc FROM pg_proc WHERE proname='update_update_column';
        proname         |                                  prosrc
------------------------+--------------------------------------------------------------------------
 update_update_column |     BEGIN       NEW.update_date = now() ;       RETURN NEW ;    END ;

I discovered by chance that these foreign keys were violated when running an UPDATE command on one of the tables:

UPDATE client SET name = 'foo';

  ERROR:  insert or update on table "client" violates foreign key constraint "client_country_fk"
  DETAIL:  Key (country_id)=(someRandomId) is not present in table "country".

I deleted all rows referencing someRandomId and I was able to run the UPDATE command.

How can my database be in an inconsistent state?

Can I run all database constraints manually to make sure it is now consistent?

I suspect the issue has to do something with the DEFERRABLE setting. But according to the doc, it's not the default.

According to the doc, there's a SET CONSTRAINTS; setting but it only applies to a specific transaction.


texts: I have a MySQL 5.6 Community Edition server running on a machine with Fedora 21 which was installed via the YUM repository. I want to enable MySQL replication on the Server, but since I am limited to one machine, I have to install two MySQL instances on the same machine. I need a complete step-by-step procedure of installing two or more MySQL server instances side by side, one to act as the Master, the other as the slave. I have tried searching on the Internet for a comprehensive procedure, but I haven't found any yet. The MySQL Official documentation didn't really help as it is OS independent, yet I want a Fedora/Linux specific installation and configuration. Other links I have tried include (not in any specific order) this and this, but to no avail. Anyone with a complete reference/procedure on how to do this?


texts: Data Warehouse indexing approach - to verify

texts:
Could you take a look at my approach to the Data Warehouse indexing (SQL Server 2014 SP1 Ent) below and tell me is it correct or should I change it ?
(our Data Warehouse most of the time is used for SSAS Cube and sometimes for direct reports)

My indexing approach:

All primary key's (in a Dimension tables) have a clustered Index
For bigger Dimension tables (50k+ of rows) I have a dedicated non-clustered index per frequently use column
All Fact tables have a PK clustered index implemented (on single non FK column like FactTableKey) – but I red this shouldn’t be placed there
All foreign key’s (in a Fact tables) have a dedicated non-clustered index (per one FK column in it)
In addition I’m considering the implementation of a CLUSTERED COLUMNSTORE INDEX on a Fact tables instead of many non-clustered indexes for every FK’s -  but in this case I’ve red that this is a not recommended solution

In addition – on my test lab with Visual Studio SSDT – I tried to put a CCI index on a Fact table which belongs to a FILEGROUP but I get an error with information that I cannot use CCI on a table which belongs to a FILEGROUP. What’s strange I can create such CCI from SSMS directly. Visual Stusio issue or SSMS bug ?


texts: Low cardinality fields: integer or string

texts:
A Ticket has the statuses:

new
in_progress
on_hold
closed

I could make ticket.status a string (on_hold) or an unique int(2). It is indexed.

Int pros: smallest size in index

Int con: low clarity for BI and evolving schema (3 compared to closed)

String pro: clear data navigation

String con: takes more space to index, gives less performance for same RAM

I imagine a string index wont take too much space over an int index if its low cardinality. Is it premature optimization to choose integer if the field has low cardinality and is not part of a compound index?

I use Postgres with SQLAlchemy, Python ORM.


texts: Is .NET Framework 3.5 still needed with a SQL Server 2017 installation to utilize Database Mail?

texts:
I'm guessing .NET Framework 3.5 is still needed to use Database Mail with SQL Server 2017? As I had to include this on my SQL Server 2016 installs on Server 2016 not that long ago.

I was unable to find a definitive answer via the web, surprisingly, maybe it goes unsaid.


texts: innodb cache duplication due to os page cache and buffer pool

texts: We have set our innodb buffer pool 80% of total memory. Mysql page allocation gets stalled sometimes when page cache is occupying memory. Now since mysql is caching table rows and indexes and follows a lifecycle to flush them, doesn't OS page cache have the same data in terms of files? Doesn't it cause cache duplicity and increase in memory usage? If data is duplicated can we assume flushing OS page cache we would have no impact on performance of mysql as innodb buffer pool has hot/active data?


texts: Replicate encrypted data from Sybase ASE 15.5 to Sybase ASE 15.5

texts:
Recently I'm studying on how the replication of encrypted data works from Sybase ASE to Sybase ASE, and I have few queries on below:-

What will be the datatype for columns that contain encrypted data in repdef?
Any grant statements required to grant to replication id?
Any extra configuration needs to be done on Repserver? RepAgent? Target Connection? (My RepAgent and Target Connection are all ready)
Any possibilities to replicate this encrypted data from Sybase ASE to Oracle side?

Appreciate if you could solve my doubt. Thanks.


texts: Amazon RDS t2.small as a production server

texts:
I currently have a m3.medium as production PostgreSQL server but I want to move it to RDS with the Multi-AZ feature.

Since the CPU usage of our current server is never higher than 5% and the RAM usage is 500MB I was considering using a cheaper instance like the t2.small on RDS.
On a few online forums and on Amazon help pages they don't recommend the small instances for a production database.
Is this just marketing or are there some real technical issues that may come up?

The only difference I found between the t2 and m3 instance types is the burstable CPU but since our DB server doesn't use any CPU I wonder what the other dangers are.


texts: sqlite3 command does not save database after creation?

texts:
I'm migrating from MySQL. I'm having trouble scripting a SQLite database creation. Similar code worked well for MySQL.

It appears the database and tables are created, but then sqlite3 command does not save changes to disk.

$ sqlite3 < testdb.sql
$ ls -Al testdb.db
-rw-r--r-- 1 jwalton jwalton 0 Mar 25 04:02 testdb.db

And:

$ cat testdb.sql

ATTACH DATABASE 'testdb.db' AS 'testdb';
PRAGMA foreign_keys = ON;

-- #####################################################

CREATE TABLE IF NOT EXISTS name_number
(
        nn_id INTEGER PRIMARY KEY AUTOINCREMENT,
        nn_name VARCHAR,
        nn_number VARCHAR
);

CREATE INDEX IF NOT EXISTS number_index ON name_number(nn_number);

-- #####################################################

CREATE TABLE IF NOT EXISTS whitelist
(
        list_id INTEGER PRIMARY KEY AUTOINCREMENT,
        nn_id INT NOT NULL,
        list_date DATETIME,
        fk1_id INTEGER,
        FOREIGN KEY(fk1_id) REFERENCES name_number(nn_id)
);

-- #####################################################

CREATE TABLE IF NOT EXISTS blacklist
(
        list_id INTEGER PRIMARY KEY AUTOINCREMENT,
        nn_id INT NOT NULL,
        list_date DATETIME,
        fk2_id INTEGER,
        FOREIGN KEY(fk2_id) REFERENCES name_number(nn_id)
);

-- #####################################################

INSERT INTO name_number (nn_name, nn_number) VALUES ("Test User", "4105551212");
INSERT INTO whitelist (nn_id, list_date) VALUES (last_insert_rowid(), datetime('now'));

INSERT INTO name_number (nn_name, nn_number) VALUES ("Test User", "8005551212");
INSERT INTO blacklist (nn_id, list_date) VALUES (last_insert_rowid(), datetime('now'));

How do I tell sqlite3 command to save the database after creation?


texts: Index REBUILD only works with SORT_IN_TEMPDB = OFF?

texts:
I'm trying to troubleshoot why my index rebuild doesn't work with WITH (SORT_IN_TEMPDB = ON, ONLINE = ON) in Microsoft SQL Server 2016 (SP1-CU3) (KB4019916) - 13.0.4435.0 (X64). I get the following error:

Cannot continue the execution because the session is in the kill state.

I ran DBCC CHECKDB which returned no errors. I have rerun with SORT_IN_TEMPDB = OFF and it seems to work but takes a very long time. I have 86GB free disk space in TEMPDB drive, so it can't be a disk space issues.

Does anyone have any ideas?


texts:
I created a superuser portal with the following command

create role portal with superuser password 'portal'

When I tried to login into postgres with portal user I am getting the following error in rails.

FATAL:  role "portal" is not permitted to log in

I can't follow what is going on.

of course I can alter user with login command to enable portal user to login. I would like to understand why super user can't login.


texts: Separate data from 1 column into multiple columns

texts:
Below is sample data and expected results:

Table1: salesprice

ItemNo     Variant     SalesCode     UnitPrice
 A           1            SRP           100
 A           2            SRP           200
 A           1            WSP           50
 A           2            WSP           100
 B           1            SRP           300
 B           2            SRP           400
 B           1            WSP           150
 B           2            WSP           200

Query Results:

ItemNo     Variant     SalesCode(SRP)  UnitPrice   SalesCode(WSP)   UnitPrice
  A          1              SRP           100          WSP             50
  A          2              SRP           200          WSP             100
  B          1              SRP           300          WSP             150
  B          2              SRP           400          WSP             200


texts: Using SSL produces error "protocol version mismatch"

texts: Using MySQL Server (5.5.46-0) on Ubuntu 14.04.2 with protocol version 10.  Configuring SSL and defining SSL cert files locations in My.cnf file.  Have defined absolute paths for CA-cert.pem, server-cert.pem and server-key.pem files.  When attempt to connect with MySQL workbench and user creds that require SSL, get a connection error stating "protocol version mismatch".  If connect with user creds that do not require SSL, in some cases same connection error, but some cases (different workstation) good connection ...?  Can anyone provide a clear explanation of the above "protocol version mismatch" error and how might resolve this issue?  At a complete loss here. Thanks ...


texts: Could a busy database affect others on the same server?

texts:
I have a very busy database with lots of reads and writes. I want to create another database on the same server, but I am wondering if that busy database will slow down the performance of the second one.

I'm using SQL Server.

In general I want to understand if a blocking query will be per database or per server instance.


texts: I have SQL Server running on a virtual machine (VM). Weekly VM backup is done. My question is when we backup the VM, does it locks down the SQL Server or the databases residing in that SQL Server? I understand VM backup is at the OS level but still want to confirm since I am in middle of investigating an issue and I am not a database expert.


texts:
Our software product currently ships on Windows 7 with Postgres 8.3 as its database.  On a busy site, there may be 24 automated systems generating 100 rows (x100 columns) per minute, with 3-10 human clients viewing subsets of around 1000 rows – all retrieved at once, with incremental updates querying pk + timestamp every minute or so and retrieving new rows that are pertinent.  There are a few auxiliary tables, but this table has the primary activity.

As a first step to a limited multi-master system (to help with geographically separated teams), we implemented an upgrade to 9.3.  Performance wasn't the first priority, so it wasn't really profiled.  As release time has come, management has decided to drop 9.3 for now citing fear of possible performance degradation and lack of testing resources.  I was sure the performance issue was preposterous so I did some PgBench testing.

Using 9.3's pgbench, I alternated between connecting to the local 8.3 and 9.3 installations (diff't port numbers).  I've captured my results in this google drive spreadsheet, but the summary is that generally 8.3 beat 9.3.  9.3 only won in raw insert performance.

We have some customization of our postgresql.conf files which I generally kept from 8.3 to 9.3, I'll list the non-default settings

max_connections = 1000
shared_buffers = 320MB
temp_buffers = 80MB
max_prepared_transactions = 50    #8.3 only, 9.3 left at 0 (not sure why)
max_fsm_pages = 204800            #8.3 only, 9.3 doesn't have setting
autovacuum_max_workers = 30

So, is this just the price of progress, or is there something I should be doing in 9.3 to make it excel?


texts:
This below Image is for My messaging database :

This is database for server side of asp site and client side was wrote by android app.
So my Question is how to set ruls for my relationship in this database?
Another Question is what are the problems of this design?


texts:
I am a highschool student who is interested in developing apps and learning more about databases and servers, so please bear with me if this is the wrong place to ask or if it's a obvious answer.

I have developed a few small database iPhone apps with Parse.com, so what somewhat of an idea of how a database works. But of course, I'm sure that is nothing compared to a real database.

My question is: How does an app like Snapchat go about storing its data (do you think)?

Does it just have one huge table of all the images? Meaning when someone uploads and app, does it create an object/row in a large table with the picture, who sent it, recipients, date, etc.? And then when the user logs in, does the app query that large table for all objects in that large table that has the currently logged in users id associated with recipients?

Talking very high-level, conceptual. This is my thought of how it would work, but I know there is millions of pictures on Snapchat servers, so I'm not sure how the speed of querying a large table like that would work, or how else it would be organized.


texts: Is there a tool to check mysql/mariadb database is all OK?

texts:
Today, Mariadb went down on one of my servers. The specific issue was something to do with ibdata1 file being locked. I found a website that showed how to fix it, and so all was OK. So I thought.

30 minutes later it went down again, same issue, same fix.

Its now running OK but I am worried it will happen again. I dont know why this would just happen when its been running fine for so long. Its a Linode server that runs Virtualmin for LAMP stack website hosting. Quite straightforward.

So I wondered if there is some sort of tool that can check over the DB and files to check there is nothing corrupt or untoward. Much like you do for operating systems, checking the disk is OK, defragging windows etc. Anything that gives the DB a once over to check all is OK.


texts: group result of multiple table to get unique result after count operation


texts:
How are passwords encoded in column passwd of pg_shadow?

Doing a:

select passwd from pg_shadow;

I see values like the following:

 md52c92c858aa1fa12144f1dcbd4ca9c3a0

... which, doesn't seem to be what I know my password's MD5 hash to be (stripping the leading "md5" obviously). Are the hashes also salted?


texts: error "--secure-file-priv option" when save selection to csv

texts:
I try to save a very large select (more than 70 milion rows into csv file) on Win 2012 R2 server

I execute query as root , but I think there is still some problem with privileges

select *
INTO OUTFILE 'D:\my_folder\my_file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from my_table
where
...

Error

  Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Thank you in advance for any help !


texts: How to reference primary key when inserting in MySQL

texts:
I have a database containing the tables authors and books.

I don't know how to correctly assign the foreign key in the books table. I read the information provided in this link and have constructed my DDL queries as follows:

CREATE TABLE if not exists authors(
-> author_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(30)
);

CREATE TABLE if not exists books(
-> book_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> title VARCHAR(255),
-> genre VARCHAR(30),
-> FOREIGN KEY(author_id) REFERENCES authors(author_id)
);

Is this the correct way to create a foreign key relationship between my authors and books tables?


texts: Availability Group reporting disconnected replica

texts:
I'm trying to configure availability groups in a VM environment so I can run some tests.

I think I've got the group created correctly, I can see the Always on group on both servers. However when I look at the dashboard for the group it has the following error

  Availability replica disconnected
  This secondary replica is not connected to the primary replica. The connected state is DISCONNECTED.

I've checked the endpoints on both servers and they look correct. There are no firewalls running and both servers can see each other. What's the best way to debug this sort of error?

Below is the TSQL I used to set all this up

Primary Server

CREATE ENDPOINT dbm_endpoint
    STATE=STARTED
    AS TCP (LISTENER_PORT=7022)
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

Secondary Server

CREATE ENDPOINT dbm_endpoint
    STATE=STARTED
    AS TCP (LISTENER_PORT=5022)
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

Primary Server

CREATE AVAILABILITY GROUP AG1
    FOR
        DATABASE TestDb
    REPLICA ON
        'SQL1' WITH
            (
                ENDPOINT_URL = 'TCP://sql1.sql.sandbox.net:7022',
                PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE),
                SECONDARY_ROLE (ALLOW_CONNECTIONS=READ_ONLY),
                AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
                FAILOVER_MODE = MANUAL
            ),
        'SQL2' WITH
            (
                ENDPOINT_URL = 'TCP://sql2.sql.sandbox.net:5022',
                PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE),
                SECONDARY_ROLE (ALLOW_CONNECTIONS=READ_ONLY),
                AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
                FAILOVER_MODE = MANUAL
            );

Secondary Server

ALTER AVAILABILITY GROUP AG1 JOIN;

Obviously I also restored the primary database to the secondary server as well.

One thought, I didn't install the SQL Agent on either server, I'm guessing this is not needed for always on availability groups?


texts: Setting up replication for various mongodb micro instances

texts:
We currently have 5 mongodb micro instances on aws, each of them serve different modules and the calls are made through REST services.
We are planning to setup replication for these instances and my question would be, is it possible to setup replication for all these instances in a single instance?
The data is pretty small in these instances, so i do not want to have primary and secondary for each one them.

Thanks in advance.


texts: Is it better to use multiple one-to-many relationships, or polymorphic?

texts:
I've got the following tables:

Clicks table
Websites table
Ads table

Both Ads and Websites have many Clicks (i.e. both models have a one-to-many relation with Clicks). In such cases, is it better to use multiple one-to-many relationships, or would a Clickable polymorphic setup be better?


texts:
we are going to upgrade from SQL server 2012 Standard edition to SQL Server 2016 Enterprise edition. we can't have a downtime more than 30 mins .The new SQL server will use always on availably group 1 primary and 2 read-only secondaries .   I  am looking for the fastest way to do this upgrade.
I am thinking  of creating mirroring between the old server and new primary server , then shut down the application update the connection string , fail over the DB to the new server, stop mirroring,  start the application. After  the application is up,  start in configuring the Availability group.

IS this a correct way to use


texts: Postgres JSONB - Flatten nested objects and groupings

texts:
As i've learned, if i need to flatten a JSON contained in a specific field of my postgres db table, i can use one of the following functions/operators
Now, those functions are perfect for a simple for some simple flattening like this one:
{
   "manufacturer":[
      {
         "manufacturer":"Tesla",
         "address":"Deer Creek Road Palo Alto",
         "contact":"support@tesla.com"
      },
      {
         "manufacturer":"BMW",
         "address":"Petuelring 130, 80809 München",
         "contact":"support@bmw.com"
      }
   ]
}

Using the jsonb_to_recordset function the json will be flattened without any problem.
Now, here comes my effective question, i'm not able to address in order to solve this problem effectively...
Suppose that every manufacturer has a nested JSON object that exposes the model and starting price like reported in the following JSON
{
   "cars":[
      {
         "manufacturer":"Tesla",
         "address":"Deer Creek Road Palo Alto",
         "contact":"support@tesla.com",
         "models":[
            {
               "model":"Model S",
               "starting_price":50000
            },
            {
               "model":"Model 3",
               "starting_price":35000
            },
            {
               "model":"Model X",
               "starting_price":70000
            }
         ]
      },
      {
         "manufacturer":"BMW",
         "address":"Petuelring 130, 80809 München",
         "contact":"support@bmw.com",
         "models":[
            {
               "model":"X1",
               "starting_price":35000
            },
            {
               "model":"X2",
               "starting_price":32000
            },
            {
               "model":"X3",
               "starting_price":39000
            },
            {
               "model":"Series 5",
               "starting_price":55000
            },
            {
               "model":"Series 3",
               "starting_price":50000
            }
         ]
      }
   ]
}

How i can create a query that also flattens the models object effectively and outputs many rows as the manufacturer models reported in the models object?

Bonus Question:
In addition to that, it's possible to create a query that outputs something like this in a more compact way grouping rows as shown below?

Any help is appreciated!


texts: Advice needed for Querying DateTimeOffset data by end users

texts:
We are building an Operational Data Store (ODS). The data is coming from our production databases which are located in the United States and Asia. The data in our production databases store date/time values as local time (no UTC or offsets). When we bring the data into our ODS, we're storing all date/time values as DateTimeOffset values.

When our users query the ODS data, we don't want them to have to think about including the appropriate offset value in their queries involving date/time values. We want them to be able to query the data as if it were simple local time values.

We're masking the offset values on date/time values by having users query views. The views return local time values and strip off the offset values. While this works, any indexes on the underlying datetimeoffset columns are not used because the act of converting the datetimeoffset to datetime in the views causes indexes to not be used.

So, I'm looking for advice on how others, who have dealt with this issue, have dealed with it. We need to be able to utilize indexes while, at the same time, not require users to think about time zones and daylight savings time when querying the data.

We're using SQL Server 2012.