在我的系统中,我有 postgresql 12 和 barman v2.13。我有几个大约 1 TB 的备份磁盘,我目前添加了 5 TB 的新数据库。以前我的 PostgreSQL 不超过 400 GB。
我认为 Barman 不能压缩备份数据库,对吧?那么我可以只备份特定的数据库吗?pgbacktress 类似吗?
在我的系统中,我有 postgresql 12 和 barman v2.13。我有几个大约 1 TB 的备份磁盘,我目前添加了 5 TB 的新数据库。以前我的 PostgreSQL 不超过 400 GB。
我认为 Barman 不能压缩备份数据库,对吧?那么我可以只备份特定的数据库吗?pgbacktress 类似吗?
我需要从昨天的备份中检索单个表中的 2 条数据记录。
我没有足够的磁盘空间将数据库恢复到同一磁盘/服务器上的临时数据库。
我们需要将一些表和模式从 Oracle 数据库移动到 MS SQL Server 数据库。我们只有 Oracle 转储文件。我们将它们导入 Oracle 数据库。然后我们需要使用复制工具将数据从 Oracle 移动到 SQL Server。是否可以绕过复制工具进行移动任务?是否可以使用 Oracle RMAN 或 DUMP 文件将数据从 Oracle 移动到 SQL Server?谢谢...
我有 4 个节点,数据库有 InnoDB 表。我的 key_buffer_size 是 128M。我应该在我的系统中增加它吗?我的 innodb_buffer_pool_size 是 75G,innodb_log_buffer_size = 256M。
Mem: 96688 92580 4107 0 116 8501
-/+ buffers/cache: 83962 12725
Swap: 10239 5104 5135
MariaDB [mydata]> SHOW STATUS LIKE "key%";
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 107171 |
| Key_blocks_used | 4 |
| Key_blocks_warm | 0 |
| Key_read_requests | 25 |
| Key_reads | 4 |
| Key_write_requests | 14 |
| Key_writes | 11 |
+------------------------+--------+
8 rows in set (0.00 sec)
我收到了那个错误,我的一个节点关闭了,所以我有这个问题。我使用 5.5.46-MariaDB-1~trusty wsrep_25.12.r4f8102 谢谢
RECORD LOCKS space id 513 page no 16 n bits 296 index `GEN_CLUST_INDEX` of table `mydata`.`user_counter` trx id 65CFC8177 lock_mode X locks rec but not gap
161230 19:08:36 [ERROR] mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
To report this bug, see http://kb.askmonty.org/en/reporting-bugs
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Server version: 5.5.46-MariaDB-1~trusty-wsrep-log
key_buffer_size=134217728
read_buffer_size=2097152
max_used_connections=809
max_threads=2002
thread_count=224
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 12467846 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x0x7feb728c6000
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7ff378886a00 thread_stack 0x48000
(my_addr_resolve failure: fork)
/usr/sbin/mysqld(my_print_stacktrace+0x2e) [0x7ff37c2db1ae]
/usr/sbin/mysqld(handle_fatal_signal+0x457) [0x7ff37bebffc7]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10340) [0x7ff37a90e340]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x39) [0x7ff379f65cc9]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x148) [0x7ff379f690d8]
/usr/sbin/mysqld(+0x2ded4e) [0x7ff37bcafd4e]
/usr/sbin/mysqld(+0x835033) [0x7ff37c206033]
/usr/sbin/mysqld(+0x83b811) [0x7ff37c20c811]
/usr/sbin/mysqld(+0x83c35b) [0x7ff37c20d35b]
/usr/sbin/mysqld(+0x75a2b2) [0x7ff37c12b2b2]
/usr/sbin/mysqld(+0x75e7ad) [0x7ff37c12f7ad]
/usr/sbin/mysqld(+0x729202) [0x7ff37c0fa202]
/usr/sbin/mysqld(Rows_log_event::find_row(Relay_log_info const*)+0x665) [0x7ff37bfa0a45]
/usr/sbin/mysqld(Update_rows_log_event::do_exec_row(Relay_log_info const*)+0x9c) [0x7ff37bfa0e8c]
/usr/sbin/mysqld(Rows_log_event::do_apply_event(Relay_log_info const*)+0x25c) [0x7ff37bf944ac]
/usr/sbin/mysqld(wsrep_apply_cb(void*, void const*, unsigned long, unsigned int, wsrep_trx_meta const*)+0x7ba) [0x7ff37be70afa]
/usr/lib/galera/libgalera_smm.so(galera::TrxHandle::apply(void*, wsrep_cb_status (*)(void*, void const*, unsigned long, unsigned int, wsrep_trx_meta const*), wsrep_trx_meta const&) const+0xd8) [0x7ff377b188f8]
/usr/lib/galera/libgalera_smm.so(+0x1df27d) [0x7ff377b4f27d]
/usr/lib/galera/libgalera_smm.so(galera::ReplicatorSMM::apply_trx(void*, galera::TrxHandle*)+0xd2) [0x7ff377b51b32]
/usr/lib/galera/libgalera_smm.so(galera::ReplicatorSMM::process_trx(void*, galera::TrxHandle*)+0x10e) [0x7ff377b5498e]
/usr/lib/galera/libgalera_smm.so(galera::GcsActionSource::dispatch(void*, gcs_action const&, bool&)+0x1b8) [0x7ff377b33668]
/usr/lib/galera/libgalera_smm.so(galera::GcsActionSource::process(void*, bool&)+0x58) [0x7ff377b33ef8]
/usr/lib/galera/libgalera_smm.so(galera::ReplicatorSMM::async_recv(void*)+0x73) [0x7ff377b54ef3]
/usr/lib/galera/libgalera_smm.so(galera_recv+0x18) [0x7ff377b634e8]
/usr/sbin/mysqld(+0x4a0744) [0x7ff37be71744]
/usr/sbin/mysqld(start_wsrep_THD+0x48e) [0x7ff37bcccc0e]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182) [0x7ff37a906182]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7ff37a02947d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x0): is an invalid pointer
Connection ID (thread ID): 10
Status: NOT_KILLED
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
161230 19:08:40 mysqld_safe Number of processes running now: 0
161230 19:08:40 mysqld_safe WSREP: not restarting wsrep node automatically
161230 19:08:40 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
我正在尝试从 PostgreSQL 数据库中导出图像文件。该users_data_circulation
表有一个photo
(bytea) 列。
我的命令:
copy (select encode(photo,'hex') from users_data_circulation limit 1)
TO '/tmp/imagetest.hext';
在操作系统中:
$> xxd -p -r /tmp/imagetest.hex > /tmp/imagetest.jpg
$> file /tmp/imagetest.jpg
/tmp/imagetest.jpg: ASCII TEXT
我无法打开jpg
文件。如何将此文件转换为jpg?
devinim@devinimpostgresql:~$ hexdump -C /tmp/image.hex | more
00000000 5c 5c 33 37 37 5c 5c 33 33 30 5c 5c 33 37 37 5c |\\377\\330\\377\|
00000010 5c 33 34 30 5c 5c 30 30 30 10 4a 46 49 46 5c 5c |\340\\000.JFIF\\|
00000020 30 30 30 01 02 5c 5c 30 30 30 5c 5c 30 30 30 01 |000..\\000\\000.|
00000030 5c 5c 30 30 30 01 5c 5c 30 30 30 5c 5c 30 30 30 |\\000.\\000\\000|
00000040 5c 5c 33 37 37 5c 5c 33 34 31 5c 5c 30 30 30 5c |\\377\\341\\000\|
00000050 76 50 49 43 5c 5c 30 30 30 02 5c 6e 5c 6e 01 5c |vPIC\\000.\n\n.\|
00000060 5c 30 30 30 5c 5c 33 37 37 5c 5c 33 37 36 5c 5c |\000\\377\\376\\|
00000070 30 30 30 21 50 69 63 74 75 72 65 20 45 6c 65 6d |000!Picture Elem|
00000080 65 6e 74 73 2c 20 49 6e 63 2e 20 49 53 45 2f 53 |ents, Inc. ISE/S|
... continues like that
我有一个模式'TEST',该模式有自己的表空间'TEST'。但是当我在 USERS 表空间上创建索引时,它就创建了。如何限制在 ORACLE 数据库中使用 USERS 表空间?
CREATE UNIQUE INDEX "TEST"."INDEX1" ON "TEST"."TESTTABLE" ("TESTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
它成功了,我希望 TEST 用户不能在 USERS 表空间上创建任何对象。它有自己的 TEST 表空间。有什么配置吗?用户应该使用 TABLESPACE "TEST" 而不是 TABLESPACE "USERS" 吗?
它给出了错误。我不明白为什么。
CREATE TRIGGER `estatecat_piece` AFTER INSERT ON `estate`
FOR EACH ROW
BEGIN
UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id);
ERROR 1064 (42000):您的 SQL 语法有错误;检查与您的 MySQL 服务器版本相对应的手册,以在第 1 行的 '' 附近使用正确的语法
-编辑-
阿吉安错误...
mysql> DELIMITER ; CREATE TRIGGER `estatecat_piece` AFTER INSERT ON `estate` FOR EACH ROW BEGIN UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id); END; DELIMITER ;
mysql>
mysql> show triggers;
Empty set (0,00 sec)
_ 最后一次尝试的错误 _
mysql> DELIMETER $$ -> CREATE TRIGGER estatecat_piece
-> AFTER INSERT ON estate FOR EACH ROW
-> BEGIN
-> UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id) where estatecat_id=new.estatecat_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMETER $$
CREATE TRIGGER estatecat_piece
AFTER INSERT ON estate FOR EACH ROW ' at line 1
我试图找到其票价具有目的地最低票价且不止一家公司使用该目的地的公司。我的命令;
SQL> select tickets.bus_id bus, tickets.fare fare, route.destination dest
from tickets left join route on route.route_id=tickets.route_id;
BUS FARE DEST
---------- ---------- -------------------------
11 21.36 Kayseri
1 23.32 Kayseri
1 63.32 Kars
5 44.31 Edirne
1 54.13 Edirne
1 33.36 Fatsa
4 33.37 Ankara
4 33.39 Kayseri
7 53.37 Istanbul
10 51.36 Izmir
7 51.36 Izmir
10 32.35 Ankara
10 42.34 Istanbul
10 52.33 Hatay
10 32.83 Bursa
2 30.00 Izmir
11 52.00 Kayseri
11 42.00 Kayseri
2 40.00 Kayseri
我想要的输出:
巴士票价目的地 ---------- ---------- ------------- 10 32.83 布尔萨 2 30.00 伊兹密尔
select bus, min(fare), dest from
(select tickets.bus_id bus, tickets.fare fare, route.destination dest
from tickets left join route on route.route_id=tickets.route_id)
group by dest having count(bus)>1;
>ERROR at line 1: ORA-00937: not a single-group group function
当我尝试分组dest
时,它给出了错误。我不明白什么是错误?有没有人想解决它?
--- 编辑表格 ---
CREATE TABLE buscompanies (
bus_id number(4) not null PRIMARY KEY,
name varchar2(25) not null);
table created.
CREATE TABLE route (
route_id number(4) not null PRIMARY KEY,
origin varchar2(25) not null,
destination varchar2(25) not null);
table created.
CREATE TABLE tickets (
tickets_id number(4) not null PRIMARY KEY,
route_id number(4) not null,
bus_id number(4) not null,
fare number(5,2) not null,
CONSTRAINT routeid_fk FOREIGN KEY (route_id) REFERENCES route(route_id),
CONSTRAINT busid_fk FOREIGN KEY (bus_id) REFERENCES buscompanies(bus_id));
SQL> select * from route;
ROUTE_ID ORIGIN DESTINATION
---------- ------------------------- -------------------------
1 Ankara Kayseri
2 Ankara Kars
3 Ankara Edirne
4 Ankara Fatsa
5 Denizli Ankara
6 Denizli Kayseri
7 Hatay Istanbul
8 Hatay Izmir
9 Izmir Ankara
10 Izmir Istanbul
11 Izmir Hatay
12 Izmir Bursa
13 Bursa Izmir
14 Bursa Kayseri
14 rows selected.
- 编辑 -
我使用了此命令,但仅显示 1 个城市
select bus, min(fare), dest from
(select tickets.bus_id bus, tickets.fare fare, route.destination dest
from tickets left join route on route.route_id=tickets.route_id)
group by bus, dest having count(dest) > 1;
BUS MIN(FARE) DEST
---------- ---------- -------------------------
11 21.36 Kayseri
也应该有其他目的地。问题是;
为每个城市找到最便宜的公司,以便超过 1 家公司前往该城市?
有人可以帮助解决这个错误吗?我无法编写此的 pl/sql 查询。谢谢。
spool 150OMGIddl.sql
declare c clob;
begin
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'150OMGI') into c from dba_tables u where owner='150OMGI';
dbms_output.put_line(c);
dbms_output.put(';');
end;
/
spool off
错误:
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 3
当我跑步时,SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'150OMGI') from dba_tables u where owner='150OMGI';
我有 26 行。
我想从另一个表更新一个表的所有列值,但它不起作用。
我收到此语法错误:
您的 SQL 语法有错误;检查与您的 MariaDB 服务器版本相对应的手册,以了解在 'FROM silinecek s WHERE frameworkcode IN ('AR','CF','FA','IR','KT','SER',' SF','SR' 在第 3 行
这是我的查询
UPDATE marc_subfield_structure
SET liblibrarian = s.liblibrarian, libopac = s.libopac,
repeatable = s.repeatable, mandatory = s.mandatory,
kohafield = s.kohafield, tab = s.tab,
authorised_value = s.authorised_value, authtypecode = s.authtypecode,
value_builder = s.value_builder, isurl = s.isurl, hidden = s.hidden,
seealso = s.seealso, link = s.link, defaultvalue = s.defaultvalue,
maxlength = s.maxlength
FROM
silinecek s
WHERE
frameworkcode IN ('AR','CF','FA','IR','KT','SER','SF','SR','VR')
AND tagfield='952';
我应该怎么办?