MySQL: Migrating database with utf8 collation and charset but latin1 data to new full UTF-8 database With my current situation I have mysql database with collation and character sets on database and tables set to utf8, but all data is still latin1. This is due to server being configured (my.cnf) with:

character-set-server = latin1

Here is more information on old database server:

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Now we have new properly configured database instances with full UTF-8 and I need to dump the data. I have been trying it different ways without any luck. The data now on original database is garbled and I need to export it to full UTF-8 database.

I have tried this:

mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
--skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql

mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
--default-character-set=utf8 DB_NAME < DB_NAME-dump.sql

But it does not help as the data in .sql files and in new database is garbled UTF-8

After reading various issues and trying different ways, this was the solution for me:

The dump has to be done like this (make sure you pass the filename as -r argument):

mysqldump -h HOST -u USER -p --default-character-set=latin1 DATABASE -r utf8.dump

Then I have opened utf8.dump file and changed one line at the top.

From

/*!40101 SET NAMES latin1 */;

To

/*!40101 SET NAMES utf8 */;

Then I imported it to (make sure to use mysql instead of mysqldump and source the file, do not use "<" to pass it from terminal):

mysql -u USER -p DB -h HOST

mysql> source utf8.dump

After this, all my data was correctly encoded and stored as UTF-8 (e.g. hÃ©t -> hét)


Conditional where for postgres stored procedure I want to create a conditional where clause using a PostgreSQL stored procedure.

This is my original stored procedure :

    select distinct
    t.serialnumber as sn,
    t.productid as tid,
    t.merchantid as mid
    from tbterminal as t
    ORDER BY idterminal ASC

I want to check if p_mid, p_tid and p_sn are null or empty; if not null and not empty, add an and where for every component.

This is what I've tried :

CREATE OR REPLACE FUNCTION get_list_terminal_datatable (
    p_mid varchar,
    p_tid varchar,
    p_sn varchar,
    p_limit int,
    p_offset int
)
 RETURNS TABLE (
 sn VARCHAR,
     tid varchar,
     mid varchar
)
AS $$
DECLARE
  data_where varchar := 'WHERE ';
BEGIN

 IF p_mid notnull and p_mid != '' THEN
   data_where := data_where || ' t.merchantid like '''%|| p_mid ||'%'' ';
 END IF;
 IF p_tid notnull and p_tid != '' THEN
   data_where := data_where || ' t.productid like '''%|| p_tid ||'%'' ';
 END IF;
 IF p_sn notnull and p_sn != '' THEN
   data_where := data_where || ' t.serialnumber like '''%|| p_sn ||'%'' ';
 END IF;
 RETURN QUERY
 select distinct
    t.serialnumber as sn,
    t.productid as tid,
    t.merchantid as mid
    from tbterminal as t
    ORDER BY idterminal ASC
    limit p_limit offset p_offset;
END; $$

LANGUAGE 'plpgsql';

I know it doesn't work properly, so I need help on how to make the conditional where clause stored procedure in PostgreSQL work.

How would I have to write the stored procedure correctly?

You can use Boolean expressions, that return true, if a parameter is null or empty, otherwise the result of a LIKE operation.

...
SELECT DISTINCT
       t.serialnumber as sn,
       t.productid as tid,
       t.merchantid as mid
       FROM tbterminal as t
       WHERE (coalesce(p_mid, '') = ''
               OR t.merchantid LIKE '%' || p_mid || '%')
             AND (coalesce(p_tid, '') = ''
                   OR t.productid LIKE '%' || p_tid || '%')
             AND (coalesce(p_sn, '') = ''
                   OR t.serialnumber LIKE '%' || p_sn || '%')
       ORDER BY idterminal ASC
       LIMIT p_limit
       OFFSET p_offset;
...


Export from Oracle 10g and import to Oracle 12c unable to create users I have and old install of Oracle 10g with a lot of users and tablespaces in it, for development purposes. Now, I need to migrate these users, tablespaces and everything else, to an Oracle 12c installation.

Following some posts online, I did this command to export, everything I hope, from the Oracle 10g.

CREATE DIRECTORY datapump AS '/oracle/datapump'
expdp system/password@osub full=Y directory=datapump dumpfile=DB10G.dmp logfile=expdpDB10G.log

It executed with no errors. To import on the oracle 12c instance I did this:

CREATE DIRECTORY datapump AS '/oracle/datapump'
impdp system/password@osub full=Y dumpfile=all.dmp logfile=impdpDB10G.log directory=datapump

But on the log file I get error like this:

CREATE USER "BI" IDENTIFIED BY VALUES 'FA1D2B85B70213F3' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK
ORA-39083: Object type USER:"JBOSSDB" failed to create with error:
ORA-65096: invalid common user or role name
Failing sql is:
 CREATE USER "JBOSSDB" IDENTIFIED BY VALUES '00C6DD010C22FF42' DEFAULT TABLESPACE "JBOSSDB" TEMPORARY TABLESPACE "TEMP"

I read online about Oracle pluggable databases. I don't care about any of that.
I just wanted to import all the users, tablespaces etc, that I had on Oracle 10g, without having to do it manually one by one.

Is it possible?

You have created a CDB (Container Database), and connected to the root container. There you are not allowed to create regular users, you can create common users whose name starts with C##.

If you do not care about or want to use pluggable databases, I suggest that you drop your empty 12c database, and create a new non-CDB with DBCA (Database Configuration Assistant).


Update unpivoted table with one to many relation Imagin you have a main table like that

DECLARE @ExistentData TABLE (ID INT IDENTITY(1000,1), [Row] INT, [Col] INT, Val NVARCHAR(50));
INSERT INTO @ExistentData([Row], [Col], Val)
VALUES
(1,10,'Value 1'),
(1,11,'Value 2'),
(1,12,'Value 3.1'),
(1,12,'Value 3.2')

ID          Row         Col         Val
----------- ----------- ----------- --------------------------------------------------
1000        1           10          Value 1
1001        1           11          Value 2
1002        1           12          Value 3.1
1003        1           12          Value 3.2

Then you want to update above table with data below

DECLARE @NewDataToUpdateExistent TABLE ([Row] int, [Col] int, Val nvarchar(50));
INSERT INTO @NewDataToUpdateExistent([Row], [Col], Val)
VALUES
(1,10,'Updated Value 1'),
/*(1,11,'Deleted Value 2'),*/
(1,12,'Updated Value 3.1'),
(1,12,'Updated 3.2'),
(1,12,'New Value 3.3')

Row         Col         Val
----------- ----------- --------------------------------------------------
1           10          Updated Value 1
1           12          Updated Value 3.1
1           12          Updated 3.2
1           12          New Value 3.3

I want to do some performant update (without using cursors or other ugly iteration stuff)
resulting in the following final data on main table (@ExistentData)

ID          Row         Col         Val
----------- ----------- ----------- --------------------------------------------------
1000        1           10          Updated Value 1
1002        1           12          Updated Value 3.1
1003        1           12          Updated Value 3.2
1004        1           12          New Value 3.3

Deleting all related data and insert again (atm I'm doing this ) is not an option cause is an expansive operation regarding FK with other tables and index/statics management.

DECLARE @ExistentData
TABLE (ID INT IDENTITY(1000,1), [Row] INT, [Col] INT, Val NVARCHAR(50));

INSERT INTO @ExistentData([Row], [Col], Val)
VALUES
(1,10,'Value 1'),
(1,11,'Value 2'),
(1,12,'Value 3.1'),
(1,12,'Value 3.2')
select * from @ExistentData

DECLARE @NewDataToUpdateExistent TABLE ([Row] int, [Col] int, Val nvarchar(50));
INSERT INTO @NewDataToUpdateExistent([Row], [Col], Val)
VALUES
(1,10,'Updated Value 1'),
/*(1,11,'Deleted Value 2'),*/
(1,12,'Updated Value 3.1'),
(1,12,'Updated 3.2'),
(1,12,'New Value 3.3')
select * from @NewDataToUpdateExistent

With ROW_NUMBER you do the trick

DECLARE @A TABLE (ID INT, [Row] INT, [Col] INT, Val NVARCHAR(50), [RowNumb] INT);
INSERT INTO @A (ID, [Row], [Col], Val, RowNumb)
SELECT A.ID, A.[Row], A.Col, A.Val,
ROW_NUMBER() OVER
    (PARTITION BY A.[Row], A.[Col] ORDER BY A.[Row], A.[Col] DESC) AS RowNumb
FROM @ExistentData A
;

DECLARE @B TABLE ([Row] INT, [Col] INT, Val NVARCHAR(50), [RowNumb] INT);
INSERT INTO @B ([Row], [Col], Val, RowNumb)
SELECT B.[Row], B.Col, B.Val,
ROW_NUMBER() OVER
    (PARTITION BY B.[Row], B.[Col] ORDER BY B.[Row], B.[Col] DESC) AS RowNumb
FROM @NewDataToUpdateExistent B
;

Now just joining tables in the following way you get the expected result

--Delete
DELETE ed
FROM @A a
LEFT OUTER JOIN @B b
    ON  a.[Row] = b.[Row] AND a.[Col] = b.[Col]
    AND a.[RowNumb] = b.[RowNumb]
INNER JOIN @ExistentData ed ON a.ID = ed.ID
WHERE b.[Row] IS NULL

--Update
UPDATE ed
SET ed.Val = B.Val
FROM @A a
INNER JOIN @B b
    ON  a.[Row] = b.[Row] AND a.[Col] = b.[Col]
    AND a.[RowNumb] = b.[RowNumb]
INNER JOIN @ExistentData ed ON a.ID = ed.ID

--INSERT
INSERT INTO @ExistentData([Row], [Col], Val)
SELECT b.[Row], b.[Col], b.Val
FROM @B b
LEFT OUTER JOIN @A a
    ON a.[Row] = b.[Row] AND a.[Col] = b.[Col]
    AND a.[RowNumb] = b.[RowNumb]
WHERE A.[Row] IS NULL

  SELECT * FROM @ExistentData
      ORDER BY id

ID          Row         Col         Val
----------- ----------- ----------- --------------------------------------------------
1000        1           10          Updated Value 1
1002        1           12          Updated Value 3.1
1003        1           12          Updated Value 3.2
1004        1           12          New Value 3.3


Unknown source of high number of connections In MySQL/Aurora I see from time to time a spike in connections number (from about 100 to 4000):

MySQL doc says:

Connections
The number of connection attempts (successful or not) to the MySQL server.

So I was thinking those coming from failed connection, however I don't see failures:
MySQL [(none)]> SHOW GLOBAL STATUS like 'Conn%';
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Connection_errors_accept          | 0        |
| Connection_errors_internal        | 0        |
| Connection_errors_max_connections | 0        |
| Connection_errors_peer_address    | 0        |
| Connection_errors_select          | 0        |
| Connection_errors_tcpwrap         | 0        |
| Connections                       | 34388970 |
+-----------------------------------+----------+
7 rows in set (0.00 sec)

MySQL [(none)]> show global variables like '%conn%';
+-----------------------------------------------+-------------------+
| Variable_name                                 | Value             |
+-----------------------------------------------+-------------------+
| aurora_max_connections_limit                  | 16000             |
| connect_timeout                               | 10                |
| disconnect_on_expired_password                | ON                |
| init_connect                                  |                   |
| max_connect_errors                            | 100               |
| max_connections                               | 4000              |
| max_user_connections                          | 0                 |
| performance_schema_session_connect_attrs_size | 512               |
+-----------------------------------------------+-------------------+

I would very much like to understand what those are and when they are coming from.

Sounds like a hacker.
Based on your graphs, I suggest that max_connections = 4000 is unreasonably high.  It looks like 20 might be reasonable.
You might want to set max_user_connections to some reasonable value (instead of 0 for "unlimited").


Syntax error when using exception inside for loop I am trying to create the following procedure on postgresql

CREATE PROCEDURE run_all_scheduled_tasks()
LANGUAGE plpgsql
AS
$$
DECLARE
    rec record;
BEGIN
    FOR rec IN SELECT * FROM some_table LOOP
        -- Statement 1..2..3..4
        EXCEPTION
            WHEN others THEN
                RAISE NOTICE 'Error Occured! Code: %, Messege:%', sqlstate, sqlerrm;
                ROLLBACK;
        COMMIT;
    END LOOP;

END;
$$;

I get the following error:

  ERROR:  syntax error at or near "EXCEPTION"
  LINE 12:   EXCEPTION

It works properly if I shift exception block outside the for loop.

What I want to achieve is that for one iteration of loop, either all statements from 1..4 should get executed or none of them. If an error occurs, I still want to preserve the changes made up till previous iteration and then carry on to next iteration by simply raising a notice for the error.

What is causing this error?

As stated above in the comment, what I was missing was another BEGIN enclosing the exception.

Updated code:

CREATE PROCEDURE run_all_scheduled_tasks()
LANGUAGE plpgsql
AS
$$
DECLARE
    rec record;
BEGIN
    FOR rec IN SELECT * FROM some_table LOOP
        BEGIN
        -- Statement 1..2..3..4
        EXCEPTION
            WHEN others THEN
                RAISE NOTICE 'Error Occured! Code: %, Messege:%', sqlstate, sqlerrm;
                ROLLBACK;
        END;
        COMMIT;
    END LOOP;

END;
$$;


How to calculate time difference in sql I have one table named  test.
In that table I have a column named  test_time of type DATE. I want to calculate the time difference between test_time and sysdate in minutes.

select 1440 * (to_date('2009-07-07 22:00', 'YYYY-MM-DD hh24:mi')
- to_date('2009-07-07   19:30', 'YYYY-MM-DD hh24:mi')) diff_hours
from dual;

this one giving time difference in minutes. But I tried this one to my table like

select 1440 * (to_date(test_time, 'YYYY-MM-DD hh24:mi')
- to_date(sysdate, 'YYYY-MM-DD hh24:mi')) diff_hours
from test;

but I'm not able to calculate the time difference in minutes. Kindly help me to solve this problem.

DATEis the Oracle datetime data type for storing a datetime value with a precision of one second.  The function TO_DATE converts a character type (VARCHAR2,CHAR,...)  to the DATE type. The first string is the string to convert, the secons string tell the convert function which format is used to code the date in the first string. So the result of  TO_DATE('2014-04-18 10:33', 'YYYY-MM-DD HH24:MI') is the DATE  equivalent to '2014-04-18 10:33:00'. The seconds are set to 0.

The function SYSDATE returns a DATE value  so it must not be converted further. The column  test_time is of data type DATE, too, it also must not be converted further. To get the DATEdifference you have only to calculate test_time-SYSDATE This difference of two DATE values gives the number of days. Therefore you have to multiply the above value by 1440 (=24*60, the number of minutes per day). You end up with

select (test_time - sysdate)*24*60
  from test;

This value is negative if test_time is lies in the past. If you want an integer value then round the  result

select round((test_time - sysdate)*24*60,0)
  from test;


How to structure renting and sale as type of purchase? I'm currently planning a database which involves two different types of Products that can be bought outright, or rented for a weekly or monthly amount. I'm just trying to figure out how to structure the database for this.

I'd like to keep the Products all in the same table. The only way I've thought of so far is to have my Product table reference another ProductType table which has rental or sale (not sure on the wording here really), then have another table which is linked to the product which has the cost. I don't think this is correct however.

How can I structure this relationship? (I don't mind any changes to this at all, other than the Products all being listed in that one table still).

As long as you don't have any additional data to store with the "rental" or "sell" property, you don't need a table for it. Just use a COLUMN manage ENUM('rent', 'sale'). Then you have to think about prices. The "BuyMe" price seems simple. Renting Models may have a real interesting live, I mean there are a lot of ideas which come in mind, and much more ideas which already live out there, not to speak about the infinite and strange wishes of some Marketing Stuff. So you would be wise enough to point the product to a category of price models. When you have rental, I assume that some products are more or less equal.

This Price Category can then be resolved to a Price Model, so you can combine the time of rental together with the Price Model to the real price.

In the beginning start with your original idea of just a price per day (or price per hour), the Price Model contains just this single one value and you multiply number of days (or of hours) with that single number. When it gets more complicated, just extend the table (and the code, of course).

So you have a table:

CREATE TABLE products (
      id int not null auto_increment primary key
    /* , name, praising, weight, properties... */
    , manage ENUM('rent', 'sale')
    , pricecategory_id int DEFAULT NULL
    , sale_price decimal(12,2) DEFAULT NULL
    /* indices */
);

CREATE TABLE pricecategory (
      id int not null auto_increment primary key
    , name varchar(255)
);

CREATE TABLE pricemodel (
      id int not null auto_increment primary key
    , name varchar(255)
    , pricecategory_id int not null
    , price_per_day decimal(18,5)
    , index(pricecategory_id)
);

You refer from pricemodel to pricecategory, since sooner or later you might get different types of customers, which get different prices (Gold Card Members or something like that). So you just add the customertype to pricemodel and are ready for the beginning.

Think about Special Offers, Easter Bundles etc. products.sale_price is only set for the sale case, and pricecategory_id only for rent.

Have fun with the application and grow! I know of Price Models which tend to need nice Graph Theory Optimization to get calculated, since the different paths have to be compared and of cause the customer gets 'best price' between hour, day, and a lot more.


Make "as" mandatory when create column alias? We found a lot of bugs are caused by missing comma,

select
    aaaa,
    bbbb  -- forgot comma
    ccc,
    dddddddd,
.....

Is it a way to make expression name invalid and it must be expression as name in a SQL statement? (not globally because of missing "as" exists legacy code)

No, there is no way to force that syntax, unless you pass all queries through some 3rd party tool that parses the SQL code and supports looking for syntax like this, but I think it will be very difficult to even approach writing something that will do this.

It is also not possible to stop someone from typing FROM dbo.table nolock and not realizing that in that case nolock is an alias, not a locking hint. That is why this works:

SELECT name FROM sys.objects nolock WITH (NOLOCK);

In addition, I find the expression AS alias syntax far less usable than alias = expression - since, when I'm reviewing or troubleshooting code, I'm much more likely to need to find a column by its alias at the beginning of a line, than by reading what could be a very long and complex expression, that spans lines or goes off the screen to the right, and ends at a different vertical point than all the other lines. And since aliases should be shorter than expressions, it makes more sense to me to put the alias first.

At the end of the day, though, it's a preference, and no, there is no way to force it. Don't let your developers blame the language for their sloppiness. This is something that should be caught before a second set of eyes ever sees that query. I mean, how hard is it to count how many columns are returned by your query?


Postgresql: No data left on drive, how can I wipe it and start over? I have a system with a non-critical postgres database that was unmonitored and consumed all available space on its drive. The OS is unaffected so I am free to delete files.

I would like to just delete the files and reinitialize the database to essentially start from scratch. How can this be done?

I'm running postgres 9.1. The config directories are in /etc/postgres but all of the actual postgres data is in /var/lib/postgres, which is mounted in a different volume group.

Since you say that the config directories are in /etc/postgres I'm guessing you're running Ubuntu or Debian, which use pg_wrapper for management of their PostgreSQL installs.

On these platforms you use pg_dropcluster to destroy and remove all databases, then pg_createcluster to create a new blank server instance.

This guide for PostgreSQL on Ubuntu may be informative.


MariaDB CONNECT engine BIN table - file record layout, storage of a date data type, padding In mariadb-10.4.5, running on Windows 10, I created this table (in the local database):

`CREATE TABLE IF NOT EXISTS `testbalNoFieldFormat` (
  `fig` int(4) NOT NULL,
  `name` char(10) NOT NULL,
  `birth` date NOT NULL,
  `id` char(5) NOT NULL,
  `salary` double(9,2) NOT NULL DEFAULT 0.00,
  `dept` int(4) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=utf8 `table_type`=BIN `block_size`=5 `file_name`='TestbalNoFieldFormat.dat'

INSERT INTO testbalNoFieldFormat VALUES(1,"1234567890",'2009-01-20','abcde',100000.12,1234)

I inspected the file in Binary Viewer from https://www.proxoft.com/BinaryViewer.aspx.

In Binary Viewer (enclosed in double quotes so you can see the start and end):

As individual bytes viewed as unsigned integers, not octals:

"   1   0   0   0   49  50  51  52  53  54  55  56  57  48  32  32
    32  32  32  32  32  32  32  32  32  32  32  32  32  32  32  32
    32  32  48  93  116 73  97  98  99  100 101 32  32  32  32  32
    32  32  32  32  32  184 30  133 235 1   106 248 64  210 4   0
    0"

As ASCII, it is:

"   .   .   .   .   1   2   3   4   5   6   7   8   9   0

            0   ]   t   I   a   b   c   d   e
                        ?   .   ?   ?   .   j   ?   @   ?   .   .
    ."

Grouped into 4-byte integers (Little Endian), the bytes are:

"   1           875770417   943142453   538980409
    538976288   538976288   538976288   538976288
    1563435040  1650542964  543515747   538976288
    538976288   2233382944  4167696875  315968
    538976256"

I can see the first int value that I inserted (first field, value 1) but not the second int value that I inserted (last field, value 1234). Possibly there is some padding affecting things but 65 bytes seems an odd number to pad to - I was thinking it would pad to a multiple of 8 bytes, or 4 bytes.

The file length is 65 bytes, vs 46 bytes for the previous table (testbal).

Can anyone interpret the byte structure for me? In particular, which bytes represent fields 3 (birth), 5 (salary), 6 (int)?

Which bytes represent padding?

How can I extract the year, month and day of month from the bytes representing the value of the date field 'birth'?

1   0   0   0  -- 1; note "little-endian"
49  50  51  52  53  54  55  56  57  48  -- ascii/utf8 for "1234567890"
32  32  32  32  32  32  32  32  32  32
32  32  32  32  32  32  32  32  32  32 -- lots of spaces for padding
48  93  116 73   -- must be the date?
97  98  99  100 101   -- ascii/utf8 for 'abcde'
32  32  32  32  32
32  32  32  32  32   -- lots of spaces for padding
184 30  133 235 1   106 248 64  -- 100000.12 --> hex: 40 f8 6a 01 eb 85 1e b8
210 4   0   0  -- 1234 = 210 + 4*2^8 + 0 * 2^16 + 0 * 2*24

On INT(4), the (4) has no meaning (unless you also use ZEROFILL).  INT is always 4 byte, but not because of (4).

DOUBLE is always 8 bytes; (m,n) should not be used.

The spaces are padding CHAR(n) CHARACTER SET utf8 out to 3 bytes per character.  This is an argument for almost always using VARCHAR, not CHAR.

With utf8mb4 there would be even more spacing.


Summarise data into count per hour, average per day I need to summarise a dataset to show the hourly totals per day. The dataset contains travel card transactions for a period of 4 weeks. I would like to see the total transactions per hour (00:00-01:00, 01:00-02:00,...23:00-00:00) for each of the 28 days.
If this is achievable I would hope to resample this to show the average transactions per hour for weekday and weekend, respectively.
The datetime field is datatype timestamp without timezone i.e. 'YYYY-MM-DD HH:MM:SS'.
Is this something PostgreSQL can accomplish?

date_tunc() is the key.
Transactions per hour
SELECT date_trunc('hour', datetime) AS hour
     , count(*) AS transactons
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

Average transactions per hour

... for weekday and weekend, respectively.

SELECT extract('ISODOW' from hour)::int/6 AS weekday_weekend
     , round(avg(transactions), 2) AS avg_transactions
FROM  (
   SELECT date_trunc('hour', datetime) AS hour
        , count(*) AS transactions
   FROM   tbl
   GROUP  BY 1
   ) sub
GROUP  BY 1;

0 .. weekday
1 .. weekend
ISODOW produces 1 - 7 for Mon - Sun. Sat & Sun are "weekend". Integer division x/6 returns 0 for weekdays and 1 for weekend.
Include hours without activity
Above queries only observe hours with actual transactions. To include hours without transactions (no rows int the table) with a value of 0 transactions, (outer) join to a an hourly raster / grid for all of the given period of 28 days. And use COALESCE(). Like:
SELECT extract('ISODOW' from hour)::int/6 AS weekday_weekend
     , round(avg(COALESCE(transactions, 0)), 2) AS avg_transactions
FROM  (
   SELECT generate_series(timestamp '2020-12-01' -- first day
                        , timestamp '2020-12-28' -- last day
                          + interval '1 day - 1 hour'
                        , interval '1 hour')
   ) grid(hour)
LEFT JOIN (
   SELECT date_trunc('hour', datetime) AS hour
        , count(*) AS transactions
   FROM   tbl
   GROUP  BY 1
   ) sub USING (hour)
GROUP  BY 1;

The expression timestamp '2020-12-28' + interval '1 day - 1 hour' produces the latest hour of interest for the time series.
db<>fiddle here
Further reading:

PostgreSQL: running count of rows for a query 'by minute'
Generating time series between two dates in PostgreSQL
How to get the number of days in a month?
Calculate working hours between 2 dates in PostgreSQL


Recovery using Archive redo log files As I'm new in using RMAN and archivelog, I've a few doubts regarding to this. My database is Oracle 11g(windows platform). It was previously in noarchivelog mode and recently I've changed it to archivelog mode to backup my data and it started generating archived redo logs in the ARCHIVELOG directory. Each day almost 7 or 9 GB archived redo logs are generating.

Also, I have set my recovery destination and size as 50G and recovery window parameter as 4 days. I know that archive redo log files help me for the point on recovery.

But I am not getting a clear idea about below.

Do I need to delete archivelog mode or will it automatically get cleared?
If I move these archivelog files to other location or disk as backup, should it help me to recover data till that date?
If I need to restore a test database using the production database, is it possible with these archive files? Does it need any other processing?

Thanks in advance.

Do I need to delete archivelog mode or will it automatically get cleared?

Configure archivelog deletetion policy as-

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

Then you can-

RMAN> DELETE ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK;

  Archived redo logs can be deleted automatically by the database or as a result of user-initiated RMAN commands. Only logs in the flash recovery area can be deleted automatically by the database. For archived redo log files in the flash recovery area, the database retains them as long as possible and automatically deletes eligible logs when additional disk space is required.

Configuring an Archived Redo Log Deletion Policy

  If I move these archivelog files to other location or disk as backup, should it help me to recover data till that date?

Yes, As long as RMAN can access these files.

  If I need to restore a test database using the production database, is it possible with these archive files? Does it need any other processing?

Archived Redo Logs are not sufficient to restore and recover a test database, you need to have full RMAN backup as well. Howerver, if you have physically identical database like Physical Standby database then these archived logs can be used to refresh such databases.


Generating two rows from one source row I have a table which has columns:

txn_id
from_user_id
to_user_id
price

I want a query that produces two rows for each source row. The output would look like:

focal_user_id (new)
txn_id
from_user_id
to_user_id
price

The focal_user_id value is generated from from_user_id for the first row output, and generated from to_user_id for the second row.

I need to do this in SQL rather than code because then I can make use of WHERE and LIMIT, as well as do some joins based on the new focal_user_id column.

Unless I completely destroy the question in my head this is what you meant by two rows out of each one:

create table #TableOne (
    TxnId int,
    FromUserId int,
    ToUserId int,
    Price decimal(5,2)) ;

insert into #TableOne values
    (1, 1, 2, 100.01),
    (2, 1, 2, 100.01),
    (3, 2, 1, 100.01),
    (4, 2, 1, 100.01),
    (5, 3, 4, 100.01),
    (6, 4, 3, 100.01),
    (7, 4, 3, 100.01) ;

select
    FromUserId AS FocalUserId,
    TxnId,
    FromUserId,
    ToUserId,
    Price
from #TableOne
union all
select
    ToUserId AS FocalUserId,
    TxnId,
    FromUserId,
    ToUserId,
    Price
from #TableOne ;

Alternatively, using APPLY (lateral join):

SELECT
    CA.FocalUserId,
    TON.TxnId,
    TON.FromUserId,
    TON.ToUserId,
    TON.Price
FROM #TableOne AS TON
CROSS APPLY
(
    VALUES
        (TON.FromUserId),
        (TON.ToUserId)
) AS CA (FocalUserId);


Create user to monitor database activity on Postgres Postgres 9.3
Debian 7

I want to create a monitor user called monitor that the only permission is to run queries to monitor all the database activity.
I created the user and it can select from pg_stat_activity, the problem is that it only sees it's own activity and I want this user to see all users activity.

Is there any special privilege to do that?

At this time, only superusers can see others' activity details in pg_stat_activity.

PostgreSQL could use a finer-grained rights model, where you can GRANT the MONITOR_QUERIES right to a user, for example. But right now it doesn't have one, and quite a few things are superuser-only. This is one of them.


Find Without primary key Table in SQL Server 2008 R2? This is my query to find out primary key, foreign key in SQL Server 2008 R2:

select  *
from information_schema.table_constraints
where constraint_type = 'PRIMARY KEY'

Know I need to select without primary key table in my database.

Thanks

Try this:

SELECT TABLE_NAME FROM information_schema.tables
WHERE TABLE_NAME
NOT IN(
SELECT TABLE_NAME from information_schema.table_constraints
WHERE constraint_type = 'Primary Key' )
AND TABLE_TYPE = 'BASE TABLE'

Edit: As per the comment from @MarkSinkinson this query will be wrong in case of Null value  information_schema.table_constraints.table_name, so I would advice the OP to follow with link added in comment which is

SELECT OBJECT_SCHEMA_NAME( object_id ) as SchemaName, name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'tablehasprimaryKey') = 0
ORDER BY SchemaName, TableName ;

to get the list of table not having Primary Key

Know more about this OBJECTPROPERTY which is a better solution here.


Handling manufacturer mergers Is there a standard pattern for linking products to manufacturers, when we want to capture the fact that manufacturers are interacting with each other over time (mergers, takeovers, etc)?

In our industry, company A makes product 1, and many people refer to that product with a token like "A-1" when talking about it or trying to look up detailed performance and physical characteristics of that product in our database.

If company B purchases A, we still want to be able to allow people to use a token like "A-1" to look up the performance data of that product, even though the owning company is now B. A new user meanwhile can look at the records and understand that company A made the product and is the former owner, while company B is now the "real" owner.

Is it viable to model this with two manufacturer foreign keys, and a version column that would increment when the manufacturer relationship changes? The version column would be used to allow the older "A owns 1" row to remain in the table for existing records that linked to it at the time A owned 1 before being bought out by B.

This is how I might approach this design... by adding an ID for the original manufacturer at the Product level, then storing the details in a history table.

The history table would allow for Manufacturer IDs (or other details) to change over time to be stored without a need for version numbers.

Product table

Product_ID
Product_Name
Orig_Mfgr_ID

Product History table

Product_ID
ProductHistory_ID
Orig_Mfgr_ID
Mfgr_StartDate
Mfgr_EndDate
CreateDate

I would then create a view or function to pull the most recent/current manufacturer based on the purchase date of the product, rather than storing this information in the Product table itself.


Mysqld draining memory, using way more then assigned and not giving any back This is a production server.
Whenever mysqld is restarted the memory consumption slowly builds up to about 75 to 90% of my total memory (while only 8 out of 32GB is allocated to mysqld).
Sometimes this takes 5 hours, sometimes this takes 16 hours.
Usually it sticks around that percentage for a good amount of hours.
If that was just it it would be fine, but after some time it then starts using even more memory, Until it overflows, causing my 32GB swap drive to fill up to 100% sometimes.
Now things start to rapidly slow down to a crawl with mostly timeouts. It does recover after some time to the point it will no longer timeout for a while, but the memory never frees up so its constantly on the verge of dropping out again.
Anyone have an insight on how can this be?
Why is mysqld using so much memory and not returning any free mem?
And why is it not respecting at all the buffer limits that were set?
I have played around for some time with the mariadb.conf, but even reverting all back to default doesn’t seem to have much impact on the memory usage.
Running
mysqld  Ver 10.3.27-MariaDB-0+deb10u1

top stats
mysql     20   0   33.2g  25.1g   3760 S 521.6  80.1   1122:26 mysqld

Memory/buffer specs from mysql tuner
[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 8.0G
[--] Other process memory: 1.4G
[--] Total buffers: 7.5G global + 2.9M per thread (151 max threads)
[--] P_S Max memory usage: 104M
[--] Global Buffers
[--]  +-- Key Buffer: 128.0M
[--]  +-- Max Tmp Table: 256.0M
[--] Query Cache Buffers
[--]  +-- Query Cache: OFF - DISABLED
[--]  +-- Query Cache Size: 0B
[--] Per Thread Buffers
[--]  +-- Read Buffer: 128.0K
[--]  +-- Read RND Buffer: 256.0K
[--]  +-- Sort Buffer: 2.0M
[--]  +-- Thread stack: 292.0K
[--]  +-- Join Buffer: 256.0K

Mariadb conf
skip-name-resolve
performance_schema      = ON
query_cache_type        = 0
query_cache_size        = 0
tmp_table_size          = 256M
max_heap_table_size     = 256M
innodb_log_files_in_group       = 4
innodb_buffer_pool_size        = 7G
innodb_status_file                      #extra reporting
innodb_file_per_table                   #enable always
innodb_flush_log_at_trx_commit  = 2     #2/0 = perf, 1 = ACID
innodb_table_locks              = 0
innodb_lock_wait_timeout        = 60
innodb_thread_concurrency       = 24
innodb_commit_concurrency       = 2
innodb_log_file_size   = 384M
innodb_buffer_pool_instances = 7

Below is an analysis of what you have.  Nothing points to how it could be over-filling memory.  Please provide the evidence (top / htop / innodb_top / whatever) that shows mysqld larger than, say, 10GB.  Maybe there are some clues there.
Analysis of GLOBAL STATUS and VARIABLES:

Observations:

Version: 10.3.27-MariaDB-0+deb10u1
32 GB of RAM
Uptime = 13:59:47; some GLOBAL STATUS values may not be meaningful yet.
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.  (I don't know if Aria is consuming a lot of space.)

The More Important Issues:
To free up some RAM:  key_buffer_size = 20M
The dataset seems to be quite small; innodb_buffer_pool_size (now 7,516,192,768) is probably bigger than necessary.
If you are using a SSD drive and are having I/O issues, some settings can be adjusted.
autocommit was on when you captured the GLOBAL STATUS.  If that is your normal mode of running things, do not forget to perform COMMIT.
You are using ENGINE=Aria a lot?  (My analysis does not cover much of that.)
Several clues indicate that some queries may need optimization.  Suggest turning the slowlog on with a small value for long_query_time to see which queries need the most help.
You are doing some things with InnoDB's FULLTEXT?  I see that the stopwords are turned off an the word length has been shrunk.  How is that going?
Details and other observations:
( innodb_buffer_pool_size ) = 7,168 / 32768M = 21.9% -- % of RAM used for InnoDB buffer_pool
-- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 -- Amount of work for page cleaners every second.
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 4). Also check for swapping.
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 7 = 0.571 -- innodb_page_cleaners
-- Recommend setting innodb_page_cleaners (now 4) to innodb_buffer_pool_instances (now 7)
(Beginning to go away in 10.5)
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth
( innodb_io_capacity ) = 200 -- When flushing, use this many IOPs.
-- Reads could be slugghish or spiky.
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 100,551 / 458752 = 21.9% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size (now 7516192768) is bigger than necessary?
( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain
-- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)
( innodb_doublewrite ) = innodb_doublewrite = OFF -- Extra I/O, but extra safety in crash.
-- OFF is OK for FusionIO, Galera, Replicas, ZFS.
( innodb_log_files_in_group ) = 4 -- Number of InnoDB log files
-- 2 is probably the only reasonable value.
A large number may cause performance problems.
( innodb_flush_method ) = innodb_flush_method = fsync -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT
( Innodb_row_lock_waits ) = 10,785 / 50387 = 0.21 /sec -- How often there is a delay in getting a row lock.
-- May be caused by complex queries that could be optimized.
( Innodb_row_lock_waits/Innodb_rows_inserted ) = 10,785/8926 = 120.8% -- Frequency of having to wait for a row.
( innodb_table_locks ) = innodb_table_locks = OFF -- Something to do with autocommit=0.
( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.
( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.
( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster. 0 is OK for Galera.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( Created_tmp_disk_tables / Created_tmp_tables ) = 9,073 / 14054 = 64.6% -- Percent of temp tables that spilled to disk
-- Maybe increase tmp_table_size (now 268435456) and max_heap_table_size (now 268435456); improve indexes; avoid blobs, etc.
( Sort_merge_passes ) = 1,196,767 / 50387 = 24 /sec -- Heafty sorts
-- Increase sort_buffer_size (now 2097152) and/or optimize complex queries.
( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)
( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
( back_log ) = 80 -- (Autosized as of 5.6.6; based on max_connections)
-- Raising to min(150, max_connections (now 151)) may help when doing lots of connections.
( Connections ) = 1,584,168 / 50387 = 31 /sec -- Connections
-- use pooling?
( thread_cache_size / Max_used_connections ) = 151 / 101 = 149.5%
-- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.
Abnormally small:
Acl_users = 4
Aria_pagecache_blocks_unused = 14,017
Handler_write = 0.19 /sec
Innodb_dblwr_pages_written = 0
aria_checkpoint_log_activity = 1.05e+6
aria_pagecache_buffer_size = 128MB
eq_range_index_dive_limit = 0
innodb_buffer_pool_chunk_size = 128MB
innodb_ft_min_token_size = 2
innodb_max_undo_log_size = 10MB
innodb_online_alter_log_max_size = 128MB
innodb_sort_buffer_size = 1.05e+6
innodb_spin_wait_delay = 4

Abnormally large:
Aria_pagecache_blocks_not_flushed = 1,688
Aria_pagecache_read_requests = 14019 /sec
Aria_pagecache_write_requests = 435 /sec
Aria_pagecache_writes = 435 /sec
Aria_transaction_log_syncs = 1,618
Com_show_slave_hosts = 0.21 /HR
Created_tmp_files = 3.8 /sec
Feature_fulltext = 22 /sec
Feature_subquery = 32 /sec
Handler_discover = 3.7 /HR
Handler_icp_attempts = 62394 /sec
Handler_icp_match = 62386 /sec
Handler_read_key = 56640 /sec
Handler_read_last = 3.4 /sec
Handler_read_next = 1378189 /sec
Handler_read_prev = 32923 /sec
Handler_read_rnd = 7044 /sec
Handler_tmp_write = 13198 /sec
Innodb_buffer_pool_read_requests = 2259173 /sec
Innodb_rows_read = 1870004 /sec
Innodb_secondary_index_triggered_cluster_reads = 210238 /sec
Rows_read = 1790121 /sec
Sort_priority_queue_sorts = 12 /sec
Sort_rows = 32610 /sec
Tc_log_page_size = 4,096
aria_sort_buffer_size = 256.0MB
innodb_commit_concurrency = 2
innodb_thread_concurrency = 24
max_heap_table_size = 256MB
max_relay_log_size = 1024MB
min(max_heap_table_size, tmp_table_size) = 256MB
performance_schema_events_stages_history_size = 20
performance_schema_events_statements_history_size = 20
performance_schema_events_waits_history_size = 20
tmp_memory_table_size = 256MB

Abnormal strings:
Innodb_have_snappy = ON
aria_recover_options = BACKUP,QUICK
ft_min_word_len = 2
ft_stopword_file =
innodb_data_home_dir = /var/lib/mysql
innodb_fast_shutdown = 1
innodb_ft_enable_stopword = OFF
innodb_log_optimize_ddl = OFF
log_slow_admin_statements = ON
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT


DBCC CHECKDB Consistency Error I am having a problem with replicating a table, and I finally tracked it down to a consistency error on the source table, but I am stumped as to what is causing the consistency error.

I ran DBCC Checkdb and found the following error on the table

  Msg 2570, Level 16, State 3, Line 1 Page (1:5217772), slot 16 in
  object ID 1038014829, index ID 1, partition ID 349502316544000, alloc
  unit ID 349502316544000 (type "In-row data"). Column "MHGPTX" value is
  out of range for data type "varchar".  Update column to a legal value.

I used DBCC PAGE to what row within the table was causing the problem, and tracked down the offending row.

  Slot 16 Column 6 Offset 0x28 Length 150

  MHGPTX = [Error converting to string (length 150 bytes)]

This is where I got stuck, the data in the field looks fine, the data is well under the max size for the column, I can query the row, etc.

I found a few references online to similiar problems, where the problem was resolved by updating the row using the same data, so I tried that, but with no change.

Any advice would be greatly appreciated

I would try that:

drop all indexes
rebuild the clustered index
run dbcc
create index
run dbcc

I would also check the hardware (disk) for inconsistencies.


Failing to automatically re-connect to New PRIMARY after a replica set failover , from Mongoose (MongoDB, NodeJS Driver) I made a simple NodeJS App, with Mongoose as MongoDB Driver. And connected to a mongodb replica set. The App is working fine until I shut down the current primary, When the PRIMARY is down, the replica set automatically elected a new PRIMARY. But, after that the node application doesn't seems to be responding for DB queries.

CODE: DB Connection

var options = {
    server: {
        socketOptions: {
            keepAlive: 1,
            connectTimeoutMS:30000,
            socketTimeoutMS:90000 }
        },
    replset: {
        socketOptions: {
            keepAlive: 1,
            connectTimeoutMS : 30000 ,
            socketTimeoutMS: 90000
        },
        rs_name: 'rs0'
     } };

var uri = "mongodb://xxx.xxx.xxx.xxx:27017,xxx.xxx.xxx.xxx:27017,xxx.xxx.xxx.xxx:27017/rstest";

 mongoose.connect(uri,options);

CODE: DB Query

  router.('/test',function(req,res){

     var testmodel = new testModel('test') ;
     testmodel.save(function (err, doc,numberAffected) {
         if (err) {
             console.log("ERROR: "+ err);
             res.status = 404;
             res.end;
         }else{
             console.log("Response sent ");
             res.status = 200;
             res.end;
         }
     });
 });

Steps Followed

Created a MongoDB replica set in three VMs.
Created a simple nodeJS App (Express + Mongoose) with a test API as above
Sent GET request to 'test' continuously with some time interval to the app from a local system.
Took the PRIMARY instance down
APPLICATION STOPPED RESPONDING TO REQUESTS

Varsions:
    "express": "4.10.6",
    "mongodb": "1.4.23",
    "mongoose": "3.8.21",

A sample app that I have done for debugging this issue is available at https://melvingeorge@bitbucket.org/melvingeorge/nodejsmongorssample.git

I am not sure if this is a bug or some mis-configuration from my end. How to solve this issue ?

You  need something like the flag { auto_reconnect: true } as described here http://mongodb.github.io/node-mongodb-native/api-generated/db.html.

According to documentation: The server option auto_reconnect is defaulted to true which can be overridden.


Does alter column change datatype use tempdb version store? I was running alter column from INT to BIGINT, my table size is 250 GB and after 1 hour I got the version store error because tempdb was full.

As far as I know it only takes your memory pages and log files but I am not sure about Tempdb. Can someone please explain this internals?

I am using read committed isolation.

select
    name,
    snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
from sys.databases

...for this database returns:

Name : DatabaseName
Snapshot_isolation_state_desc : OFF
is_read_committed_snapshot_on : 0

Assuming we're talking about a plain ALTER TABLE, not an operation done through some wizard that does temp table and rename tricks or anything similar.

A straight column data type change may result in update of every row in the table. It depends on the types involved. You have to consider this: the row image in the table is just a bag of bytes, until is somehow interpreted by the engine. The engine follows the table metadata description to make sense of what those bytes are. The metadata will say 'from offset 12 to offset 16 is an int, the column number 4. From offset 17 to offset 19 is a short, the column number 5' and so on and so forth. Now, when you do an ALTER that changes a column type, the metadata will change. And now the $1M question: will the new metadata describe the same bag of bytes correctly? If yes, then the row ALTER is instantaneous and all the rows in the table remain unchanged. If not, then all rows have to be updated. A huge table will lead to a huge update, all in one transaction. This update can exhaust your resources (eg. run out of log space). Shanky is right that this 'update' may take the form of a temp table->insert, but that's an implementation detail.

I wrote some time ago about SQL Server table columns under the hood which shows how some of these operations occur, and how they leave evidence in the actual physical rowset metadata.

Now, you say that you are running out of version store space when this operation is done, even if snapshot was not enabled, right? The version store is used by more than just snapshot isolation. To give a well known example, the DELETED pseudo-table in triggers is powered from the version store. Which means that the version store may be used even if you did not enable snapshot isolation. See Managing TempDB in SQL Server: TempDB Basics (Version Store: Why do we need it?), Sunil quotes 3 non-snapshot related uses of the version-store: triggers, MARS and online index build.

SQL 2014 does not support online alter column, and as a DDL operation it should not require row-versioning for trigger nor for MARS. So IMHO it should not grow the version store.

My main suspect would be, in this case, a readable secondary (it's a shot in the dark here...). The fact that readable secondaries map all reads to snapshot isolation is well documented. I don't remember exactly the details and I may be wrong, but I think that in order for the secondary database to support snapshot isolation, the primary has to use the version store. Think about the fact that the secondary is a  physical copy of the primary and the row size has to match.

Perhaps not an answer, but too long for a comment.


Proper way to upgrade Oracle database Two questions:

I have 11.2.0.3 database. I need to upgrade to a higher version either 11.2.0.4 or 12.1.0.2. Which one I should upgrade it to? Im not going to upgrade on the same server. I will have to do it in a new server. So how can I proceed with the upgrade? Export/import? DBCA? Manual upgrade? What is the procedure to do this?
Same as above question except that this is another database but 10g. Which version I should upgrade it to and how to?

Added info::

I will be moving from OEL 5 to 6 or above.

Downtime I will get over an weekend.

No change in character set or licensing features.

Database size is approx 50 GB.

This is only development instance and not prod.

Should I install 12c database and then import everything from 11g to 12c database or run DBUA?

Best.!

Version 10 is out of support and 11.2.0.4 is out as well except for those who paid for extended.  Upgrading to 12 is the best choice if you want a database that is supported with security patch sets and bug fixes.
Many other factors need to be considered as well and some are outlined in this answer here:

downtime allowed:  an hour, a day, a weekend?
are you changing character sets (if you are will need to use the oracle tool to check for possible issues)
are you adding or removing licensed features?  Example: Enterprise to Standard means partitioning is not allowed
databases with a large code base can have migration issues where things don't work the same way. QA and testing need to be done.
changing operating systems?  Directory paths need adjusting
what other systems interact with the database?  They all need to have configuration changes too
what resources do you have to test your migration?  Believe me you will want to do it more than once.  VMWare snapshots allow a go-back facility that is handy.

Migration paths

Easiest way is to create an installation script that can be used on the new production and development instances and install the latest version of Oracle.
Create table spaces to match your existing ones or take this opportunity to improve the organization of them.
use the data pump to export the contents from existing and import into the new
check that grants, directories, jobs and database links are copied over
test and test again

Given the specifics you have added to your answer the choice of the upgrade assistant or a clean install is still an "It depends" issue.  The upgrade assistant makes things easier but does things like locking the user accounts that can catch one by surprise.  If you want total control over the upgrade do a clean install and import.  If you are not changing table spaces then try the assistant.

The important thing is to test and test again.


How best to store complex cost information I am creating a database to store data related to costs. The cost information can be very varied and I won't be able to fully determine all possible columns currently.

An example piece of data might be the cost of a F-15 jet. So taking this from Wikipedia:

$27.9 million in 1998 dollars.

I will also need to store contextual information, for example:

Model Variant
Weight
Wingspan
Length

The model must also be able to store completely different information such as price of a pressurised water reactor used in a nuclear power plant. The cost could be:

€200 million in 2011 euros.

The contextual information for this would be things like:

Throughput
Max Operating Temperature
Weight
Country of Origin

From a database design point of view the cost information seems quite easy to capture, all costs will have:

Description
Economic Conditions
Unit
Value

The contextual information however can be anything and as such it's hard to design a schema that will easily capture all the relevant data.

The purpose of having this data is to use it to estimate future costs. So all contextual information should be searchable, the user might need all costs from the database that are related to aircrafts or all information on the cost of valves under 100Kg in weight.

So my question is, what is the best way of storing this data? Will I just have to try and create a schema as detailed as possible, will something like Entity-Attribute-Value work best or maybe a NoSQL document store?

Thanks for your help.

After much investigation I think that NoSQL is the best way forward. Having unsearchable JSON fields doesn't appeal and I like the flexibility of schema free design.

I have created an example MongoDB document that covers everything I was trying to do:

{
    Cost_Type : "Actuals",
    Cost_Description : "200mm copper steam valve",
    Cost_UOM : "£K",
    Cost_Value : 1000,
    ECs : 2017,
    Source : {
        URL : ,
        Location_In_Source : "pg 203",
        Document : "The cost of valves over time.pdf",
        }
    Physical_Properties : [
        {
            Property : "Height",
            Property_Value : "200",
            Property_UOM : "mm",
            },
        {
            Property : "Weight",
            Property_Value : "200",
            Property_UOM : "kg"
            }
    ]
    Categories : [
        {
            id: "Valve",
            Path: ",Components,Pumps & Valves,",
            }
        {
            id: "Aircraft",
            Path : ",Industry Sector,Aviation,Aircraft,"
            }
        {
            id: "France"
            Path : ",Europe,France,"
            }
        ]
    Tags : ["Fuel system", "Hot system"],
}

The initial fields cover everything that is required for each cost.

The source field can either be an URL or an actual binary document.

The physical properties field holds an array of key value fields with an additional unit of measurement field. if I didn't need the UOM it would be simpler as I could just use a standard dictionary.

The categories are a reference to a mongodb graph collection which will be defined by an administrator to avoid bloat.

Finally the tags field will allow for the user to add any other information they like.

I think this should cover everything I need but now I just need to actually implement it in a MongoDB prototype.


How to get row count from all tables of a schema ( without using information schema ) The query should collect data from table name , schema name from information.schema and row count should be taken from actual table.

This is actually a very good question since MyISAM stores the count in its header, whereas InnoDB requires a full count (See my answer to the post Why doesn't InnoDB store the row count?)

SOLUTION

Use INFORMATION_SCHEMA.TABLES to create the SQL for counting each table

ALL DATABASES

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('SELECT ',QUOTE(db),' table_schema,',QUOTE(tb),
' table_name,COUNT(1) table_rows FROM `',db,'`.`',tb,'`') SEPARATOR ' UNION ')
INTO @CountSQL
FROM (SELECT table_schema db,table_name tb FROM information_schema.tables WHERE
table_schema NOT IN ('information_schema','performance_schema','mysql')) A;
SELECT @CountSQL\G
PREPARE s FROM @CountSQL; EXECUTE s; DEALLOCATE PREPARE s;

CURRENT DATABASE

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('SELECT ',QUOTE(db),' table_schema,',QUOTE(tb),
' table_name,COUNT(1) table_rows FROM `',db,'`.`',tb,'`') SEPARATOR ' UNION ')
INTO @CountSQL
FROM (SELECT table_schema db,table_name tb FROM information_schema.tables WHERE
table_schema = DATABASE()) A;
SELECT @CountSQL\G
PREPARE s FROM @CountSQL; EXECUTE s; DEALLOCATE PREPARE s;

SPECIFIC DATABASE (like mydata)

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('SELECT ',QUOTE(db),' table_schema,',QUOTE(tb),
' table_name,COUNT(1) table_rows FROM `',db,'`.`',tb,'`') SEPARATOR ' UNION ')
INTO @CountSQL
FROM (SELECT table_schema db,table_name tb FROM information_schema.tables WHERE
table_schema = 'mydata') A;
SELECT @CountSQL\G
PREPARE s FROM @CountSQL; EXECUTE s; DEALLOCATE PREPARE s;

To see the SQL that is generated run this

SELECT @CountSQL\G

GIVE IT A TRY !!!

See docs at https://dev.mysql.com/doc/refman/8.0/en/tables-table.html


why is mysql still writing data two hours after all transactions have stopped I have a mysql server used mostly as data warehouse. It loads about 4-5GB of data per day (actual database size, not the size of the source data).

Lately, I noticed something peculiar. After I stopped the loading process, and nothing else modifies the database, mysql keeps writing data at a rate of 5MB/s for over two hours.

This particular install uses 20GB of innodb_buffer_pool, so even if it was flushing all pages in memory, it would take about 70 minutes to do that.

Additional background information

This server is being mirrored, so it keeps the transaction log.

innodb_buffer_pool_pages_dirty keeps yoyo-ing between 60 and 10.

innodb_rows_(deleted|inserted|updated) is stable, so I'm pretty sure no modification is happening.

I have confirmed that it is indeed mysql doing the writting using /proc/sys/vm/block_dump (see http://www.xaprb.com/blog/2009/08/23/how-to-find-per-process-io-statistics-on-linux/)

This last run, the server has only been up for two days, so it's highly unlikely that it has that many reads backlogged.

This is mysql 5.0 (yes it's old)

InnoDB has the habit of purging about 100 MB every 10 seconds from the InnoDB Buffer pool. That's by design. The idea behind InnoDB transactional model is to delay writes as much as possible.
If you have major spikes in reads, or sustained moderate reads going on, there are still writes occurring because of MVCC. I wrote about this back on May 3, 2012 ( Which is faster, InnoDB or MyISAM? )
You can still see evidence of this in a special way.
Try one of the following two(2) options:
OPTION #1
SET GLOBAL innodb_max_dirty_pages_pct = 0;

The default for innodb_max_dirty_pages_pct is 90 in MySQL 5.0. Set it to zero(0) and flush the Buffer Pool. After it is flushed, set it back to 90. The dirty pages will just pile up again. Delayed writes of changed blocks with just go back to the 100MB/10sec flush pattern all over again.
OPTION #2
SET GLOBAL innodb_max_dirty_pages_pct = 0;

Leave the innodb_max_dirty_pages_pct indefinitely. You will see higher-than-normal writes of the dirty pages.
Regardless which option you choose, if innodb_buffer_pool_pages_dirty conitnues that same pattern and rate of change, without the presence of INSERTs/UPDATEs/DELETES, only SELECTs can be source of this behavior.
You may want to experiment with setting innodb_log_buffer_size (default is 8MB). A larger log buffer saves on disk I/O, but be careful with large COMMITs.


Cannot Access Non-System Databases in SQL Server Express 2016 after I allowed someone access to my SQL Server Express 2016 through team viewer,
I suddenly cannot access non-system databases. No problem with system ones, but I cannot access my other databases. Any ideas?Please see below screenshot

@RDFozz: (Sorry, replies below are disabled) Yes, I knew the user and I am connected as the same user.

From the looks of your screenshot, it looks like your query is not connected to the BERNARDO\SQLEXPRESS01 instance and is connected to the BERNARDO\SQLEXPRESS instance (as indicated at the bottom of the query tab).  Change the connection of that query pane to connect to BERNARDO\SQLEXPRESS01 and I suspect your issue will be resolved.


MYSQL - number of rows returned equals number of connections? Does my query get sent to the database once and I get a list of all the results in one shot which I then loop through, or do I have to request the next row from the DB each time?

Essentially, does reducing the number of rows I expect to return mean less connections/calls to the DB meaning my DB will be able to handle more connections at once, or is the number of database connections not dependent on the number of returned rows?

A connection survives until one of these:

You explicitly call disconnect(), or
Your program terminates, or
A network glitch causes the disconnect.

Normally, you should code your app to connect once near the beginning and disconnect when finished.  (There are rare use cases for having multiple connections open at once.)

Most APIs have an obscure setting that determines whether you get all the rows (of a SELECT) in one "round trip" to the server, or whether they are fed to you individually (maybe in small chunks??) in many round trips.  There are advantages and disadvantages of either.  99.9% of the time, I am happy getting the entire resultset on one round trip, even if it is 100K rows.


Restoring corrupt SQL Server 2005 database I am restoring a SQL Server 2005 database onto a SQL Server 2008 R2 instance using this command:

RESTORE DATABASE SampleDB FROM DISK= 'C:\SampleProject\SampleDB.bak' WITH REPLACE,
MOVE 'sampledb' TO
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SampleDB.mdf',
MOVE 'sampledb_log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SampleDB_log.ldf',
CONTINUE_AFTER_ERROR, STATS = 10;

And the output is:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Msg 3242, Level 16, State 2, Line 3
The file on device 'C:\SampleProject\SampleDB.bak' is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Why does the processed percent reach 100% but then terminate abnormally?

How can I restore this database backup even with corrupt data? Continue-after-error does not appear to make any difference.

Restoring a backup runs in three phases.

creating the files and zeroing them. (Log files are always zeroed, data files depend on a server setting)
reading the pages and writing them to their place in the new files
rolling forward (committed transactions that did not hit a checkpoint yet) and then back (uncommitted transactions).

Only phase 2 is measured by the percent counter. If you are restoring a very large log backup, the 3rd phase can actually take significantly longer than the 2nd phase all the while the percent done counter is sitting at 100%.

Every full database backup always contains a portion of the log. If you have logical inconsistencies within that piece of the backup file, SQL Server would not find out until it is in the 3rd phase. That's why you are seeing 100%.

The best resource for dealing with SQL Server corruption is sqlskills.com. More likely then not however you will find that you can't get to the data with build-in methods. If the problem lies indeed in the log section, you might be able to use an external vendor tool that can read backups like red-gate SQL data compare. There are also tools out there that can help with data recovery from corrupt databases like apexsql recover, but I am not sure if any of those can actually read a backup.


What does it mean when I see a lock in sys.dm_tran_locks and there's no matching row in sys.dm_exec_requests? I'm running this query

select sessions.session_id from
   sys.dm_tran_locks locks inner join  sys.dm_exec_sessions sessions
      on locks.request_session_id=sessions.session_id

and it consistently outputs around four thousand rows. Yet when I run

select session_id from sys.dm_exec_requests

it outputs no more than several rows. And if I try this

select sessions.session_id, requests.session_id
    from sys.dm_tran_locks locks inner join sys.dm_exec_sessions sessions
       on locks.request_session_id=sessions.session_id
       left outer join sys.dm_exec_requests requests
         on sessions.session_id=requests.session_id
order by requests.session_id

I see that none of sessions.session_id are null but notably more than half of requests.session_id are null.

What does that mean? Where do those rows in sys.dm_tran_locks belong than I cannot find a matching row in sys.dm_exec_requests?

Two more additional filters are necessary for your first query.

1- You need to execute query for user databases only i.e. resource_database_id > 4
and
2- Session status "sleeping" mean, session is currently running no request, so exclude such sessions from your result. i.e. [sessions].status  <>'Sleeping'

select sessions.session_id,* from
   sys.dm_tran_locks locks inner join  sys.dm_exec_sessions sessions
      on locks.request_session_id=sessions.session_id
WHERE locks.resource_database_id > 4
AND [sessions].status  <>'Sleeping'


Another question about finding the last time a value changed I know there is a simple answer to this question but just can't get my head round it. I have a table with datetime and int columns.
| TimeStartTz             | RowNumber | RateID      |
|-------------------------|-----------|-------------|
| 2018-05-07 07:00:00.000 | 45        | 1           |
| 2018-04-09 07:00:00.000 | 43        | 1           |
| 2018-03-26 07:00:00.000 | 42        | 1           |
| 2018-03-19 07:00:00.000 | 41        | 1           | <-- want this row
| 2018-03-05 07:00:00.000 | 2         | 2           |
| 2018-02-26 07:00:00.000 | 1         | 2           |
| 2018-02-19 07:00:00.000 | 40        | 1           |
| 2018-02-12 07:00:00.000 | 39        | 1           |
| 2018-01-29 06:45:00.000 | 38        | 1           |
| 2018-01-22 06:45:00.000 | 37        | 1           |

I want to get the row of the most recent change in RateID or the first row if there is no change in the set, i.e. in the example the row with RowNumber 41. I tried partitioning by RateID but it obviously doesn't account for contiguous sections:
    ROW_NUMBER() OVER (PARTITION BY pq.[RateID] ORDER BY pq.[TimeStart]) RowNumber

I believe the easiest way would be using LAG or LEAD functions. They allow you to accesses data from a previous or subsequent row in the same result set without the use of a self-join. But they're available starting with SQL Server 2012 (11.x).
So your solution would require the a self-join like this:
WITH CTE_MostRecentChange
AS
(
    SELECT
        TimeStartTz,
        RateID,
        ROW_NUMBER() OVER (ORDER BY TimeStartTz DESC) AS RowNumber
    FROM MostRecentChange
)
SELECT TOP(1)
    CTE_1.RowNumber,
    CTE_1.TimeStartTz,
    CTE_1.RateID
FROM CTE_MostRecentChange CTE_1
    LEFT JOIN CTE_MostRecentChange CTE_2
        ON CTE_1.RowNumber = CTE_2.RowNumber - 1
WHERE CTE_1.RateID <> CTE_2.RateID
    OR CTE_2.RowNumber IS NULL
ORDER BY CTE_1.TimeStartTz DESC;


The equivalent of Common Table Expressions in MySQL I have a query which I was running in Microsoft SQL server and it worked just fine. It made use of CTEs because I joined three tables. The first two in an inner query then an outer one. However when I tried this on my web server it only has MySQL installed and I could not run the query. Is there any way to run a query with that structure? Here is a sample:

WITH CTE_DatesTable
AS
(
SELECT CAST('20160401' as date) AS [date]
UNION ALL
SELECT DATE_ADD(dd, 1, [date])
FROM CTE_DatesTable
WHERE DATE_ADD(dd, 1, [date]) <= '20170331'
) Select A.[date],
(SELECT CASE DATEPART(WEEKDAY,A.[date])
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY' End) WkDay,
Case When Isnull(C.Hdate,'')<>'' then C.Descr
When (DATEPART(WK,A.[Date])%2)=1 And B.WkOff=1 Then 'AlterWeekoff'
When B.WkOff=0 Then 'Weekoff'Else 'WD' End As WorkDay,
Case When (DATEPART(WK,A.[Date])%2)=1 And B.WkOff=1 or Isnull(C.Hdate,'')<>'' Then 'Holiday'
When B.WkOff=0 Then 'Holiday' Else 'WeekDay' End As WorkDescr
From CTE_DatesTable A Left Join BPC_WDD1 B on DATEPART(dw,A.[Date])=B.WDay
Left Join BPC_HCL1 C on A.date=C.Hdate
OPTION (MAXRECURSION 0);

Onetime action:  Build a table with lots of dates-- a la DatesTable.  (This can be done with a cross join to help generate lots of numbers, then use + INTERVAL n DAY for turning into dates.)

Then LEFT JOIN (with a WHERE clause) from the DatesTable to the other table(s).

Note that you will need to rewrite most date operations because of different syntax.

With MariaDB, you can use a pseudo table like seq_1_to_1000 to get a bunch of numbers.  Also, MariaDB has CTEs.  (MySQL will be getting CTEs sometime in version 8.0.)


Plotting graphs for MYSQL I was executing following command

mysqladmin ext -i1 -c3600

It produces valuable output but I want to use the output to plot graphs so that I can visualize inserts, updates, buffer and other stuff.

Anyone has any idea on how to use the output to plot graphs ?

There are values that you need to graph with absolute values, such as

Innodb_buffer_pool_bytes_dirty
Threads_connected
Threads_running

There are also values whose delta needs to be calculated and graphed, such as

Queries

Com_select
Com_delete
Com_update
etc ...

Incoming and Outgoing Traffic

Bytes_received
Bytes_sent

You should consider using Nagios XI or MONyog to do all that for you.

If you want to be a hero, use Google Charts

Google Chart API - A Real World Example
Sample Chart You Need to Craft in a Bash Script Using the Deltas and Absolute Values

I used Google Chart a few times and got away from it. It does work, but you have a lot of coding to store the absolute values and calculate deltas for specific variables. Then, you script the URL from the values and render the URL in a browser.

GIVE IT A TRY, HERO !!!


List primary replicas and non-HA databases - excluding forwarder replicas When asking which databases are writable on any given server, I've been using the below snippet to list PRIMARY replica databases and databases that do not participate in an AG.

Now that I've deployed a Distributed Availability Group, it's not working anymoar! Since the local forwarder reports as a PRIMARY replica on the sys.dm_hadr_availability_replica_states DMV, my old query is telling me a database is writable when it is not.

  How do I change this query to exclude databases in a local forwarder replica?

select
    ars.role_desc
   ,ag_name = ag.[name]
   ,adc.[database_name]
from sys.availability_groups ag
join sys.dm_hadr_availability_replica_states ars on ars.group_id = ag.group_id
join sys.availability_databases_cluster adc on adc.group_id = ag.group_id
where ars.is_local = 1
    and ars.role_desc = 'PRIMARY'
union all
select
    'LOCAL_ONLY'
   ,@@servername
   ,[name]
from sys.databases d
where not exists (
    select 1
    from sys.availability_databases_cluster adc
    where adc.[database_name] = d.[name]
);

...or more simply...

  How do I list all local forwarder databases?

To list all databases in an forwarder replica on the current server...

select
     dag.group_id
    ,dag.[name]
    ,dag.is_distributed
    ,fwd.replica_id
    ,fwd.replica_server_name
    ,adc.[database_name]
from sys.availability_groups dag
join sys.availability_replicas fwd on fwd.group_id = dag.group_id
join sys.availability_groups ag on ag.name = fwd.replica_server_name
join sys.availability_databases_cluster adc on adc.group_id = ag.group_id
where dag.is_distributed = 1;

To list all databases that are PRIMARY or local only and exclude forwarder databases...

select
    ars.role_desc
   ,ag_name = ag.[name]
   ,adc.[database_name]
from sys.availability_groups ag
join sys.dm_hadr_availability_replica_states ars on ars.group_id = ag.group_id
join sys.availability_databases_cluster adc on adc.group_id = ag.group_id
where ars.is_local = 1
    and ars.role_desc = 'PRIMARY'
    and not exists (
        select 1
        from sys.availability_groups dag
        join sys.availability_replicas fwd on fwd.group_id = dag.group_id
        join sys.availability_groups ag2 on ag2.name = fwd.replica_server_name
        join sys.availability_databases_cluster db on db.group_id = ag2.group_id
        where dag.is_distributed = 1
            and db.[database_name] = adc.[database_name]
)
union all
select
    'LOCAL_ONLY'
   ,@@servername
   ,[name]
from sys.databases d
where not exists (
    select 1
    from sys.availability_databases_cluster adc
    where adc.[database_name] = d.[name]
);


Joining tables based on time and distance: PostGIS with PostgreSQL? I have a table with several thousand rows that contains time and position data. Rows with the same group_id have the same timestamp. Lets call this table "Small":

Small Table:
id  event_time                   group_id   item_id  position
1   '2018-06-21 18:35:01.631094+00' '123a'  1   '01010230...'
2   '2018-06-21 18:35:01.630881+00' '123a'  2   '01010044...'
3   '2018-06-18 10:35:01.630663+00' '321b'  1   '01015600...'
4   '2018-06-18 10:35:01.630305+00' '321b'  2   '01010031...'

I have another table (Table "Big") that has similar data columns (time, position, data1, data2 etc). The timestamps in this table continuous and are overlapping over the first table and there are 80+ million rows:

Big Table:
id  event_time                       Data1   position
1   '2018-06-21 18:45:01.631094+00' 'john'  '01013000...'
2   '2018-06-21 18:41:01.630881+00' 'joe'   '01016000...'
3   '2018-06-21 18:33:01.630663+00' 'john'  '01017000...'
4   '2018-06-21 18:30:01.630305+00' 'rory'  '01018000...'

I have geospatial and time indexes on the two tables.

What I would like to do is find the nearest matches between Big and Small and return data1, data2 and the differences in space and time. In short I'd like to find out that "john" is the best match to group '123a', item 2  and he was 100m and 2 minutes from it but 'rory' was closest to item 1 (5 minutes and 1 km or whatever).

I've tried a command similar to this but it is way too slow. It doesn't seem to be using the indexes.

SELECT
  big.id,
  small.id,
  st_distance(big.position, small.position) as pos_delta,
  (big.event_time, small.event_time) as time_delta,
  big.data1,
  small.item_id
FROM big, small
WHERE
  (big.event_time - small.event_time) < '2 hours'
ORDER BY login_sar_vessel.position <-> login_pos_report.position
LIMIT 1

Is there a way to possible first select data from big table for 2 hours around the timestamp associated with the group_id, find the big rows with  the smallest (big.position to small.position) distance and then repeat for each group_id? That seems a bit messy.

Oh, and the DB is postgres 9.6 with postgis 2.4.

I think you want something like this,

SELECT
  big.id,
  small.id,
  ST_Distance(big.geom, small.geom),
  big.event_time <-> small.event_time
FROM small
CROSS JOIN LATERAL (
  SELECT *
  FROM big
  WHERE (small.event_time <-> big.event_time) < '2 hours'::interval
  ORDER BY small.geom <-> big.geom
  OFFSET 0
  LIMIT 1
) AS big;

You can put both of these on both timestmap and your geom for extra joy using btree_gist

CREATE EXTENSION btree_gist;

CREATE INDEX ON big USING gist (event_time, geom);
CREATE INDEX ON small USING gist (event_time, geom);
VACUUM FULL ANALYZE big;
VACUUM FULL ANALYZE small;

Note: I would upgrade to PostgreSQL 10 so you'd have parallel stuff, as it'll make a major difference for this work load.

You may also consider BRIN indexes on the timestamp, and clustering by timestamp. And after that's done going to GIS. Or even creating a new indexed MATERIALIZED VIEW with only the last week of data or whatever.


Remove duplicates in SQL Server Group By I have 2 tables, Product and Type, the tables looks something like this:

Product
-Id
-Name
-TypeId

Type
-Id
-Name

I want to be able to write a SQL Server query which outputs the products grouped by Type:

+------------+------------+
| Bikes      | Product 1  |
|            | Product 2  |
|            | Product 3  |
| Clothes    | Tshirt 1   |
|            | Trousers 2 |
|            | Hat 3      |
|            | Shoes 4    |
| Electrical | Tv         |
|            | Radio      |
|            | Laptop     |
+------------+------------+

If i write:

SELECT Type.Name,Product.Name
FROM Product INNER JOIN Type ON Type.Id = Product.TypeId
ORDER By Type.Name

I get similar results however the type value is included in every column.

+------------+------------+
| Bikes      | Product 1  |
| Bikes      | Product 2  |
| Bikes      | Product 3  |
| Clothes    | Tshirt 1   |
| Clothes    | Trousers 2 |
| Clothes    | Hat 3      |
| Clothes    | Shoes 4    |
| Electrical | Tv         |
| Electrical | Radio      |
| Electrical | Laptop     |
+------------+------------+

Using SQL Server is there any way i can only show the first type value?

Windowing functions can help here.

SELECT CASE WHEN RowNum = 1 THEN TypeName ELSE NULL END AS TypeName, ProductName
FROM (
SELECT Type.Name AS TypeName,Product.Name AS ProductName, ROW_NUMBER() OVER(PARTITION BY Type.Name ORDER BY Product.Name) AS Rownum, COUNT([whatever you want to count]) AS ItemCount
FROM Product INNER JOIN Type ON Type.Id = Product.TypeId
GROUP BY Type.Name, Product.Name
) X

The MSDN reference for the OVER clause is here, by the way:

https://msdn.microsoft.com/en-us/library/ms189461.aspx


MySQL database configuration for fastening the select and insert queries There are several DBEngines are available for MySQL DB. Currently I'm using InnoDB. My actual problem is, I have a table that consist of millions of data. Therefore the select queries that given to this table is extremely slow. I need to know which configuration should I use to make the select query faster, that doesn't affects the speed of insertion.

Your problem is not the DB Engine, but the lack of the indices. Indices make possible for your database to find things (records) much faster. You had to make some.

I suggest to read an sql indexing tutorial (google is your friend), and then come back with a new question, if you have yet one.

Extension after you gave your query: in this case there is two problem. First, LIMIT isn't really simple to make faster with indices. But it is not a big problem, because in your query is it visible, what is the cause of the sloooow query. It is the multiple inequality conditions in the WHERE. I didn't give a medal for the liferay programmers for this sh... sometimes-maybe-not-really-useful solution.

The greatest problem is, that such indices can't be make faster by indices easily. On big tables they should be avoided, if we want to develop high-quality software.

The solution - indexing a table for multiple inequality search term - isn't known currently also for me, thus I opened a new question for you, what can be done in MySQL in your problem. Maybe we get some interesting answer and in this case I will be able to extend also this question.


In which folder / directory does SQL 2008 store files? Where can I set the folder to store a database in when I create a new Database in the Microsoft SQL 2008 Server?

Via the GUI, right click the main server node:

Programmatically, it is stored in the registry. You can see this here: http://blogs.technet.com/b/sqlman/archive/2009/07/20/tsql-script-determining-default-database-file-log-path.aspx

However, you'd typically specify paths, names, sizes etc in your CREATE DATABASE


mysql upgrade from 5.6 to 5.7, how to config mysql backup we have a lot of mysql database running from 5.6 to 5.7 , we use netbackup to backup mysql ,
the local script  (mysql_backup.sh) is as belows：
/opt/mysql/meb-4.1.3/bin/mysqlbackup --login-path=root   --backup-image=sbt:bkpsbtNB$backuptime --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 --sbt-environment="NB_ORA_SERV=pnbumaster,NB_ORA_CLIENT=prmbsage_svc,NB_ORA_POLICY=mysql_cdeskdb,NB_ORA_SCHED=Default-Application-Backup,ORACLE_HOME=/age/mysql" --backup-dir=/usr/openv/meb_bkdir/ backup-to-image  --socket=/age/mysql/data-5.7.29/mysql.sock --port=3306 >> $MYSQL_LOG_FILE
every time we have upgrade database , we should re-config mysql_backup.sh
is there any method to auto config mysql backup?

Auto-config requires a script that will edit backup files to change versions. You can use any command-line editor, like vi, to change these files to appropriate configurations. It is almost impossible to say what particular commands you must include or modify in these scripts, as it depends completely on your configurations. Still, the best method is to use third-party backup software, such as Handy Backup, which works equally with both MySQL 5.6 and 5.7, and with all other versions, too.


Row based replication on slave where as statement based on Master We have a master slave setup of MYSQL 5.6. Master and slave runs SBR replication where as slave is not writing binlogs.

We need to enable binlogs on the Slave server but we want the binlogs to be in RBR format on the slave instead. We want to ship them to Kafka and it wants RBR replication only.

Is this doable to have RBR on the slave where as it is getting data from master as SBR ?

Thanks

You shouldn't have any trouble. For you replication you use the binlogs in the master, and the your slave reads them and "translates" them to his relay logs. Then your slave executes that relay log and for the replication, that's it.

In fact, you have already "different" settings, since you weren't doing any binlogs at all in your slave, so enabling RBR won't do any harm.


Attempting to identify minimal functional dependencies in a Compact Disc details scenario I believe this relation/table is in first normal form (1NF):
Collection (cd_id, title, label, artist, type, country, song_id, song_title, length)
Note: Bold means keys.
What I have managed to identify:
{cd_id, song_id} → {song_title, length}
{cd_id} → {title, label, artist, type, country}

Is this correct, is it correct to say that it meets 1NF?
Are there more functional dependencies I am missing?

Additional details
Contextual information and/or sample values of the attributes/columns involved:

CD_id: 009329
Title: Title of the CD
Artist: Nickelback
Type: Group | Solo
Country: References the artist’s country or origin
Song_title: "Burn It to the Ground"
Length: 3:32 mins

Extras
Per song under a CD, meaning that there will be duplicates. Every time a song belonging to a CD is added.

A concern that exists is that a song may be released as a single, then as part of an album, then re-released in a "greatest hits" album. This would lead me to have a song table, then create a join table that would connect the album table to the song table. This join table would allow for a single song to be connected to a number of albums. The PK of that table could be the Album_ID and Song_ID, making it a composite.
I would also look at creating an Artist table, which would separate data like the artist's name, country of origin, and group/solo. This could also head off the "Prince Effect," where a singer/group changes their name.
Tables (PKs are bold)

Albums (Album_ID, Artist_ID, Album_Title, Release_Date)

Artist (Artist_ID, Country_Of_Origin, Type, Artist_Name)

Song (Song_ID, Song_Name, Song_Duration)

Song_Album (Album_ID, Song_ID, Track_Number)


Feasibility of Partitioning existing tables with huge data in Oracle We have a set of 24 tables that take up about 1tb+ data which is why DBA suggested to use partitioning to better handle usage.

The only issue being the partitioning of the existing tables is something they are not advising as data transfer from current to temp table will take a lot of time(dbms_redefinition), which is why they are suggesting to use a 2 table approach where old table will be static and new table will be partitioned, and select will be a union of the 2 tables as required.

I just wanted to explore if there is any other way of partitioning the existing tables without requiring significant down time

Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.

You can redefine the table online and it will be accessible for DML. The table will be locked in an exclusive mode for a negligible period of time, where the size of the table doesn't matter.

The downside is you have to have extra space equal to the size of your table(1TB).

Update:

  Online Conversion of a Nonpartitioned Table to a Partitioned Table

  Nonpartitioned tables can be converted to partitioned tables online. Indexes are maintained as part of this operation and can be partitioned as well. The conversion has no impact on the ongoing DML operations.

In Oracle 12c Release 2, you can convert your non-partitioned table to partitioned table using ALTER TABLE statement. But, as per your tagging, you are in 11g R2 and you don't have this option.

Documentation:
 Redefining Tables Online
Oracle 12.2: Online Conversion of a Nonpartitioned Table to a Partitioned Table


SQL server Log Shipping I have two node SQL server standard failover cluster.
I need to setup single node DR using log Shipping method.
Want to ask after switch over to DR how we can setup, plan so that application can connect automatically to DR database server without any changes on application connection string.
Thanks

Without changes to application means you would have to do it at "the network layer". With that I mean things like DNS, rename machine etc.
If you can modify the connection string, then you can try below connection attribute. It is really meant for database mirroring, so you would have to try if it makes work in other situations:
Failover Partner=myMirrorServerAddress;

And, of course, make sure you handle all the other things that you have to handle when you move a database from one instance to another, like scheduled jobs, logins, linked servers etc. I.e., everything that is stored outside the database and is required for the system/application to be functioning.


SQL query to update value based on email address in different table joined by id Please could someone help me construct an SQL query for the following...

I need to update the value 'ucr' in an existing table (db_table_1) with the 'ucr' uploaded to a temporary table (dummy_table) by matching the email address up with the email address that actually exists in a different table altogether (db_table_2).

Both db_table_1 and db_table_2 are joined by id.

db_table_1

id     | blah1  | blah2  | ucr
-------+--------+--------+----------
10001  | abc    |   33   |  VALUE1
10002  | def    |   44   |  VALUE2

db_table_2

id     | blah1  | blah2  | email
-------+--------+--------+----------
10001  | tyu    |   33   |  1@1.com
10002  | fgt    |   44   |  2@2.com

dummy_table

ucr    | name  | email
-------+-------+------------
10001  | abc   |   1@1.com
10002  | def   |   2@2.com

So something like:-

Update 'ucr' in db_table_1 with 'ucr' in dummy_table for 'id' which matches 'email' in db_table_2 in dummy_table. Sorry not sure of the correct syntax when having to join two tables and cross reference another column. Apologies if not explained correctly.

I'm not sure if this solution covers your desired result:

CREATE TABLE table1 (id int, bla1 varchar(10), bla2 varchar(10), ucr varchar(10));
INSERT INTO table1 VALUES (10001, 'ABC', 33, 'VALUE1');
INSERT INTO table1 VALUES (10002, 'DEF', 44, 'VALUE2');
CREATE TABLE table2 (id int, bla1 varchar(10), bla2 varchar(10), email varchar(100));
INSERT INTO table2 VALUES (10001, 'TYU', 33, '1@1.com');
INSERT INTO table2 VALUES (10002, 'FGT', 44, '2@2.com');
CREATE TABLE dummy (ucr int, name varchar(100), email varchar(100));
INSERT INTO dummy VALUES (10001, 'ABC', '1@1.com');
INSERT INTO dummy VALUES (10002, 'DEF', '2@2.com');

UPDATE table1
JOIN   table2
ON     table1.id = table2.id
JOIN   dummy
ON     dummy.email = table2.email
SET    table1.ucr = dummy.ucr;

SELECT *
FROM   table1;

   id | bla1 | bla2 | ucr
----: | :--- | :--- | :----
10001 | ABC  | 33   | 10001
10002 | DEF  | 44   | 10002

dbfiddle here


The easiest way is to join the table with one combined with sequential numbers:

DELETE w.*
  FROM table AS w
  NATURAL JOIN ( SELECT z.*
                      , IF(@cnt <> 0, @cnt:=0, @cnt:=1) AS cnt
                   FROM table AS z
                  ORDER BY z.date ASC
               ) AS q
 WHERE MOD(q.cnt, 2) = 1

Also the same can be done by windowed function ROW_NUMBER() if mysql is  version 8+.


Replicate encrypted data from Sybase ASE 15.5 to Sybase ASE 15.5 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.

Assumptions:

you're referring to ASE's column-level encryption which requires a user/role to have decrypt permissions to see the actual data (as opposed to database-level encryption which merely encrypts the data out on disk)

ASE encrypted data is replicated as varbinary; this means that at no time is the data decrypted and/or re-encrypted; of course, it also means you need a copy of the encryption key in the RDS.

NOTE: If using a MSA or Warm Standby setup you can replicate the encryption key(s) to the RDB.

NOTE: If you have the wrong encryption key in the RDS, that varbinary(X) data will not be decryptable ("Duh, Mark!" ?).

If you're creating explicit repdefs for the table then you'll need to designate the encrypted column's datatype as varbinary(X), where X is the value you'll find in the associated (PDB) syscolumns.encrlen column.

The default function string class is already configured with a function/function-string that supports replication of encrypted data; this basically consists of issuing set ciphertext on when the DSI connects to the RDB, thus allowing that varbinary(X) data to be written directly into an encrypted column.

Permissions to write the varbinary(X) data into the RDB table should be the same as for any write operation into the table.

As for replication of encrypted data in a heterogenous environment ... I'm about 98% sure this isn't possible if simply because each RDBMS product, if it supports some sort of column-level encryption, uses different encryption methods and keys. [I'd have to peruse the latest SRS 16.x manuals if this has changed with recent versions of the product.]


What happens redis uses all allocated memory? I am quite new to redis and try to learn and explore the structure and architecture of redis.

Before version 2.4, there was Virtual Memory option available in redis by which  one could swap the lesser used values to the disk, and from the disk whenever needed. Only hotspot data(frequently used) was kept in main memory.

But now as there is no option like Virtual Memory, how does redis deals with the scenario when the maximum space allocated to it gets filled up fully ?

How it removes data from the main memory, using which algorithm/process ?
Can we write the data which is rarely used to disk without using Virtual Memory ?

Redis respects the maxmemory configuration directive. When set to 0 (the default), Redis will try to allocate as much memory as needed. Once it over does it, the OS will kill it.

Setting maxmemory to any positive value limits the amount of RAM that Redis will consume for data. When you reach the limit, Redis' behavior will depend on the maxmemory-policy configuration setting. The following is taken from the default redis.conf file and details the different evictions policies:

  volatile-lru -> Evict using approximated LRU among the keys with an expire set.
  allkeys-lru -> Evict any key using approximated LRU.
  volatile-lfu -> Evict using approximated LFU among the keys with an expire set.
  allkeys-lfu -> Evict any key using approximated LFU.
  volatile-random -> Remove a random key among the ones with an expire   set.
  allkeys-random -> Remove a random key, any key.
  volatile-ttl -> Remove the key with the nearest expire time (minor TTL)
  noeviction -> Don't evict anything, just return an error on write operations.


Censoring columns for a user There is a user with read permissions on my SQL Server. I don't want them to see my critical columns on a table. How can I censor those columns to the user?

Could I use data masking?

Probably easiest to solve with DCL:

DENY SELECT ON data1.table (column3) TO user1;

Other ways is to create a view where the critical columns is not exposed, and grant select on that view to the user. Needless to say you should revoke any privileges on the table from that user


Getting intervals between time from all succeeding rows? (Time is in text data type) NAME    DATE         TIME      INTERVAL
a      10/19/2015    5:00 PM
a      10/20/2015    5:00 PM    86400
a      10/20/2015    5:30 PM    1800

This is easy to do in Excel. But considering we have a data of 2 million+ rows, we can only do it to the only  DB resource we have which is Access.

How do we do it on Access? We need the interval of seconds between all the time each row per NAME.. So for all "A", we need the second interval between its first time, and second time, the second time, to third time, and so on.

So for "B", its all the same too.

What makes it extra hard is i stored all the data types as TEXT. So I cant just use a datediff or timediff function. :(

You can create a date-time Access type from text using the format() function, so you CAN use datediff.  You should consider creating an query that adds a new column that is date formatted.   The SQL I include here uses that repeatedly which makes it hard to read.

Assume your table is called Table1 with just those three columns, and grouped on Name with no duplicate times (e.g. interval between current row and next row is never zero).

NAME DATE       TIME
a   10/19/2015  5:00 PM
a   10/20/2015  5:00 PM
a   10/20/2015  5:30 PM
b   10/20/2015  4:00 PM
b   10/21/2015  3:00 PM
b   10/21/2015  5:00 PM

The following query unfortunately does not show that first line (for each group) that has no interval, only subsequent rows with the current time, and the interval between the current time and the previous time.    The results look like this:

NAME    DATE        TIME    INTERVAL
a     10/19/2015    5:00 PM 86400
a     10/20/2015    5:00 PM 1800
b     10/20/2015    4:00 PM 82800
b     10/21/2015    3:00 PM 7200

Create a new query, do not add any tables, switch to SQL view,  and Copy and paste the SQL below.  change "Table1" in the code below to your actual table name which you don't provide.

 SELECT table1.NAME,future.DATE,future.TIME,
        Min(DateDiff('s',
              Format([Table1].[TIME] & " " & [Table1][DATE]),
              Format([future].[TIME] & " " & [future].[DATE])))
        AS [INTERVAL]
 FROM Table1 INNER JOIN Table1 AS future ON Table1.NAME = future.NAME
 WHERE (((
   Format([future].[TIME] & " " & [future].[DATE]))
     >Format([Table1].[TIME] & " " & [Table1].[DATE]
   )))
 GROUP BY table1.NAME, future.DATE,future.TIME;

The key is to use a self join (add your data table twice to your query).  When you do that Access creates a table alias with _1 added to the end.  Instead I select the query properties and add an alias for the table in design view.    Join on the NAME field only, and limit on the date-time to select the minimum date-time that is just greater than the current row.

Let me know if that works for you


What database to choose for 1TB of time series? I'm saving about 3GB per day of financial orderbook data to a database (currently Mysql). In the coming weeks, I'm going to save trade and quote (bids/asks) data too.

Data is structured, but I'm doing many searches on it, for example get all orderbooks by stock symbol (which is very slow somehow). Or get all bids/asks by symbol. Currently, a query might take 5 seconds or so (select X where ticker like Y), and the size is only 20GB. In the future, at 1TB, I'm a bit concerned regarding speed per query.

What are some recommendations for cost efficient but also performant solutions for this task? I'm currently running a Mysql database on Digitalocean. Appreciate any help, also any tips regarding what to look into (indexing, sql vs nosql, performance tips, which specific databases would be better suited).

Any relational database will work.  1TB is huge, but feasible.

Don't use ticker LIKE Y unless you need to use wildcards.  Instead use `ticker = 'Y'.  Are you saying that the resultset is 20GB?  Then the real problem is network time.  It takes a lot of time to shovel that much data.

And have an index starting with ticker.  It may be better to have the PRIMARY KEY start with ticker.  If you would like more advice, I need to see more information.

A single row, properly indexed, will take only milliseconds to fetch.